CODEWITHSUNDEEP
sqlsql-server
SQL_Noob
SQL_Noob

Reputation: 3

Pivot table times by a table

I have some code for a pivot table but I also want to times (x) it buy another table if it full into the criteria.

select *
from
(SELECT 
        year(dteOccupiedDate) as [year],
        moveincharge as type ,
        left(datename(month,dteOccupiedDate),3)as [month], 
        MoveInCharge 
    FROM dav.Gainshare where strTenancyType = 'LTO' and dteOccupiedDate between '2020-04-01' and '2021-03-31' and moveincharge is not null
    ) as s
    PIVOT
(
    count(moveincharge)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pvt
order by year

this code shows


year    type            jan feb mar     apr     may     jun     jul     aug     sep     oct     nov     dec
2020    Single          0   0   0       5       1       4       12      12      6       0       0       0
2020    Family          0   0   0       5       1       4       12      12      6       0       0       0
2020    Early-leave     0   0   0       5       1       4       12      12      6       0       0       0
2020    Re-house        0   0   0       5       1       4       12      12      6       0       0       0

they are 4 different moveincharge types when it fallin that type I want it X times by the

single (150)

family (200)

rehouse (130)

early leave (140)

I can also make these into a table if that make it better. I want to see this

year    type           jan  feb     mar     apr     may     jun     jul     aug     sep     oct     nov     dec
2020    Single          0   0   0       750     150     600     1800    1800    900       0       0       0

fo example Type Single for July is 12 x 150 because single is worth 150 each and there are 12 in that month

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I much prefer conditional aggregation over the bespoke pivot syntax. This is easily accomplished with a join:

select year(gs.dteOccupiedDate) as [year],
        sum(case when month(gs.dteOccupiedDate) = 1 then v.charge else 0 end) as jan,
        sum(case when month(gs.dteOccupiedDate) = 2 then v.charge else 0 end) as feb,
       . . .
from dav.Gainshare gs join
     (values ('single', 150), ('family', 200), . . .
     ) v(moveincharge, charge)
     on gs.movincharge = v.ovincharge
where gs.strTenancyType = 'LTO' and
      gs.dteOccupiedDate between '2020-04-01' and '2021-03-31' and 
      gs.moveincharge is not null
group by year(gs.dteOccupiedDate);

Note: This will return two rows -- one for 2020 and one for 2021. That is what your query does. You might want to remove the group by and the year from the select.

Upvotes: 1

Related Questions