0

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
)
2
Contributors
1
Reply
22
Views
2 Years
Discussion Span
Last Post by MaxMaherC
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.