SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 *SOLVED* sql multiple if expressions equivalent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  07:19:27  Show Profile  Reply with Quote
Hi all,

When coding in SQL what is the equivalent to multiple if statements to return the sum of monthly values?

For example:


Sum(IIf([Date] Between #01/08/2012# And #31/08/2012#,[Value]))
Sum(IIf([Date] Between #01/09/2012# And #30/09/2012#,[Value]))


I have attempted to use this to no avail:


SELECT

		CASE
		WHEN InvoiceDate between '2012-01-01' and '2012-01-31' 
		THEN SUM (InvoiceValue)
		WHEN InvoiceDate between '2012-02-01' and '2012-02-29' 
		THEN SUM (InvoiceValue)		
		END AS Jan_2012_Value

FROM
tbSalesHistory

GROUP BY
InvoiceValue, InvoiceDate


Many thanks,
D

Edited by - davidais on 10/04/2012 08:42:42

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/04/2012 :  07:24:02  Show Profile  Reply with Quote
Move the case expression to inside the SUM like this:
SELECT SUM(CASE WHEN InvoiceDate BETWEEN '2012-01-01' AND '2012-01-31' THEN InvoiceValue ELSE 0 END),
       SUM(CASE WHEN InvoiceDate BETWEEN '2012-02-01' AND '2012-02-29' THEN InvoiceValue ELSE 0 END)
FROM   tbSalesHistory
If your invoicedate has time information also, you would want to change the between clause to something different as shown below:
-- instead of
BETWEEN '2012-01-01' AND '2012-01-31'
-- use
InvoiceDate >= '20120101' AND InvoiceDate <  '20120201'
Edit: BTW, if you are trying to calculate the invoice amounts by month, a better option perhaps would be to have a single sum column which is grouped by month.

Edited by - sunitabeck on 10/04/2012 07:25:06
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 10/04/2012 :  07:32:49  Show Profile  Reply with Quote
spot on cheers!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000