SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Query with Cases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vergy39
Starting Member

USA
15 Posts

Posted - 12/06/2012 :  14:37:06  Show Profile  Reply with Quote
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

101 Posts

Posted - 12/06/2012 :  17:50:21  Show Profile  Reply with Quote
--
can you post how the data looks b4 running this query

Edited by - shilpash on 12/06/2012 17:56:25
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 12/06/2012 :  18:17:05  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/06/2012 :  20:32:14  Show Profile  Reply with Quote
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
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 12/07/2012 :  14:54:08  Show Profile  Reply with Quote
Wow, that was it. I thought I had tried that, but obviously I did not. Thank you so much.

David V
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000