Case function with BETWEEN - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Case function with BETWEEN

Author

Topic

berylsmall
Starting Member

26 Posts

Posted - 2004-10-11 : 22:43:11

Hi all, I'm trying to use Case to sum amounts according to bill dates. But I get "syntax error near 'Between' when I try to save it. Can anyone tell me what's wrong here? My procedure looks like this:

Create Procedure "stpAging"

As
SELECT MemberID,
Sum(CASE CONVERT(varchar(12), dbo.tblBills.BillDate, 101) WHEN BETWEEN CONVERT(varchar(12), GETDATE() - 59, 101) AND CONVERT(varchar(12), GETDATE() - 30, 101) THEN CashDue Else 0 END) AS '30 Days',

Sum(CASE CONVERT(varchar(12), dbo.tblBills.BillDate, 101) WHEN BETWEEN CONVERT(varchar(12), GETDATE() - 89, 101) AND CONVERT(varchar(12), GETDATE() - 60, 101) THEN CashDue Else 0 END) As '60 Days'

From tblBills
Group By MemberID

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-11 : 22:56:32

try something like this:


SELECT 
	MemberID,
	Sum(case when datediff(dy,BillDate,getdate()) between 30 and 59 THEN CashDue Else 0 END) AS '30 Days',
	Sum(case when datediff(dy,BillDate,getdate()) between 60 and 89 THEN CashDue Else 0 END) AS '60 Days'
From tblBills
Group By MemberID

Corey

berylsmall
Starting Member

26 Posts

Posted - 2004-10-11 : 23:05:48

Thanks a million! Worked great!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources