| Author |
 Topic  |
|
|
Solearner
Starting Member
USA
2 Posts |
 Posted - 08/28/2012 : 15:50:03
|
I want sum all invoices that are due between certain dates by using the getdate funtion.
For example: For all invoices that have a due date between today and 7 days from now, I want the results to show in a certain column. And then for all invoices that have a due date between 8 days from now and 14 days from now, I want them to fall in to another column.
below is the script I am using:
sum(CASE
WHEN P.DUEDATE <=Dateadd(d,datediff(d,0,getdate()),+7) and P.DOCTYPE < 4 THEN P.CURTRXAM ELSE 0
END) [Week 1],
sum(CASE
WHEN P.DUEDATE <=Dateadd(d,datediff(d,8,getdate()),+14) and P.DOCTYPE < 4 THEN P.CURTRXAM
ELSE 0
END) [Week 2],
However when I run the results I am getting the same amounts in week 1 and week 2, I know this is incorrect because week 1 should yield one set of results and week 2 should yield a different set of results.
Im not sure what I am doing wrong.
Any help will be greatly appreciated |
|
|
chadmat
The Chadinator
USA
1959 Posts |
Posted - 08/28/2012 : 17:10:51
|
Try something like this:
sum(CASE
WHEN P.DUEDATE BETWEEN GETDATE() AND Dateadd(d,7, getdate()) and P.DOCTYPE < 4 THEN P.CURTRXAM ELSE 0
END) [Week 1],
sum(CASE
WHEN P.DUEDATE BETWEEN Dateadd(d,8, getdate()) AND Dateadd(d,14, getdate()) and P.DOCTYPE < 4 THEN P.CURTRXAM
ELSE 0
END) [Week 2],
-Chad |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 08/28/2012 : 23:36:24
|
best thing is to create a table for date ranges and then use it to join and pivot your results.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Solearner
Starting Member
USA
2 Posts |
Posted - 08/29/2012 : 11:48:16
|
Thanks Chad
That change you suggested worked perfectly.
|
|
|
| |
Topic |
|
|
|
Due dates that fall between today and future dates
