CTE syntax - what am I doing wrong? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE syntax - what am I doing wrong?

Author

Topic

mar311
Starting Member

9 Posts

Posted - 2011-03-22 : 13:17:46

Please look at the query I am trying to run. Can anyone tell me what is wrong with my syntax?

My ultimate goal is to sum the columns yst, MTD, YTD, ystphr, MTDphr, YTDphr and then reflect the total sum of each column in a new column (ystGRANDTOTAL, MTDGRANDTOTAL, etc) as seen being attempted below?

Any help would be greatly appreciated!

declare @effdate datetime, @locid int, @showAll bit, @fystart datetime, @locName varchar(50)

set @effdate = getdate()
set @locid = 20
set @showAll = 1

WITH gTotal(location,name,yst,MTD,YTD,ystphr,MTDphr,YTDphr) AS
(
select @locname=facilityname from gwgeneral..locations where locid=@locid
select location=isnull(@locname,'All Locations')
select
location,
name,
yst=ystitemcount,
MTD=mtditemcount,
YTD=ytditemcount,
ystphr=cast(case when ystminutes > 0 then round(ystitemcount/(ystminutes/60.0),0) else 0 end as int),
MTDphr=cast(case when MTDminutes > 0 then round(MTDitemcount/(MTDminutes/60.0),0) else 0 end as int),
YTDphr=cast(case when YTDminutes > 0 then round(YTDitemcount/(YTDminutes/60.0),0) else 0 end as int)

from (
select
location=l.fac,
name=isnull(rtrim(e.fname)+' '+left(e.lname,1),'Unknown'),
s.locid,
staffid=cast(p.staffid as int),
p.stationid,
ystitemcount=sum(case dt when dateadd(d,-1,@effdate) then itemcount else 0 end),
mtditemcount=sum(case when month(dt)=month(@effdate) and year(dt)=year(@effdate) then itemcount else 0 end),
ytditemcount=sum(case when dt between @fystart and @effdate then itemcount else 0 end),
ystminutes=sum(case dt when dateadd(d,-1,@effdate) then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end),
mtdminutes=sum(case when month(dt)=month(@effdate) and year(dt)=year(@effdate) then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end),
ytdminutes=sum(case when dt between @fystart and @effdate then datediff(n,cast(stime as datetime),cast(etime as datetime)) else 0 end)

from ptotals p
join stations s on s.stationid=p.stationid
join gen..locations l on l.locid=s.locid
left join gen..vwAllees e on ltrim(e.empno)=p.staffid
where (@locid = 0 or s.locid=@locid)
group by l.fac,s.locid,e.fname,e.lname,cast(p.staffid as int),p.stationid
) x
where ystitemcount > 0 or ytditemcount > 0
and (@showAll = 1 or ystitemcount > 0)
)
SELECT *,
ystGRANDTOTAL= sum(yst) OVER (PARTITION BY location),
MTDGRANDTOTAL= sum(MTD) OVER (PARTITION BY location),
YTDGRANDTOTAL= sum(YTD) OVER (PARTITION BY location)
FROM gTotal;

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:32:07

Not that you supplied us with an error message to go on....but are you getting

quote:

Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mar311
Starting Member

9 Posts

Posted - 2011-03-24 : 12:50:36

My bad...the err msg I'm getting is:

"Incorrect syntax near 'gTotal'." on the line containing the WITH statement.

Vague error I know...so something is wrong with my syntax. Can anyone tell me where my syntax is incorrect?

Thanks for your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-25 : 16:25:46

try
;WITH gTotal

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-25 : 16:46:02

The semicolon tells the parser that the WITH isn't a part of
set @showAll = 1
in the line above.

No, you're never too old to Yak'n'Roll if you're too young to die.

mar311
Starting Member

9 Posts

Posted - 2011-03-30 : 14:54:18

WebFred - you da man! You answered in 2 minutes what quite a few others could not on other forums. Thank you very much!!

I'm trying to contain the SUM values to just 1 row as opposed to having the SUM show for each row of the data set.

EX: Right now the ystGRANDTOTAL Sum is appended to every row returned but I just want it returned as 1 row so I do not have repeating data.

ystGRANDTOTAL
-------------
550
550
550
550
550 etc...

Looking to just have:

ystGRANDTOTAL
-------------
550

Still reseaching. Thanks again for your help!! Much appreciated!

mar311
Starting Member

9 Posts

Posted - 2011-04-05 : 16:02:59

Ok - I have not come up with an efficient way to show the "GRANDTOTAL" sums as just one row as opposed to appending it to each and every row of the data set. Can anyone advise on best way to do this? Thanks.

SELECT *,
ystGRANDTOTAL= sum(yst) OVER (PARTITION BY location),
MTDGRANDTOTAL= sum(MTD) OVER (PARTITION BY location),
YTDGRANDTOTAL= sum(YTD) OVER (PARTITION BY location)
FROM gTotal;

mar311
Starting Member

9 Posts

Posted - 2011-04-08 : 09:07:52

Am I approaching this the right way to display the value of each GRANDTOTAL in 1 row? The SUM value does not need to be repeated and appended to each row in the datatset and I'd like to have it limited to the value being appended to just 1 row. Any thoughts?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources