Rollover sum with grouping in SQL Server with values repeating for empty fortnights

Question

I need to do Rollover sum with grouping in SQL Server with values repeating for empty fortnights

I need to sum of sales within same year for the later fortnights.

Data is like shown below

Year fortnight sales

2011         1        10

2011         2        5

2011         5        30

2012         4        30

2012         5        2

I would need the output like below

Year | fortnight | salestillnowThisYear

2011 1 10

2011 2 15 (which is 10 + 5 from previous fortnight within same year)

2011 3 15 same as previous fortnight

2011 4 15 same as previous fortnight

2011 5 45 ( 10 + 5 + 30 from previous quaters of this year)

2011 6 45 same as previous fortnight

2011 7 45 same as previous fortnight

......and so on for each fortnight till last fortnight

2011 24 45 last fortnight data

2012 4 30 note this is the fortnight which first has value

2012 5 32 (which is 10 + 5 from previous fortnight within same year)

2012 6 32 last fortnight data

2012 7 32 last fortnight data

......and so on for each fortnight till last fortnight

2012 24 32 last fortnight data

Below is the available solution with me.

1 .Create temp table (rpt_fortnight) with 24 rows to represent 24 fortnights

2. join the data with that rpt_fortnight table to get data for all fortnights as (step2result)

SELECT YEar, fMaste.FortNightNumber as FortNightNumber, sales as sales FROM data AS FNI inner join rpt_fortNight as fMaste
on fMaste.FortnightNumber >= FNi.FortNightNumber --<--extrapolated data for next fortnights

3.combined data for fortnights from previous fortnights if available

SELECT year,
       FortNightNumber,
       Sum(sales) as sales
 FROM   step2result
 group by   year,FortNightNumber

4. we shall get data for all next fortnights with sum of previous fortnights in step 3

I wanted to check if there was any better solution

Answer 1

Try this. Use Recursive CTE to generate years with 1 to 24 fortnight.

CREATE TABLE #test
  (
     years     INT,
     fortnight INT,
     sal       INT
  )

INSERT #test
VALUES (2011,1,10),
       (2011,2,5),
       (2011,5,30),
       (2012,4,30),
       (2012,5,2);

WITH cte
     AS (SELECT DISTINCT 1 AS fortnight,
                         years
         FROM   #test
         UNION ALL
         SELECT fortnight + 1,
                years
         FROM   cte
         WHERE  fortnight < 24),
     CTE1
     AS (SELECT a.years,
                a.fortnight,
                Isnull(sal, 0) SAL
         FROM   cte A
                LEFT JOIN #test B
                       ON a.fortnight = b.fortnight
                          AND a.years = b.years)
SELECT *,
       Sum(sal)
     OVER(
       partition BY years
       ORDER BY fortnight rows UNBOUNDED PRECEDING) AS Running_Total
FROM   CTE1 A
ORDER  BY years,
          fortnight