CTE Query

[Ian_7]

Trying to write a CTE query where I will get the Max and Min of charge encounters. I am relatively new to the world of using 'Common Table Expression' so bear with me. When I try to parse this I get syntax error messages near keyword statements 'with ' and 'order'. Not sure where my syntax mistake is ..

My inital code is below:

declare @newdata varchar(30)
declare @hh varchar(20)
set @newdata = (select DbName from ConfigDbInfo(nolock) where DbInfoName like 'SF OLTP DB')
set @hh = (select substring((@newdata), len(@newdata)-1, 2))
with MaxAndMinByHour as (
    select max(ObjId) as 'Chg_Created Interface (Max)', min(ObjId) as 'Chg_Created Interface (Min)'
    ,datepart(hh, LastCngDtime) as 'mHour'

    from Chg(nolock)
    where Lastcngdtime between '2013-01-01 00:00:00.003' and '2014-03-31 23:59:59.000'
    group by datepart(hh,LastCngDTime)
    order by mHour desc
)

[MaxMaherC]

You need a ';' before the with.

And it would be better to do something like this:

;with MaxAndMinByHour ( [Chg_Created Interface (Max)], 
                        [Chg_Created Interface (Min)],
                        [mHour]
                      )
as 
(
    select max(ObjId) as 'Chg_Created Interface (Max)',
            min(ObjId) as 'Chg_Created Interface (Min)',
            datepart(hh, LastCngDtime) as 'mHour'
    from Chg(nolock)
    where Lastcngdtime between '2013-01-01 00:00:00.003' and '2014-03-31 23:59:59.000'
    group by datepart(hh,LastCngDTime)
)
SELECT [Chg_Created Interface (Max)], 
       [Chg_Created Interface (Min)],
       [mHour] 
FROM MaxAndMinByHour
ORDER BY mHour DESC

Putting the ORDER BY clause outside the CTE might fix your problem.