I'm trying to compare different costs from different periods. But I dont no how I can compare the single record with the record before and after. What I need is a yes or no in my dataset when the costs from a records is the same as record before and record after.
My dataset looks like this:
+--------+-----------+----------+------------+-------+-----------+
| Client | Provision | CAK Year | CAK Period | Costs | Serial Nr |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2017 | 13 | 150 | 1 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2018 | 1 | 200 | 2 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2018 | 2 | 170 | 3 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2018 | 3 | 150 | 4 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2018 | 4 | 150 | 5 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 210 | 2018 | 5 | 150 | 6 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 689 | 2018 | 1 | 345 | 1 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 689 | 2018 | 2 | 345 | 1 |
+--------+-----------+----------+------------+-------+-----------+
| 1 | 689 | 2018 | 3 | 345 | 1 |
+--------+-----------+----------+------------+-------+-----------+
What i've tried so far:
CASE
WHEN Provision = Provision
AND Costs = LEAD(Costs, 1, 0) OVER(ORDER BY CAK Year, CAK Period)
AND Costs = LAG(Costs, 1, 0) OVER(ORDER BY CAK Year, CAK Period)
THEN 'Yes
ELSE 'No'
END
My expected result:
+--------+-----------+----------+------------+-------+-----------+--------+
| Client | Provision | CAK Year | CAK Period | Costs | Serial Nr | Result |
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2017 | 13 | 150 | 1 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2018 | 1 | 200 | 2 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2018 | 2 | 170 | 3 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2018 | 3 | 150 | 4 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2018 | 4 | 150 | 5 | Yes
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 210 | 2018 | 5 | 150 | 6 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 689 | 2018 | 1 | 345 | 1 | No
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 689 | 2018 | 2 | 345 | 1 | Yes
+--------+-----------+----------+------------+-------+-----------+--------+
| 1 | 689 | 2018 | 3 | 345 | 1 | No
+--------+-----------+----------+------------+-------+-----------+--------+
You guys can help me further because I don't get the expected result?
You need to add in partition by Provision otherwise your lag and lead ordering will run across all Provision values:
declare @d table(Client int,Provision int,CAKYear int, CAKPeriod int, Costs int, SerialNr int);
insert into @d values
(1,210,2017,13,150,1)
,(1,210,2018,1,200,2)
,(1,210,2018,2,170,3)
,(1,210,2018,3,150,4)
,(1,210,2018,4,150,5)
,(1,210,2018,5,150,6)
,(1,689,2018,1,345,1)
,(1,689,2018,2,345,1)
,(1,689,2018,3,345,1);
select *
,case when Provision = Provision
and Costs = lead(Costs, 1, 0) over(partition by Provision order by CAKYear, CAKPeriod)
and Costs = lag(Costs, 1, 0) over(partition by Provision order by CAKYear, CAKPeriod)
then 'Yes'
else 'No'
end as Result
from @d
order by Provision
,CAKYear
,CAKPeriod;
+--------+-----------+---------+-----------+-------+----------+--------+
| Client | Provision | CAKYear | CAKPeriod | Costs | SerialNr | Result |
+--------+-----------+---------+-----------+-------+----------+--------+
| 1 | 210 | 2017 | 13 | 150 | 1 | No |
| 1 | 210 | 2018 | 1 | 200 | 2 | No |
| 1 | 210 | 2018 | 2 | 170 | 3 | No |
| 1 | 210 | 2018 | 3 | 150 | 4 | No |
| 1 | 210 | 2018 | 4 | 150 | 5 | Yes |
| 1 | 210 | 2018 | 5 | 150 | 6 | No |
| 1 | 689 | 2018 | 1 | 345 | 1 | No |
| 1 | 689 | 2018 | 2 | 345 | 1 | Yes |
| 1 | 689 | 2018 | 3 | 345 | 1 | No |
+--------+-----------+---------+-----------+-------+----------+--------+