Thread: CASE WHEN vs. BETWEEN

I am trying to create an expression in my SQL statement that SUMS the values for a given month for a given stock. I am not sure whether or not to use several SELECT statements as expressions or use a CASE WHEN statement. I would also like to SUM everything before the current year. For instance, if the current year was 2009, it was SUM everything purchased before 1/1/09. Here is what I have so far but cannot seem to get the syntax down:

Code:

SELECT 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '1/1/2009' AND '1/31/2009' THEN sd_shares ELSE 0 END) As Month1, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '2/1/2009' AND '2/28/2009' THEN sd_shares ELSE 0 END) As Month2, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '3/1/2009' AND '3/31/2009' THEN sd_shares ELSE 0 END) As Month3, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '4/1/2009' AND '4/30/2009' THEN sd_shares ELSE 0 END) As Month4, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '5/1/2009' AND '5/31/2009' THEN sd_shares ELSE 0 END) As Month5, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '6/1/2009' AND '6/30/2009' THEN sd_shares ELSE 0 END) As Month6, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '7/1/2009' AND '7/31/2009' THEN sd_shares ELSE 0 END) As Month7, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '8/1/2009' AND '8/31/2009' THEN sd_shares ELSE 0 END) As Month8, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '9/1/2009' AND '9/30/2009' THEN sd_shares ELSE 0 END) As Month9, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '10/1/2009' AND '10/31/2009' THEN sd_shares ELSE 0 END) As Month10, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '11/1/2009' AND '11/30/2009' THEN sd_shares ELSE 0 END) As Month11, 
	SUM(CASE sd_shares WHEN sd_date BETWEEN '12/1/2009' AND '12/31/2009' THEN sd_shares ELSE 0 END) As Month12
FROM tblStocks_detail
WHERE s_id = 3

Any thoughts? What is the most efficient way of going about this?

Also, I am using Access for testing purposes. Will migrate over to SQL Server once complete.

Try

Code:

SUM(Case when sd_date > = '2009-01-01' and sd_date < '2009-02-01' then sd_shares end ) as Month1

Note also that using BETWEEN '2009-01-01' and '2009-01-31' will miss any transactions on 1/31/2009 after midnight ('2009-01-31' converts to '2009-01-31 00:00:00). and using BETWEEN '2009-01-01' and '2009-02-01' might catch any transactions exactly at midnight between 1-31 and 2-01 .

[edit]Note, that's SQL SERVER. Don't know nothin' about Access[/edit]

All of my dates are stored as simple dates, for instance, 1/20/09, 2/25/09, there is no time associated as the day the purchase goes through may be different than the date published.

Just to be clear, if you are storing them as datetime or shortdatetime in SQL Server, they do have times -- albeit 00:00:00.

In this case, the BETWEEN you have is just peachy.

That seems to do the trick. Now, what about summing up all values BEFORE the first of the year? So, if the year is 2009, how do you SUM everything before 1/1/09?

sum (case when year(sd_date) < 2009 then sd_shares end) as oldStuff
or fancier
sum (case when year(sd_date) < year(getdate()) then sd_shares end) as oldStuff
if you want it to work in 2010 as well.

In regards to the CASE WHEN issue, I am trying to display a summary of all stocks. I want to show the stock name, the total shares purchased, the average purchase price, the last trade date, and a few other irrelevant fields. My DB is setup like so:

tblStocks: s_id, s_name
tblStocks_detail: sd_id, s_id, sd_shares, sd_price_per_share, sd_date

Here is my SQL, but it is telling me that I need to include sd_shares in the GROUP BY. When I do this, it does not SUM the records, but instead, shows each individual trade detail. Also, I cannot get the last trade date to work. I basically just want to select the TOP 1 sd_date record for each individual s_id.

Code:

strSQL = "SELECT sd.s_id, s.s_name, s.s_symbol, s.s_publisher, s.s_published_date, " & _
	"SUM(CASE WHEN sd.sd_shares > 0 THEN sd.sd_shares ELSE 0 END) As sd_shares, " & _
	"(CASE WHEN sd.sd_shares > 0 THEN (SELECT (SUM(sd_shares * sd_price_per_share) / SUM(sd_shares)) FROM tblStocks_detail) ELSE 0 END) As sd_price_per_share " & _
	"FROM tblStocks s " & _
	"LEFT JOIN tblStocks_detail sd ON s.s_id = sd.s_id " & _
	"GROUP BY sd.s_id, s.s_name, s.s_symbol, s.s_publisher, s.s_published_date " & _
	"ORDER BY s.s_name"

Any thoughts? Also, I am using a CASE WHEN for formatting purposes. I have FormatNumber(), and I need to pass either a 0 or the actual amount, rather than a blank value, so that it doesn't error out the FormatNumber() function.

I think I know what you are looking for, but do you need the total Shares Purchased and the Average Share Price since the beginning of time, and then also the last trade date?
Also, what version of SQL Server are you on -- makes a difference to what I can use in the query

I am using SQL 2000. I would like both of those as on the summary page, it shows the # shares, avg. price per share, and last trade. Or at least that is what I would like for the query to do. And yes, it is since the beginning of time (all records for that give s_id).