SQL Server - Force Select All Months

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

SQL Server - Force Select All Months

Postposted on Fri Jul 17, 2009 12:34 pm

So I'm trying to write myself a query that will select a summed up series of data by month for a particular year. However, I want the selection to return values for all 12 months (0 if no data for a month has been entered). So far, the only thing I've been able to come up with is something like this:
Code: Select all

DECLARE @MonthList TABLE (
   MonthNum tinyint,
   MonthName varchar(50)
)
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (1, 'January')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (2, 'February')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (3, 'March')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (4, 'April')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (5, 'May')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (6, 'June')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (7, 'July')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (8, 'August')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (9, 'September')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (10, 'October')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (11, 'November')
INSERT INTO @MonthList (MonthNum, MonthName) VALUES (12, 'December')

SELECT @TargetYear TargetYear, ml.MonthName, SUM(ISNULL(md.MyValues, 0)) SUMS
FROM MyData md FULL OUTER JOIN @MonthList ml ON MONTH(md.EffectiveDate) = ml.MonthNum
WHERE YEAR(md.EffectiveDate) = @TargetYear
ORDER BY ml.MonthNum

The actual query is a more complex than that, but it's the general idea. Does anyone have a simpler way to create the list of months, possibly getting it into a single select statement? Any clue what the performance hit would be if I were to have a static table in my database instead of creating @MonthList? Thanks.
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 690
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: SQL Server - Force Select All Months

Postposted on Fri Jul 17, 2009 1:08 pm

Code: Select all
SELECT DISTINCT Product," & _
    " SUM(CASE WHEN mailQty BETWEEN 25 AND 50 THEN 1 ELSE 0 END) AS [25 - 50],"& _
    " SUM(CASE WHEN mailQty BETWEEN 50 AND 99 THEN 1 ELSE 0 END) AS [50 - 99],"& _
    " SUM(CASE WHEN mailQty BETWEEN 100 AND 149 THEN 1 ELSE 0 END) AS [100 - 149]," & _
    " SUM(CASE WHEN mailQty BETWEEN 159 AND 199 THEN 1 ELSE 0 END) AS [150 - 199]," & _
    " SUM(CASE WHEN mailQty BETWEEN 200 AND 249 THEN 1 ELSE 0 END) AS [200 - 249]," & _
    " SUM(CASE WHEN mailQty BETWEEN 250 AND 299 THEN 1 ELSE 0 END) AS [250 - 299]," & _
    " SUM(CASE WHEN mailQty BETWEEN 300 AND 349 THEN 1 ELSE 0 END) AS [300 - 349]," & _
    " SUM(CASE WHEN mailQty BETWEEN 350 AND 399 THEN 1 ELSE 0 END) AS [350 - 399]," & _
    " SUM(CASE WHEN mailQty BETWEEN 400 AND 499 THEN 1 ELSE 0 END) AS [400 - 499]," & _
    " SUM(CASE WHEN mailQty > 500 THEN 1 ELSE 0 END) AS ""500"" FROM tblOrder WHERE  orderDate BETWEEN '"& date1 &"' AND '" & date2 &"' GROUP BY Product ORDER BY product"


This is a query I wrote to select the sum of data between certain data points and the where query clause controls the date. The results it returned are 9 columns named by the range, and the number of products in each range, for the dates supplied. You might be able to work with this, it seems similar.
Corsair 600T | ASUS P8P67 PRO | Intel 2500k @ 4.4Ghz | EVGA 560 TI | G.SKILL Ripjaws Series 8GB | Corsair HX650 650W
steelcity_ballin
Gerbilus Supremus
Silver subscriber
 
 
Posts: 11914
Joined: Mon May 26, 2003 5:55 am
Location: Pittsburgh PA

Re: SQL Server - Force Select All Months

Postposted on Fri Jul 17, 2009 5:23 pm

Do you want something like a 'cumulative sum', where the first line is January's value, the second line is (January + February's values), ..., the 12 line is the year's total? If you search for 'cumulative sum', you'll find several pages that explain it better than I can.
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: SQL Server - Force Select All Months

Postposted on Fri Jul 17, 2009 5:52 pm

No, what I'm looking for is there to always be 12 rows, one for each month, even if data has only been entered for less than twelve months (i.e. only January an February's data has been entered). The SQL code I showed above does what I want. I'm want to know if anyone else knows a better way of doing it.
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 690
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: SQL Server - Force Select All Months

Postposted on Fri Jul 17, 2009 8:06 pm

Sums, group by, and the new-ish case statements should work I suppose.
Image
The Model M is not for the faint of heart. You either like them or hate them.

Gerbils unite! Fold for UnitedGerbilNation, team 2630.
Flying Fox
Gerbil God
 
Posts: 24439
Joined: Mon May 24, 2004 2:19 am

Re: SQL Server - Force Select All Months

Postposted on Sat Jul 18, 2009 7:09 am

I think that this should do the trick...

http://www.sqlhacks.com/index.php/Dates/GroupByMonth
Cubical 10
I only know enough to be dangerous.

Do ubuntu? pfsense
cubical10
Gerbil First Class
 
