I try to calculate in SQL MTD cost of a table.
I have following table:
| year | user | month | type | cost | blank |
|---|---|---|---|---|---|
| 2021 | a | 1 | type1 | 10 | 0 |
| 2021 | a | 2 | type1 | 20 | 0 |
| 2021 | a | 3 | type1 | 35 | 0 |
| 2022 | a | 1 | type1 | 5 | 0 |
| 2022 | a | 2 | type1 | 35 | 0 |
| 2021 | b | 1 | type1 | 10 | 0 |
| 2021 | b | 2 | type1 | 30 | 0 |
What I need to have is now the MTD cost as per year, user and type
| year | user | month | type | cost | costMTD | blank |
|---|---|---|---|---|---|---|
| 2021 | a | 1 | type1 | 10 | 10 | 0 |
| 2021 | a | 2 | type1 | 20 | 10 | 0 |
| 2021 | a | 3 | type1 | 35 | 15 | 0 |
| 2022 | a | 1 | type1 | 5 | 5 | 0 |
| 2022 | a | 2 | type1 | 35 | 30 | 0 |
| 2021 | b | 1 | type1 | 10 | 10 | 0 |
| 2021 | b | 2 | type1 | 30 | 20 | 0 |
Can i do this with a query in SQL?
i tried like this but it doesn't work:
SELECT t1.year, t1.user, t1.month, t1.type, t1.cost,
iif(t1.month = '1', t1.cost, t1.cost- t2.cost) AS costMTD, 0 AS blank
FROM dbo.usercosts AS t1 INNER JOIN dbo.usercosts AS t2
ON t1.year = t2.year
AND t1.user= t2.user
AND t1.type= t2.type
AND t2.month = iif(t2.month = '1', '1', t1.month - 1)
Thank you
CodePudding user response：
You can use LAG to see the previous row's cost.
select
year, user, month, type, cost,
cost - coalesce(lag(cost) over(partition by user order by year, month), 0) as costmtd,
0 as blank
from dbo.usercosts as
order by user, year, month;