Repeat a row according to the specific cell value in SQL

Question

I have a table like this.

year   month    TenDays   TotalPerTenDays
------------------------------------------
96     9         3         12
96     10        1         15
96     10        3         22
96     11        1         2
96     11        2         10
96     11        3         1
96     12        1         13

I am trying to have a query with this result but ...

year   month    TenDays   TotalPerTenDays
------------------------------------------
96     9         1         0
96     9         2         0
96     9         3         12
96     10        1         15
96     10        2         0
96     10        3         22
96     11        1         2
96     11        2         10
96     11        3         1
96     12        1         13
96     12        2         0
96     12        3         0

Current query

SELECT dbo.DateTable.Year,
       dbo.DateTable.Month,
       dbo.DateTable.TenDays,
       Sum(dbo.ChequeItemTreasurer.ChequeTreasurer) AS TrTotalMonth
FROM   dbo.DateTable
       LEFT OUTER JOIN dbo.ChequeItemTreasurer
                    ON dbo.DateTable.ShamsiDateLong = dbo.ChequeItemTreasurer.ChequeDateTreasurer
GROUP  BY dbo.DateTable.Year,
          dbo.DateTable.Month,
          dbo.DateTable.TenDays
ORDER  BY dbo.DateTable.Year,
          dbo.DateTable.Month,
          dbo.DateTable.TenDays 

Would you please help me for this? (SQL 2014) Thanks

Answer 1

use a number / tally table.

; with 
qry as
(
    -- your existing query
    SELECT  dbo.DateTable.Year, 
        dbo.DateTable.Month, 
        dbo.DateTable.TenDays, 
        SUM(dbo.ChequeItemTreasurer.ChequeTreasurer) AS TrTotalMonth 
    FROM    dbo.DateTable 
        LEFT OUTER JOIN dbo.ChequeItemTreasurer     
                     ON dbo.DateTable.ShamsiDateLong = dbo.ChequeItemTreasurer.ChequeDateTreasurer 
    GROUP BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays 
    ORDER BY dbo.DateTable.Year, dbo.DateTable.Month, dbo.DateTable.TenDays
)
select  q.Year, q.Month, TenDays, TrTotalMonth
from    qry q

union all

select  q.Year, q.Month, n.n as TenDays, TrTotalMonth = 0
from    qry q
        cross join num n    -- num is a number / tally table
where   q.tendays   >= n.n
and not exists
    (
        select  *
        from    qry x
        where   x.year      = q.Year
        and x.Month     = q.Month
        and x.TenDays   = n.n
    )
order by Year, Month, TenDays