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.
Author |
Topic |
Solearner
Starting Member
2 Posts |
Posted - 2012-08-28 : 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
1974 Posts |
Posted - 2012-08-28 : 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 0END) [Week 1],sum(CASEWHEN P.DUEDATE BETWEEN Dateadd(d,8, getdate()) AND Dateadd(d,14, getdate()) and P.DOCTYPE < 4 THEN P.CURTRXAMELSE 0END) [Week 2],-Chad |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-28 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
Solearner
Starting Member
2 Posts |
Posted - 2012-08-29 : 11:48:16
|
Thanks ChadThat change you suggested worked perfectly. |
|
|
|
|
|
|
|