CODEWITHSUNDEEP
sqlsql-server
JSONX
JSONX

Reputation: 73

Matching previous and current records WITH and WITHOUT LAG and LEAD

I have table like below. Records do not have any primary key and I want to achieve it both WITH and WITHOUT LAG and LEAD function.

ID      ENTID     INOUTDATE             YEAR    MONTH   STATUS
1923    1923    [NULL]                  2099     12      Out
1923    10690   [NULL]                  2099     12      Out
1923    9670    2012-08-24 00:00:00     2012     8       In
1923    1923    2013-06-01 00:00:00     2013     6       In
1923    9670    2018-04-19 00:00:00     2018     4       Out
1923    10690   2019-02-01 00:00:00     2019     2       In

And I want to get the records as per below.

ID      ENTID     INOUTDATE             YEAR    MONTH   STATUS
1923    10690   [NULL]                  2099    12      Out
1923    9670    2012-08-24 00:00:00     2012    8       In
1923    9670    2018-04-19 00:00:00     2018    4       Out
1923    10690   2019-02-01 00:00:00     2019    2       In

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

lag() is the simplest method:

select t.*
from (select t.*, 
             lag(status) over (partition by id, (case when inoutdate is null then 1 else 2 end)
                               order by inoutdate
                              ) as prev_status
      from t
     ) t
where prev_status is null or prev_status <> status;

You can treat this as a group-and-islands problem, identifying the islands using row_number(). The logic is more complicated:

select t.*
from (select t.*, 
             row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end), status, (seqnum - seqnum_s)
                                order by inoutdate
                               ) as seqnum_g
      from (select t.*,
                   row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end) order by inoutdate) as seqnum,
                   row_number() over (partition by id, (case when inoutdate is null then 1 else 2 end), status order by inoutdate) as seqnum_s
            from t
           ) t
     ) t
where seqnum_g = 1;

Upvotes: 1

Related Questions