'How to find employee department changes?

I have a table that has the old departments and new departments of various employees:

EmployeeID OldDept NewDept
123 Design Design
234 Software Engineering
345 Design Software
456 Advertising Software

How can I create a table that looks like this:

Dept TransfersIn TransfersOut
Software 2 1
Design 0 1
Advertising 0 1
Engineering 1 0
sqlgoogle-bigquery


Solution 1:[1]

Use below

select * from (
  select NewDept as Department, count(*) Transfers, 'TransfersIn' Mode from your_table
  where NewDept != OldDept
  group by Department, Mode
  union all
  select OldDept as Department, count(*) Transfers, 'TransfersOut' Mode from your_table
  where NewDept != OldDept
  group by Department, Mode
)
pivot (sum(Transfers) for mode in ('TransfersIn', 'TransfersOut'))

if applied to sample data in your question - output is

enter image description here

Solution 2:[2]

You might be able to use this query to find the transfers Out:

SELECT 
    OldDept AS Dept,
    COUNTIF(OldDept != NewDept) As TransfersOut
FROM table
GROUPBY
    EmployeeID,
    OldDept,
    NewDept

Solution 3:[3]

You can use A FUL OUTER JOIN which thw subselects

SELECT 
   COALESCE(OldDept,NewDept) AS Dept, IFNULL(TransfersIn,0) AS TransfersIn   , IFNULL(TransfersOut) AS TransfersOut
FROM
(SELECT NewDept, COUNT(*) AS TransfersIn FROM tabl1 GROUP BY NewDept) As Ne
FULL OUTER JOIN 
(SELECT OldDept, COUNT(*) AS TransfersOut FROM tabl1 GROUP BY NewDept) Ol
ON Ne.NewDept = Ol.OldDept

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 CairoMisr
Solution 3

Related Questions