Posts: 184
Joined: Fri Mar 03, 2006 2:52 pm
Location: Montreal

Re: SQL Server - Force Select All Months

Postposted on Sat Jul 18, 2009 8:44 am

cubical10 wrote:I think that this should do the trick...
If I understand correctly, this is a little different because he wants to have all months appear in the results, even if there aren't any records for that month (and so they get a zero).
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: SQL Server - Force Select All Months

Postposted on Sat Jul 18, 2009 8:54 am

wibeasley wrote:
cubical10 wrote:I think that this should do the trick...
If I understand correctly, this is a little different because he wants to have all months appear in the results, even if there aren't any records for that month (and so they get a zero).

Precisely. Forget about the summation and grouping. That's the easy part. What I'm trying to figure out is the best way to force 12 rows to be there, even if there's <12 worth of actual data.
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 690
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: SQL Server - Force Select All Months

Postposted on Sat Jul 18, 2009 10:22 pm

Okay, not a guru, but here's my thought: Have a table with 12 rows that you can use a JOIN clause. (Is that a clause?) I'm not sure which kind of JOIN it is that you'd need to use, but one of them will still show all the rows even if the don't match anything from the other table.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
titan
Grand Gerbil Poohbah
 
Posts: 3276
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains

Re: SQL Server - Force Select All Months

Postposted on Sun Jul 19, 2009 12:05 am

titan wrote:Okay, not a guru, but here's my thought: Have a table with 12 rows that you can use a JOIN clause. (Is that a clause?) I'm not sure which kind of JOIN it is that you'd need to use, but one of them will still show all the rows even if the don't match anything from the other table.

That's exactly what the code in the original post does. He's looking for a cleaner way of doing it.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37733
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: SQL Server - Force Select All Months

Postposted on Sun Jul 19, 2009 12:28 am

steelcity_ballin and Flying Fox have already mentioned the technique. I created the following example. See if this helps.
Code: Select all
--Create table
CREATE TABLE Sales
(SaleDate datetime,
SoldQuantity int)
GO

--Insert Sales data
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('1/1/2009',20)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('2/1/2009',40)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('3/1/2009',60)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('4/1/2009',50)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('6/1/2009',80)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('8/1/2009',70)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('10/1/2009',10)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('12/1/2009',90)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('1/1/2008',30)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('3/1/2008',40)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('5/1/2008',10)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('7/1/2008',70)

--Query Sales to get Sales by year with 12 columns depicting monthly sales
SELECT
YEAR(SaleDate) Year,
SUM(CASE WHEN MONTH(SaleDate) = 1 THEN SoldQuantity ELSE 0 END) JanSales,
SUM(CASE WHEN MONTH(SaleDate) = 2 THEN SoldQuantity ELSE 0 END) FebSales,
SUM(CASE WHEN MONTH(SaleDate) = 3 THEN SoldQuantity ELSE 0 END) MarSales,
SUM(CASE WHEN MONTH(SaleDate) = 4 THEN SoldQuantity ELSE 0 END) AprSales,
SUM(CASE WHEN MONTH(SaleDate) = 5 THEN SoldQuantity ELSE 0 END) MaySales,
SUM(CASE WHEN MONTH(SaleDate) = 6 THEN SoldQuantity ELSE 0 END) JunSales,
SUM(CASE WHEN MONTH(SaleDate) = 7 THEN SoldQuantity ELSE 0 END) JulSales,
SUM(CASE WHEN MONTH(SaleDate) = 8 THEN SoldQuantity ELSE 0 END) AugSales,
SUM(CASE WHEN MONTH(SaleDate) = 9 THEN SoldQuantity ELSE 0 END) SepSales,
SUM(CASE WHEN MONTH(SaleDate) = 10 THEN SoldQuantity ELSE 0 END) OctSales,
SUM(CASE WHEN MONTH(SaleDate) = 11 THEN SoldQuantity ELSE 0 END) NovSales,
SUM(CASE WHEN MONTH(SaleDate) = 12 THEN SoldQuantity ELSE 0 END) DecSales
FROM
Sales
GROUP BY
YEAR(SaleDate)
b_naresh
Gerbil First Class
 
Posts: 177
Joined: Wed Mar 22, 2006 4:25 pm

Re: SQL Server - Force Select All Months

Postposted on Sun Jul 19, 2009 2:37 pm

b_naresh wrote:steelcity_ballin and Flying Fox have already mentioned the technique. I created the following example. See if this helps.
Code: Select all
--Create table
CREATE TABLE Sales
(SaleDate datetime,
SoldQuantity int)
GO

--Insert Sales data
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('1/1/2009',20)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('2/1/2009',40)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('3/1/2009',60)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('4/1/2009',50)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('6/1/2009',80)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('8/1/2009',70)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('10/1/2009',10)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('12/1/2009',90)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('1/1/2008',30)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('3/1/2008',40)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('5/1/2008',10)
INSERT INTO Sales(SaleDate, SoldQuantity) VALUES('7/1/2008',70)

