CODEWITHSUNDEEP
sqlsql-serversql-server-2014
R. Salehi
R. Salehi

Reputation: 183

Repeat a row according to the specific cell value in SQL

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

Upvotes: 0

Views: 89

Answers (3)

DeadCat
DeadCat

Reputation: 192

Please try this, i got it right :)

   SELECT Table2.year,
               Table2.month,
               Table2.TenDays,
               ISNULL(YourTable.TotalPerTenDays,0)
  FROM (SELECT 
 DISTINCT Year,Month,Table1.TenDays
FROM   (SELECT 1 AS TenDays UNION ALL SELECT 2 UNION ALL SELECT 3) AS Table1
CROSS JOIN YourTable)  AS Table2
 LEFT JOIN YourTable
            ON(YourTable.year         =             Table2.year
            AND YourTable.month =               Table2.month
            AND YourTable.TenDays = Table2.TenDays)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Something like this 

SELECT a.year,
       b.month,
       tc.TenDays,
       COALESCE(b.TotalPerTenDays, 0)
FROM   (SELECT DISTINCT year,month
        FROM   yourtable) a
       CROSS JOIN (VALUES (1),(2),(3)) tc (TenDays)
       LEFT JOIN yourtable b
              ON a.year = b.year
                 AND a.month = b.month
                 AND tc.TenDays = b.TenDays

Your original query should be converted to something like this

SELECT dt.Year,
       dt.Month,
       tc.TenDays,
       Sum(ct.ChequeTreasurer) AS TrTotalMonth
FROM   (select distinct Year, Month, ShamsiDateLong from dbo.DateTable) dt
       CROSS JOIN (VALUES (1),(2),(3)) tc (TenDays)
       LEFT OUTER JOIN dbo.ChequeItemTreasurer ct
                    ON dt.ShamsiDateLong = ct.ChequeDateTreasurer
GROUP  BY dt.Year,
          dt.Month,
          tc.TenDays
ORDER  BY dt.Year,
          dt.Month,
          tc.TenDays

start using alias name, it makes the query more readable..

Upvotes: 4

Related Questions