monthly count of pgms by year (Jan-Dec) to rolling 12 month count

mahpog
mahpog used Ask the Experts™
on
I got a solution for creating a 12 month annual report that would count pgms for each mon.
so, jan  -dec the pgm would have a value. If there was no record for the pgm in a month, the month got defaulted to '"0".  Perfect solution.    I am attaching original sql code, is there any way to turn this into a "rolling" 12 month?  I believe the current usage of spt_values table will not work for the months.  

I have been making attempts to recode this to generate rolling. I have new sql that appeared to work, but the records ended up in wrong months (attaching after original code).

I appreciate any help.
sample.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
Please try: (rolling last 12 months)
select 
    year(r.selmonth) selyear, 
    month(r.selmonth) selmonth, 
    (select count(1) from mrb_details a
      where a.program = b.program_id
        and a.date_created between r.selmonth and dateadd(s, -1, dateadd(month, 1, r.selmonth))
        and year(date_created) = year(r.selmonth)
        and month(date_created) = month(r.selmonth)) as cntpgm,
    b.program_id,
    b.program
from 
  (select dateadd(month, -number, dateadd(month, datediff(month, 0, getdate()), 0)) selmonth
     from [master].[dbo].[spt_values]
    where type = 'P' and number between 0 and 11) r,
  mrb_program b
group by r.selmonth, b.program_id, b.program
order by b.program_id, r.selmonth

Open in new window

Author

Commented:
Thx, gave it a try, and I am attaching results.  Not working yet. However, further than I have gone.
sample2.docx
Top Expert 2011
Commented:
Please try:
declare @periodEnd date

set @periodEnd = '2012-02-29'

select 
    year(r.selmonth) selyear, 
    month(r.selmonth) selmonth, 
    (select count(1) from mrb_details a
      where a.program = b.program_id
        and a.date_created between r.selmonth and dateadd(s, -1, dateadd(month, 1, r.selmonth))) as cntpgm,
    b.program_id,
    b.program
from 
  (select dateadd(month, -number, dateadd(month, datediff(month, 0, @periodEnd), 0)) selmonth
     from [master].[dbo].[spt_values]
    where type = 'P' and number between 0 and 11) r,
  mrb_program b
group by r.selmonth, b.program_id, b.program
order by b.program_id, r.selmonth

Open in new window

Author

Commented:
Yippie! The last code was a charm. Works 100% I am attaching sample outputs.......thanks!
sample3.docx

Author

Commented:
This worked perfectly and will keep online view.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial