CODEWITHSUNDEEP
sqlsql-server
Bluemarble
Bluemarble

Reputation: 2059

SQL Server - Count Rows based on How Column values have changed across rows

I have a table in SQL Server DB like this:

TBL_EMPLOYEE

enter image description here

Now I need to Pick up the first (min) and last (max) RecordID for Each Employee and do the following:

  1. If the first record is USA and Last is Canada : then flag the employee as "USA to Canada".
  2. If the last record is USA and first is Canada : then flag the employee as "Canada to USA".

My Ultimate goal is to produce the below table - which will show the number of employees that have moved across the two countries.

TBL_MIGRATION

enter image description here

How can I achieve this in SQL Server ?

Upvotes: 0

Views: 36

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

First_value & Last_value is available since 2012 or higher, if you are running with lower version then you can use apply : 

select movement, count(*)
from (select distinct t.employee, 
             concat(t1.workfrom, ' to ', t11.workfrom) as movement
      from table t cross apply
           ( select top (1) t1.*
             from table t1
             where t1.employee = t.employee 
             order by t1.id 
           ) t1 cross apply
           ( select top (1) t11.*
             from table t11
             where t11.employee = t.employee 
             order by t11.id desc 
           ) t11
      where t1.workfrom <> t11.workfrom
     ) t
group by movement;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

It sounds like you want the first and last rows for each employee. Then you can track the overall movement:

select first_workfrom, last_workfrom, count(*)
from (select t.*,
             first_value(workfrom) over (partition by employee order by recordid) as first_workfrom,
             first_value(workfrom) over (partition by employee order by recordid desc) as last_workfrom
      from t
     ) t
group by first_workfrom, last_workfrom
having first_workfrom <> last_workfrom;

Upvotes: 1

Related Questions