Dividing data into months

[OnTheAnvil]

This is a pure SQL question, not a .Net question, so if there is a better website to post questions like this please let me know.

 

Assume I currently track data in a Sales table with the following fields:

 

RecordDate, NumCarsSold, NumBoatsSold, NumPlanesSold

 

What I need to do is have a report that will display the total number of cars,boats,planes sold by month for an entire year. So it needs to sum all the cars sold for RecordDates between 1/1/2004-1/31/2004 and then 2/1/2004-2/29/2004, etc. and then display those numbers on the report. I would like to have one query that returns 12 rows for any given year where each row is the sum of cars sold, number of boats sold, and number of planes sold for a particular month.

 

What is the best way to structure my query? Should I use the SQL UNION function or something else? This is what I have:

 

SELECT NumCarsSold,NumBoatsSold,NumPlanesSold, 'January' AS Month

FROM Sales

WHERE RecordDate >='1/1/2004' AND RecordDate <= '1/31/2004'

 

UNION

 

SELECT NumCarsSold,NumBoatsSold,NumPlanesSold, 'January' AS Month

FROM Sales

WHERE RecordDate >='2/1/2004' AND RecordDate <= '2/29/2004'

 

UNION

...

 

 

 

Thanks,

OnTheAnvil

[barski]

@enddate as smalldatetime

 

as

 

declare @startdate as smalldatetime

 

set @startdate = cast(datepart(m,dateadd(m,-11,@enddate)) as nvarchar)+ '-1-' + cast(datepart(yyyy,dateadd(m,-11,@enddate)) as nvarchar)

 

select

datepart(m,recorddate), sum(boats),sum(cars),sum(planes)

from sales where recorddate between @startdate and @enddate

[kejpa]

What is the best way to structure my query? Should I use the SQL UNION function or something else? This is what I have:

 

SELECT NumCarsSold,NumBoatsSold,NumPlanesSold, 'January' AS Month

FROM Sales

WHERE RecordDate >='1/1/2004' AND RecordDate <= '1/31/2004'

 

UNION

 

SELECT NumCarsSold,NumBoatsSold,NumPlanesSold, 'January' AS Month

FROM Sales

WHERE RecordDate >='2/1/2004' AND RecordDate <= '2/29/2004'

 

UNION

...

Aouch!

Have a look at GroupBy

 

SELECT NumCarsSold,NumBoatsSold,NumPlanesSold, Month(RecordDate)

FROM Sales group by month(RecordDate)

where Year(RecordDate)=2004

 

(Not totally sure that Month() and Year() is working functions in your database though...)

 

HTH

Kejpa

[OnTheAnvil]

Thanks kejpa but the problem with your SQL is that I get my data returned in seperate rows. I wanted the data in seperate columns. Any other thoughts?

 

~OnTheAnvil

[kejpa]

Thanks kejpa but the problem with your SQL is that I get my data returned in seperate rows.

So does your first approach with UNION...

 

I wanted the data in seperate columns. Any other thoughts?

Having it in columns like

-----------------------------------
Sales  | January | February
Cars   |         5  |         10
Boats  |        10 |          7
Planes |        15 |          1
-----------------------------------

Requires a heavy (and complicatly beuatiful) subselect, not too sure that's what you're after ;)

 

Just because your data is in rows and not columns doesn't necessarily mean that in your UI you must show it in rows, you can always transform it into columns...

 

HTH

/Kejpa