| Author |
 Topic  |
|
|
Vergy39
Starting Member
USA
9 Posts |
 Posted - 12/06/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 12/06/2012 : 17:50:21
|
--
can you post how the data looks b4 running this query |
Edited by - shilpash on 12/06/2012 17:56:25 |
 |
|
|
Vergy39
Starting Member
USA
9 Posts |
Posted - 12/06/2012 : 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
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 12/06/2012 : 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
USA
9 Posts |
Posted - 12/07/2012 : 14:54:08
|
Wow, that was it. I thought I had tried that, but obviously I did not. Thank you so much.
David V |
|
|
| |
Topic |
|
|
|
Query with Cases
