Check previous and next record

Question

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 miss a `'` after `'Yes` (but I guess that's a typo) and `Provision = Provision` can be left out, that's always true (but it doesn't harm). The order you display the records here is different from the order in the `OVER` clauses. E.g. `CAK Period` `1` for `2018` aren't next to each other. Maybe that's the problem? — sticky bit, Jan 15 '19 at 12:02
Review the `OVER()` clause that you use with window functions. https://learn.microsoft.com/es-es/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017 — EzLo, Jan 15 '19 at 12:21

score 1 · Accepted Answer · answered Jan 15 '19 at 12:19

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;

Output

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

@miltenburger If you have more than one `Client` then you probably want to partition on client as well. — HABO, Jan 15 '19 at 14:04
@HABO, I sure have more clients, but every provision is unique, so that's enough — miltenburger, Jan 15 '19 at 14:40

Check previous and next record

1 Answers1

Output