Difference in amount in SQL Server-CodePudding

How to get the difference from the table in SQL Server, the difference should be banglore and delhi on the basis of dates , for example on 1st march banglore and delhi what is the difference in the amount.

Purchase_date   City    Amount
2022-03-01  Banglore    50
2022-03-02  Banglore    50
2022-03-03  Banglore    15
2022-03-04  Banglore    10
2022-03-05  Banglore    0
2022-03-05  Banglore    100
2022-03-01  Delhi   50
2022-03-02  Delhi   20
2022-03-03  Delhi   10
2022-03-04  Delhi   100
2022-03-05  Delhi   90

CodePudding user response：

Method 1: specifing the names in the query

select t.PurchaseDate,
       sum(case when t.City = 'Banglore' then t.Amount end)
       -
       sum(case when t.City = 'Delhi' then t.Amount end)
       as Difference
from   test t 
group by t.PurchaseDate

DBFiddle

This returns

PurchaseDate	Difference
2022-03-01	0
2022-03-02	30
2022-03-03	5
2022-03-04	-90
2022-03-05	10

Method 2: Without specifing the names in the query

select t2.PurchaseDate,
       t2.Difference
from   ( select t.PurchaseDate,
                t.City,
                lag(sum(t.Amount), 1) over (order by t.PurchaseDate, t.City) - sum(t.Amount) as Difference,
                row_number() over (partition by t.PurchaseDate order by t.PurchaseDate) as RowNumber
        from   test t
        group by t.PurchaseDate, t.City
      ) t2  
where t2.RowNumber = 2
order by t2.PurchaseDate, t2.City

Note that this only works when you ONLY have 2 different cities !

It is also in the DBFiddle

CodePudding user response：

A simple inner join should do the trick:

select t1.purchase_date,
t1.amount - t2.amount as difference_banglore_delhi
from table_name t1 inner join table_name t2
on t1.purchase_date = t2.purchase_date
and t1.city = 'Banglore' and t2.city = 'Delhi';

Fiddle