CODEWITHSUNDEEP
sqlsql-servert-sql
Julian Slaughter
Julian Slaughter

Reputation: 137

T-SQL Running Monthly Totals Including Missing Months

I know this sounds really simple but I just cannot seem to get my head around it.

I have a temporary table that holds for example, Handler, MonthName, MonthNumber and MTD, which is a total for that month. What I need to do with that data is then create a running total for each Handler, from April to March. Now, here is the bit I am struggling with. Not all Handlers will have data for all months.

For example.

Handler              MonthName       MonthNo          MTD
Julian Slaughter     April           1                10000
Julian Slaughter     June            3                12000
Julian Slaughter     July            4                10000
Julian Slaughter     September       6                12000
Bob Monkhouse        April           1                5000
Bob Monkhouse        July            4                5000

So I want the results to look like this

Julian Slaughter     April           1                10000
Julian Slaughter     May             2                10000
Julian Slaughter     June            3                22000
Julian Slaughter     July            4                32000
Julian Slaughter     August          5                32000
Julian Slaughter     September       6                44000

...and so on until March

Bob Monkhouse        April           1                5000
Bob Monkhouse        May             2                5000
Bob Monkhouse        June            3                5000
Bob Monkhouse        July            4                10000

...and so on until March

I have tried LEFT JOIN onto a table of the Month Names\Numbers and I have had an attempt at 

OVER(PARTITION ..... ORDER BY ..... RANGE\ROWS)

but can't get the missing months.

Thanks in advance, sorry for the poor formatting, not sure how to do tables on here.

EDIT - Here is my LEFT JOIN attempt

SELECT
Months.MonthNo,
Department,
Executive,
#8.MonthNo,
MTD = SUM([TY MTD Prem]) OVER (PARTITION BY Department, Executive, [Exec Code] ORDER BY #8.MonthNo RANGE UNBOUNDED PRECEDING)
FROM Months
LEFT JOIN #8 ON Months.MonthNo = #8.MonthNo

For one Executive, I only get 4 rows, not the 12 I need. Can't show you the results for Data Protection purposes.

Upvotes: 0

Views: 912

Answers (2)

Julian Slaughter
Julian Slaughter

Reputation: 137

Sorry for the delay. The proposed solution worked a treat. I had to use the same code several times in various other parts of my giant query but it worked great.

Upvotes: 0

user504466
user504466

Reputation: 

DECLARE @start_date date, @end_date date
SELECT @start_date='2012-04-01',@end_date='2013-03-31'
;WITH xo AS
(
    SELECT @start_date AS cte_start_date
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date)
    FROM xo
    WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date   
), x as (
    select *,row_number() over (order by cte_start_date) monthno
  from xo
  )
,  y as (
  select distinct handler from test
)
SELECT y.handler, datename(mm,x.cte_start_date), x.monthno
,(select sum(mtd) from test a where a.handler=y.handler and a.monthno<=x.monthno) mtd
FROM y
cross join x
order by 1,3

see example on SQLFiddle http://sqlfiddle.com/#!3/7d483/15

Upvotes: 1

Related Questions