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 )