--Query Sales to get Sales by year with 12 columns depicting monthly sales
SELECT
YEAR(SaleDate) Year,
SUM(CASE WHEN MONTH(SaleDate) = 1 THEN SoldQuantity ELSE 0 END) JanSales,
SUM(CASE WHEN MONTH(SaleDate) = 2 THEN SoldQuantity ELSE 0 END) FebSales,
SUM(CASE WHEN MONTH(SaleDate) = 3 THEN SoldQuantity ELSE 0 END) MarSales,
SUM(CASE WHEN MONTH(SaleDate) = 4 THEN SoldQuantity ELSE 0 END) AprSales,
SUM(CASE WHEN MONTH(SaleDate) = 5 THEN SoldQuantity ELSE 0 END) MaySales,
SUM(CASE WHEN MONTH(SaleDate) = 6 THEN SoldQuantity ELSE 0 END) JunSales,
SUM(CASE WHEN MONTH(SaleDate) = 7 THEN SoldQuantity ELSE 0 END) JulSales,
SUM(CASE WHEN MONTH(SaleDate) = 8 THEN SoldQuantity ELSE 0 END) AugSales,
SUM(CASE WHEN MONTH(SaleDate) = 9 THEN SoldQuantity ELSE 0 END) SepSales,
SUM(CASE WHEN MONTH(SaleDate) = 10 THEN SoldQuantity ELSE 0 END) OctSales,
SUM(CASE WHEN MONTH(SaleDate) = 11 THEN SoldQuantity ELSE 0 END) NovSales,
SUM(CASE WHEN MONTH(SaleDate) = 12 THEN SoldQuantity ELSE 0 END) DecSales
FROM
Sales
GROUP BY
YEAR(SaleDate)

Interesting thought b_naresh. However, I'm looking for 1 row / month, not 1 column per month. I could use 12 SUM(CASE()) statements unioned together to get what I'm after, but I feel that the runtime of that would be far longer than doing an outer join on a static "Months" table.

Does anyone know if SQL Server has the ability to turn a list into a collection of rows from within a select statement. Something like:
Code: Select all
SELECT (1,2,3,4,5,6,7,8,9,10,11,12) AS Month
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 690
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: SQL Server - Force Select All Months

Postposted on Mon Jul 20, 2009 4:44 am

Ok, so you have written the query to force 12 rows of data - 1 for each month - and need to know a better implementation for the static Months table? How about this?
Code: Select all
SELECT TOP 12
        IDENTITY(INT,1,1) AS MonthNumber
INTO #Months
   FROM SysColumns sc1,
        SysColumns sc2


EDIT - Found that you can insert multiple rows with a single INSERT statement in SQL Server 2008.
http://blog.sqlauthority.com/2008/07/02 ... nstructor/
b_naresh
Gerbil First Class
 
Posts: 177
Joined: Wed Mar 22, 2006 4:25 pm

Re: SQL Server - Force Select All Months

Postposted on Mon Jul 20, 2009 5:00 am

just brew it! wrote:
titan wrote:Okay, not a guru, but here's my thought: Have a table with 12 rows that you can use a JOIN clause. (Is that a clause?) I'm not sure which kind of JOIN it is that you'd need to use, but one of them will still show all the rows even if the don't match anything from the other table.

That's exactly what the code in the original post does. He's looking for a cleaner way of doing it.

Whoops! :oops: I missed that little bit.
The best things in life are free.
http://www.gentoo.org
Guy 1: Surely, you will fold with me.
Guy 2: Alright, but don't call me Shirley.
titan
Grand Gerbil Poohbah
 
Posts: 3276
Joined: Mon Feb 18, 2002 7:00 pm
Location: Great Smoky Mountains

Re: SQL Server - Force Select All Months

Postposted on Mon Jul 20, 2009 2:44 pm

Thanks guys.

I decided to going with a static Months table that the query can use as part of an outer join. Other mechanisms seem to have a higher levels of implementation to them, and since this really isn't a performance heavy application, simplicity seemed like the best way to go after looking at it all.
Intel i7 860, Asus P7P55D Pro, 4x2GB Corsair XMS3 1600 (CMX4GX3M2A1600C9), EVGA GTX 560 Ti Superclocked
Seagate 7200.7 160GB, WD Caviar Black 640GB, WD Caviar Green 1TB, WD Caviar Green 2TB
Dell 2408WFP and Dell 2407WFP-HC for dual-24" goodness
emorgoch
Gerbil Elite
 
Posts: 690
Joined: Tue Mar 27, 2007 11:26 am
Location: Toronto, ON

Re: SQL Server - Force Select All Months

Postposted on Mon Jul 20, 2009 5:55 pm

Even if it is a new separate table, it contains only 12 rows, minimal amount of columns and indexing required. Unless you have a draconian DBA I doubt this matters. Group by/summation + left/right joining that static "lookup" table seem like a valid strategy to me.
Image
The Model M is not for the faint of heart. You either like them or hate them.

Gerbils unite! Fold for UnitedGerbilNation, team 2630.
Flying Fox
Gerbil God
 
Posts: 24439
Joined: Mon May 24, 2004 2:19 am


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 1 guest