Conditional rowcount

Empl开发者_JAVA技巧id        FunctionId          Count 

1-------      2 
1 ------      3   ----------      1 
1 ------      4   ----------      2 
1  ------     4
1 ------      5 ----------        3 
1  ------     6     ----------    4 
1   ------    3      ----------   5 
2    ------   3 
2   ------    3 
2   ------    1      ----------   1 
2    ------   2     ----------    2 

H&R is looking for a measure to count the flexibility/mobility in the company.

When an Employee is changing from job/function a FunctionID is stored in the DWH empl dim. See the example above how this table looks like (6000 employee records with lots of mutations as well).

So I need a count only when an employee is going to do something else (another function). The example above is showing you how the count output should be.

How can do it with T-SqL or in a SSIS package (foreach loop ?)

Disclaimer

For given test script, SQL Server honors the order of inserts in the Employee CTE but beware that there is no guarantee it would do so every time.

It is imperative that you provide an additional sort order besides EmplId in the ROW_NUMBER statement.

SQL Statement

;WITH EmployeeRank AS (
  SELECT  EmplId
          , FunctionID
          , rn = ROW_NUMBER() OVER (ORDER BY EmplId)
  FROM    Employee
)
SELECT  er1.EmplID, COUNT(*)
FROM    EmployeeRank er1
        INNER JOIN EmployeeRank er2 
          ON  er2.Emplid = er1.Emplid
              AND er2.rn = er1.rn + 1
              AND er2.FunctionId <> er1.FunctionId
GROUP BY
        er1.EmplID

Test script

;WITH Employee(Emplid, FunctionId) AS (
            SELECT 1, 2 
  UNION ALL SELECT 1, 3 --1        
  UNION ALL SELECT 1, 4 --2         
  UNION ALL SELECT 1, 4
  UNION ALL SELECT 1, 5 --3          
  UNION ALL SELECT 1, 6 --4
  UNION ALL SELECT 1, 3 --5      
  UNION ALL SELECT 2, 3 --1
  UNION ALL SELECT 2, 3 
  UNION ALL SELECT 2, 1 --2         
  UNION ALL SELECT 2, 2 --3         
)
, EmployeeRank AS (
  SELECT  EmplId
          , FunctionID
          , rn = ROW_NUMBER() OVER (ORDER BY EmplId)
  FROM    Employee
)
SELECT  er1.EmplID, COUNT(*)
FROM    EmployeeRank er1
        INNER JOIN EmployeeRank er2 
          ON  er2.Emplid = er1.Emplid
              AND er2.rn = er1.rn + 1
              AND er2.FunctionId <> er1.FunctionId
GROUP BY
        er1.EmplID