How to find employee department changes?

user_18968022 0
Viewed 73

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
google-bigquery sql
3 Answers

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
user_137697730

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

user_52219440

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
user_51935360
Related