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?
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
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