Query with Cases - 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 2000 Forums

SQL Server Development (2000)

Query with Cases

Author

Topic

Vergy39
Starting Member

15 Posts

Posted - 2012-12-06 : 14:37:06

I need to create a report that has the Weekending Dates as the Columns and the ReasonFK in the Rows. I was able to do this with a Query:

select ReasonFK,
Sum(case when rcdCreateDate Between '2012-10-28 00:01' And '2012-11-03 23:59' then Amt end) AS Nov3,
Sum(case when rcdCreateDate Between '2012-11-04 00:01' AND '2012-11-10 23:59' then Amt end) AS Nov10,
Sum(case when rcdCreateDate Between '2012-11-11 00:01' And '2012-11-17 23:59' then Amt end) AS Nov17,
from TFSRequests
group by Amt, ReasonFK

However, the data is returned as:

ReasonFK NOV3 NOV10 NOV17
1 NULL NULL 25.00
1 100.00 NULL NULL
1 NULL NULL 125.00
10 NULL NULL 105.00
11 NULL NULL 250.00

As you can see, there are 3 rows for ReasonFK 1. I only want one row per reasonFK. Not sure how I can accomplish this. Any suggestions or assistance is greatly appreciated.

thanks
David V

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-06 : 17:50:21

--
can you post how the data looks b4 running this query

Vergy39
Starting Member

15 Posts

Posted - 2012-12-06 : 18:17:05

There are 2 tables. TFSRequests and TFSReasons. TFSRequests stores all data - TFSID, cFirstName, cLastName, Addr, City, state, Amt, ReasonFK. The TFSReasons stores the Reasons - ReasonID, ReasonName. I need to complete a report that results in the amount of money for each reasonname by Weekending date. I can get this done, but it has the Weekending date as the row and the reasons as the columns, which makes the report hard to read. Again, Any assistance is greatly appreciated.

Thanks
David V

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-06 : 20:32:14

You are grouping and summing on the amount, so you get a record for each amount!

select ReasonFK,
Sum(case when rcdCreateDate Between '2012-10-28 00:01' And '2012-11-03 23:59' then Amt end) AS Nov3,
Sum(case when rcdCreateDate Between '2012-11-04 00:01' AND '2012-11-10 23:59' then Amt end) AS Nov10,
Sum(case when rcdCreateDate Between '2012-11-11 00:01' And '2012-11-17 23:59' then Amt end) AS Nov17,
from TFSRequests
group by ReasonFK

Jim

Everyday I learn something that somebody else already knew

Vergy39
Starting Member

15 Posts

Posted - 2012-12-07 : 14:54:08

Wow, that was it. I thought I had tried that, but obviously I did not. Thank you so much.

David V

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources