Condition on window-functions (SQL)-CodePudding

I have a table which I obtain after joining a few tables together, it looks like this:

SELECT TOP(200)
A.Value as value,readDate,symbol,description,VIN
FROM Table1 as V
JOIN Table2 ON Table2.VX_Id=V.VX_Id
JOIN Table3 ON Table3.FX_Id =Table2.FX_Id
JOIN Table4 on Table3.FX_Id=Table4.FX_Id
JOIN Table5 as A on A.RX_Id=Table4.RX_Id
WHERE symbol='SV'
ORDER BY VIN,readDate


value   readDate                symbol   description      VIN  
------   ---------------------   -----    ----------      ------               
105     2013-02-05 15:17:25.000     SV     text1          C92320
120     2013-02-05 15:25:25.000     SV     text2          C92320
234     2013-01-22 06:17:55.000     SV     text3          F45910   
240     2013-01-23 07:20:55.000     SV     text4          F45910
246     2013-01-23 09:20:55.000     SV     text5          F45910
1500    2015-10-24 12:48:38.000     SV     text6          J20920   
337     2014-01-24 11:58:38.000     SV     text7          P20824

I want to calculate the difference for the next value and previous value (a new column with this difference):

value    difference      readDate                symbol   description      VIN  
------    ----           ---------------------   -----    ----------      ------               
105       NULL          2013-02-05 15:17:25.000     SV     text1          C92320
120       15            2013-02-05 15:25:25.000     SV     text2          C92320
234       114           2013-01-22 06:17:55.000     SV     text3          F45910   
240       6             2013-01-23 07:20:55.000     SV     text4          F45910
246       6             2013-01-23 09:20:55.000     SV     text5          F45910
1500      1254          2015-10-24 12:48:38.000     SV     text6          J20920   
337       -1163         2014-01-24 11:58:38.000     SV     text7          P20824

I can get this table by writing:

SELECT TOP(200)
A.Value as value,A.Value-LAG(A.Value, 1) OVER (ORDER BY VIN,readDate) as 
difference,readDate,symbol,description,VIN
FROM Table1 as V
JOIN Table2 ON Table2.VX_Id=V.VX_Id
JOIN Table3 ON Table3.FX_Id =Table2.FX_Id
JOIN Table4 on Table3.FX_Id=Table4.FX_Id
JOIN Table5 as A on A.RX_Id=Table4.RX_Id
WHERE symbol='SV'

I'm only interested in the difference which are positive and comes from the same VIN number, so I would not be interested in 114 (as C92320->F45910) or 1254 (as F45910->J20920) . So the table I would like to have would look like:

value    difference      readDate                symbol   description      VIN  
------    ----           ---------------------   -----    ----------      ------               
105       NULL          2013-02-05 15:17:25.000     SV     text1          C92320
120       15            2013-02-05 15:25:25.000     SV     text2          C92320
234       NULL          2013-01-22 06:17:55.000     SV     text3          F45910   
240       6             2013-01-23 07:20:55.000     SV     text4          F45910
246       6             2013-01-23 09:20:55.000     SV     text5          F45910
1500      NULL          2015-10-24 12:48:38.000     SV     text6          J20920   
337       NULL         2014-01-24 11:58:38.000      SV     text7          P20824

Is there a way to do this?

CodePudding user response：

As @Larnu pointed out you need PARTITION BY and a CASE expression to check for negative numbers.

Try this:

-- To check for negative numbers we need another select in order to be able to use the result calculated by LAG
SELECT TOP(200) [value], ( CASE WHEN difference < 0 THEN NULL ELSE difference END ) AS difference, readDate, symbol, description, VIN
FROM
    ( SELECT 
    -- Added PARTITION BY
    A.Value as value,A.Value-LAG(A.Value, 1) OVER (PARTITION BY VIN ORDER BY readDate) as 
    difference, readDate, symbol, description, VIN
    FROM Table1 as V
    JOIN Table2 ON Table2.VX_Id=V.VX_Id
    JOIN Table3 ON Table3.FX_Id =Table2.FX_Id
    JOIN Table4 on Table3.FX_Id=Table4.FX_Id
    JOIN Table5 as A on A.RX_Id=Table4.RX_Id
    WHERE symbol='SV' ) AS MainQuery
-- Optionally you can exclude records where difference is 0 or less
WHERE difference > 0
ORDER BY VIN,readDate

Answer to bonus question

To remove duplicate VINs you can try something along those lines:

SELECT *
FROM(
    -- To check for negative numbers we need another select in order to be able to use the result calculated by LAG
    SELECT TOP(200) [value], ( CASE WHEN difference < 0 THEN NULL ELSE difference END ) AS difference,
        -- Order differences per VIN in descending order. Note: 1 - is the largest difference; numbering will restart from 1 for each VIN
        ROW_NUMBER() OVER( PARTITION BY VIN ORDER BY difference DESC ) AS DifferenceRank,
        readDate, symbol, description, VIN
    FROM
        ( SELECT 
        -- Added PARTITION BY
        A.Value as value,A.Value-LAG(A.Value, 1) OVER (PARTITION BY VIN ORDER BY readDate) as 
        difference, readDate, symbol, description, VIN
        FROM Table1 as V
        JOIN Table2 ON Table2.VX_Id=V.VX_Id
        JOIN Table3 ON Table3.FX_Id =Table2.FX_Id
        JOIN Table4 on Table3.FX_Id=Table4.FX_Id
        JOIN Table5 as A on A.RX_Id=Table4.RX_Id
        WHERE symbol='SV' ) AS MainQuery
    -- Optionally you can exclude records where difference is 0 or less
    WHERE difference > 0
    ORDER BY VIN, readDate ) AS DifferenceFilter
-- We only want the biggest difference
WHERE DifferenceRank = 1