count() last 12 months

[DanHD]

hello

I need to see the total salesorders per month for the last 12 months. So when I run the query then the result like:
09 2010: 3 (current month)
08 2010: 55
07 2010: 72
...
...
12 2009: 85

How can I do this?

Dan

 

[RivetHed]

Something like:

Select cast(year(MyDateField) as varchar) + cast(month(MyDateField) as varchar) as Month,
count(Sales) as SalesTotal
From MyTable
Where MyDateField > 'Whatever Date'
Group By  cast(year(MyDateField) as varchar) + cast(month(MyDateField) as varchar)

Should do it I think

 

[djj55]

You might want to add an order by month desc to that.

Personally I would separate the month and year.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DanHD]

hi RivetHed

thanks, it works, but ...........
I've to fill in a date. My goal is a query that select by herself 'current date - 12 months'.


Dan

 

[markros]

declare @StartDate datetime, @PriorDate datetime -- if you use SQL Server 2008, then use date

set @StartDate = getdate()

set @PriorDate = dateadd(month,-12+DATEDIFF(month,'19000101',@StartDate),'19000101')

Select cast(year(MyDateField) as varchar) + cast(month(MyDateField) as varchar(30)) as Month,
count(Sales) as SalesTotal
From MyTable
Where MyDateField >= @PriorDate and MyDateField <=@StartDate
Group By  cast(year(MyDateField) as varchar) + cast(month(MyDateField) as varchar(30))

PluralSight Learning Library

 

[DanHD]

thanks Markros and others,

this works fine for me.

Dan