CODEWITHSUNDEEP
sql-serverrowssubtraction
Rima Touya
Rima Touya

Reputation: 31

Subtract top two rows from one column using one id

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

Answers (2)

Ron Smith
Ron Smith

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

rs.
rs.

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

Related Questions