Counting in Summary Report - SQL Server Forums

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)

Counting in Summary Report

Author

Topic

Shilyla
Starting Member

2 Posts

Posted - 2010-06-25 : 14:42:08

The Question:
How do I Count days from the difference of two dates and place it as a value in a summary report? I received an error message indicating: Msg 102, Level 15, State 1, Procedure cstsp_nak, Line 30
Incorrect syntax near ')'.

Attempted Query:

Select A.Field1 as Cam, SUM(CASE WHEN DateDiff(day,D.Date1,GetDate()) > 25 THEN 1 ELSE 0) AS Red,
SUM(CASE WHEN DateDiff(day,D.Date1,GetDate()) BETWEEN 15 AND 24 THEN 1 ELSE 0) AS Orange,
SUM(CASE WHEN DateDiff(day,D.Date1,GetDate()) > BETWEEN 6 AND 14 THEN 1 ELSE 0) AS Yellow,
SUM(CASE WHEN DateDiff(day,D.Date1,GetDate()) <= 5 THEN 1 ELSE 0) AS Green

FROM C JOIN D ON C.SID = D.SID
JOIN A ON D.SID = A.SID
JOIN B ON B.syID = D.syID

Group BY A.Field1

Expected result:

I'm trying to create a summarized report in this format

Red Orange Yellow Green Total
Field1
Field2
Field3

hai
Yak Posting Veteran

84 Posts

Posted - 2010-06-25 : 15:24:44

Need an 'End' with case statement.
eg
SUM(CASE WHEN DateDiff(day,D.Date1,GetDate()) BETWEEN 15 AND 24 THEN 1 ELSE 0 END) as Red

Shilyla
Starting Member

2 Posts

Posted - 2010-06-25 : 16:32:20

Thanks. it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources