CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Khasru Khan
Khasru Khan

Reputation: 65

Comparing data from two rows in a same sql table

I am trying to find out differences between two rows in a same table. Having trouble to find right query. For example, I have

Year   Item      Qty    Amount 
------------------------------
2014   Shoes     500     2500 
2014   Ties      300      900 
2014   Pants     200     4000 
2015   Shoes     600     3000 
2015   Ties      200      600

I am trying to find out what was the increased (or decreased) from previous year to this year. I will always have only two years to compare. The query result should look like following:

Items   Qty Diff   Amount Diff 
------------------------------
Shoes      100         500 
Ties    (-100)      (-300) 
Pants     Null        Null

What should be the query look like?

Upvotes: 2

Views: 165

Answers (4)

Azhar Nawaz
Azhar Nawaz

Reputation: 91

Here is the required query:

SET @YEAR1 = '2014';
SET @YEAR2 = '2015';

SELECT 
      Item,
      if(count(*)>1,sum(if(Year=@YEAR2,Qty,-Qty)),NULL) as 'Qty Diff',                                 
      if(count(*)>1,sum(if(Year=@YEAR2,Amount,-Amount)),NULL) as 'Amount Diff' 
FROM 
      table 
WHERE 
      Year IN (@YEAR1,@YEAR2) 
group by Item;

Upvotes: 0

Chamika Goonetilaka
Chamika Goonetilaka

Reputation: 716

Using the lag function is the best approach to this.

SELECT  [Year], [Item], [Qty], [Amount],
       [Qty] - LAG([Qty]) OVER (PARTITION BY [Item] ORDER BY [Year]) [QtyDiff],
       [Amount] - LAG([Amount]) OVER (PARTITION BY [Item] ORDER BY [Year]) [AmountDiff]
FROM [ItemTable] it
order BY [Year] DESC, [Item];

Hope this helps.

Upvotes: 1

Nolan Shang
Nolan Shang

Reputation: 2328

1. Use LAG or LEAD

WITH tb(Year,Item,Qty,Amount) AS (
     SELECT 2014,'Shoes',500,2500 UNION
     SELECT 2014,'Ties',300,900 UNION
     SELECT 2014,'Pants',200,4000 UNION
     SELECT 2015,'Shoes',600,3000 UNION
     SELECT 2015,'Ties',200,600
 ) 
 SELECT *,Qty-LAG(qty)OVER(PARTITION BY Item ORDER BY year) AS QtyDiff ,Amount-LAG(Amount)OVER(PARTITION BY Item ORDER BY year) AS AmountDiff 
 FROM tb

Year        Item  Qty         Amount      QtyDiff     AmountDiff
----------- ----- ----------- ----------- ----------- -----------
2014        Pants 200         4000        NULL        NULL
2014        Shoes 500         2500        NULL        NULL
2015        Shoes 600         3000        100         500
2014        Ties  300         900         NULL        NULL
2015        Ties  200         600         -100        -300

2.Cross or Outer Apply

WITH tb(Year,Item,Qty,Amount) AS (
     SELECT 2014,'Shoes',500,2500 UNION
     SELECT 2014,'Ties',300,900 UNION
     SELECT 2014,'Pants',200,4000 UNION
     SELECT 2015,'Shoes',600,3000 UNION
     SELECT 2015,'Ties',200,600
 ) 
 SELECT t1.Year,t1.Item,t1.Qty- t2.qty AS DiffQty,t1.Amount-t2.Amount AS DiffAmount
 FROM tb AS t1
 OUTER APPLY (SELECT TOP 1 tt.qty,tt.Amount FROM tb AS tt WHERE tt.Year<t1.Year AND t1.Item=tt.Item ORDER BY tt.Year desc) AS t2
 ORDER BY t1.Item,t1.Year

Upvotes: 2

DVT
DVT

Reputation: 3127

If you want to include everything, then you can use FULL OUTER JOIN, if just the one with the earlier year, LEFT OUTER JOIN, if you want the one with both earlier and subsequent year, then INNER JOIN.

SELECT
    T1.Item
    , (T2.QTY-T1.QTY) AS [QTY Diff]
    , (T2.Amount - T1.Amount) AS [Amount Diff]
FROM
    <<Table>> T1
    LEFT OUTER JOIN <<Table>> T2
        ON T1.Item=T2.Item
        AND T1.YEAR=(T2.YEAR-1);

Upvotes: 5

Related Questions