CODEWITHSUNDEEP
sqlsql-server
Amit Gupta
Amit Gupta

Reputation: 11

Get the next and prev row data manipulations in SQL Server

I have a data like below format in table:

Id  EmployeeCode    JobNumber           TransferNo  FromDate    Todate
--------------------------------------------------------------------------
1   127             1.0                  0          01-Mar-19   10-Mar-19
2   127             1.0                  NULL       11-Mar-19   15-Mar-19 
3   127             J-1                  1          16-Mar-19   NULL
4   136             1.0                  0          01-Mar-19   15-Mar-19
5   136             J-1                  1          16-Mar-19   20-Mar-19
6   136             1.0                  2          21-Mar-19   NULL

And I want result like this:

Id  EmployeeCode    JobNumber           TransferNo  FromDate    Todate
--------------------------------------------------------------------------
2   127             1.0                  NULL       01-Mar-19   15-Mar-19 
3   127             J-1                  1          16-Mar-19   NULL
4   136             1.0                  0          01-Mar-19   15-Mar-19
5   136             J-1                  1          16-Mar-19   20-Mar-19
6   136             1.0                  2          21-Mar-19   NULL

The idea is

  1. If Job is same in continuous than Single row with max id with min date and max date. For example, for employee 127 first job and second job number is same and second and third row is different, then the first and second row will be returned, with minimum fromdate and max todate, and third row will be returned as is.

  2. If job number is different with its next job number than all rows will be returned.
    For example: for employee 136: first job number is different with second, second is different with third, so all rows will be returned.

Upvotes: 0

Views: 44

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is an example of a gaps and islands problem. The solution here is to define the "islands" by their starts, so the process is:

  • determine when a new grouping begins (i.e. no overlap with previous row)
  • do a cumulative sum of the the starts to get the grouping value
  • aggregate

This looks like

select max(id), EmployeeCode, JobNumber,
       min(fromdate), max(todate)
from (select t.*,
             sum(case when fromdate = dateadd(day, 1, prev_todate) then 0 else 1 end) over
                 (partition by EmployeeCode, JobNumber order by id
                 ) as grouping
      from (select t.*,
                   lag(todate) over (partition by EmployeeCode, JobNumber order by id) as prev_todate
            from t
           ) t
     ) t
group by grouping, EmployeeCode, JobNumber;

It is unclear what the logic is for TransferNo. The simplest solution is just min() or max(), but that will not return NULL.

Upvotes: 0

Grimm
Grimm

Reputation: 791

I doubt you will get a result from simple set-based queries. So my advice: Declare a cursor on SELECT DISTINCT EmployeeCode .... Within that cursor select all rows with that EmployeeCode. Work in this set to figure out your values and construct a resultset from that.

Upvotes: 0

DGi
DGi

Reputation: 97

You can group by jobNumber and EmployeeCode and use the Max/Min-Aggregate-Functions to get the dates you want

Upvotes: 1

Related Questions