SQL - How to combine 3 dates from 3 different tables (BigQuery)-CodePudding

Need to have one view for each agent, and want to aggregate all dates to include all metrics

Basic Information:-

In the productivity table carries the information like as follows:

  | Handled_DATE   | Agent_Email           | Handled  | Missed
  |:-----------:  |:-------------:         |:--------:|:------:|
  |2013-11-05     |  Agent_01@google.com   |    80    |    9
  |2013-11-06     |   Agent_02@google.com  |    60    |   15
  |2013-11-07     |   Agent_03@google.com  |    70    |    7
  |2013-11-08     |   Agent_04@google.com  |    55    |   13
  |2013-11-09     |   Agent_01@google.com  |    73    |    5
  |2013-11-10     |   Agent_01@google.com  |    64    |   14

In the Quality table carries the information like this:

  | Monitored_DATE  | Agent_Email          | Bussiness_Critical | Failed
  |:-----------:  |:-------------:         |:-------------:     |:-----------:|
  |2013-11-05     |  Agent_01@google.com   |    2               |    2
  |2013-11-06     |   Agent_01@google.com  |    1               |    1
  |2013-11-07     |   Agent_01@google.com  |    4               |    4
  |2013-11-08     |   Agent_04@google.com  |    1               |    1

In the Absentieesm table it carries the information like this:

| Attendance_Date | Agent_Email           | Attendance | Late_min
|:-----------:    |:-------------:        |:----------:|:-----------:|
|2013-11-05       |  Agent_01@google.com  |    Attend  |    9
|2013-11-06       |  Agent_01@google.com  |    Sick    |    0
|2013-11-07       |  Agent_01@google.com  |    Sick    |    0
|2013-11-08       |  Agent_04@google.com  |    55      |   13
|2013-11-10       |  Agent_01@google.com  |    Attend  |    0

Desired Output

| DATE       | Agent_Email           | Handled | Missed | Business_Critical | Failed |Attendance|Late_min
|:---------- |:-------------:        |:-------:|:------:|:-----------------:|:-----: |:--------:|:------:|
|2013-11-05  |  Agent_01@google.com  |    80   |    9   |       2           |   2    |  Attend  |   9
|2013-11-06  |  Agent_01@google.com  |    0    |    0   |       1           |   1    |  sick    |   0
|2013-11-07  |  Agent_01@google.com  |    0    |    0   |       4           |   4    |  sick    |   0
|2013-11-08  |  Agent_01@google.com  |    0    |    0   |       0           |   0    |  sick    |   0
|2013-11-09  |  Agent_01@google.com  |    73   |    5   |       0           |   0    |  NA      |   0
|2013-11-10  |  Agent_01@google.com  |    64   |   14   |       0           |   0    |  Attend  |   0

CodePudding user response：

Join the tables with Agent_Email and respective dates as follows:

SELECT p.handled_date as date,
       p.Agent_Email, p.Handled, p.Missed 
       q.Business_Critical, q.Failed 
       a.Attendance, a.Late_min
FROM Productivity as p
LEFT JOIN Quality as q
  ON p.Agent_Email = q.Agent_Email
  AND p.handled_date = q.Monitored_DATE
LEFT JOIN absentieesm as a
  ON p.Agent_Email = a.Agent_Email
  AND p.handled_date = a.Attendance_DATE

Note - Keep the table which has most of the Agents as LEFT TABLE

CodePudding user response：

Try Below Code. Let me know if it helps.

With Date as ( select distinct date1 from (
SELECT p.handled_date as date1 FROM `productivity` as p union all
select q.Monitored_DATE as date1 from `Quality` as q union all
select a.Attendance_Date as date1 from `Absentieesm` as a)),

Agent as ( select distinct Agent_Email from (
SELECT p.Agent_Email as Agent_Email FROM `productivity` as p union all
select q.Agent_Email as Agent_Email from `Quality` as q union all
select a.Agent_Email as Agent_Email from `Absentieesm` as a))


select distinct
Date1,
Agent.Agent_Email,
ifnull(Handled,0) as Handled, 
ifnull(Missed,0) as Missed, 
ifnull(Bussiness_Critical,0) as Bussiness_Critical, 
ifnull(Failed,0) as Failed, 
ifnull(Attendance,NULL) Attendance, 
ifnull(Late_min,0) as Late_min
from Agent ,Date
left outer join `productivity` p 
on p.Agent_Email = Agent.Agent_Email and p.Handled_DATE = Date.Date1
left outer join `Quality` q 
on q.Agent_Email = Agent.Agent_Email and q.Monitored_DATE = Date.Date1
left outer join `Absentieesm` a on a.Agent_Email = Agent.Agent_Email and a.Attendance_Date = Date.Date1
order by Agent_Email,Date1