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
 General SQL Server Forums
 New to SQL Server Programming
 *SOLVED* sql multiple if expressions equivalent

Author  Topic 

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 07:19:27
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 07:24:02
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.
Go to Top of Page

davidais
Starting Member

17 Posts

Posted - 2012-10-04 : 07:32:49
spot on cheers!
Go to Top of Page
   

- Advertisement -