Reputation: 31
does anyone know how can I subtract top two rows from one column only using one id? Here's my sample query:
SELECT top 2 a.consumption,
coalesce(a.consumption -
(SELECT b.consumption
FROM tbl_t_billing b
WHERE b.id = a.id + 1), a.consumption) AS diff
FROM tbl_t_billing a
WHERE a.customerId = '5'
ORDER BY a.dateCreated DESC
I want to know how to get the difference between the top 2 rows using one id from the consumption column using the customerId #5. I've tried but I can't get the right query for that. Can somebody help me please? Thanks!
Upvotes: 0
Views: 1610
Reputation: 3266
declare @tbl_t_billing table(consumption int, customerId int, datecreated datetime)
insert into @tbl_t_billing
values
(10,5,'20100101'),
(7,5,'20000101'),
(9,4,'20100101'),
(5,4,'20000101'),
(8,3,'20100101'),
(3,3,'20000101'),
(7,2,'20100101'),
(3,2,'20000101'),
(4,1,'20100101'),
(2,1,'20000101')
-- get the difference between the last two consumption values for each customerId
select
customerId,
sum(consumption) diff
from(
select
customerId,
consumption *
case row_number() over(partition by customerId order by datecreated desc)
when 1 then 1 when 2 then -1
end consumption
from @tbl_t_billing
) t
group by customerId
Upvotes: 1
Reputation: 27467
try this:
;with cte as
(
select consumption, customerId,
row_number() over (partiton by customerid order by datecreated desc) rn
from tbl_t_billing where customerId = '5'
)
select a.customerId, a.consumption,
coalesce((a.consumption - b.consumption), a.consumption) consumption_diff
from cte a left outer join cte b on a.rn + 1 = b.rn
where a.rn = 1
Upvotes: 1