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 2008 Forums
 Transact-SQL (2008)
 Due dates that fall between today and future dates

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 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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

Solearner
Starting Member

2 Posts

Posted - 2012-08-29 : 11:48:16
Thanks Chad

That change you suggested worked perfectly.

Go to Top of Page
   

- Advertisement -