'SQL - How to combine 3 dates from 3 different tables (BigQuery) [closed]

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

Basic Information:-

In the Absentieesm table it carries the information like this:

| Attendance_Date | Agent_Email           | Attendance | Late_min
|:-----------:    |:-------------:        |:----------:|:-----------:|
|2013-11-05       |  [email protected]  |    Attend  |    9
|2013-11-06       |  [email protected]  |    Sick    |    0
|2013-11-07       |  [email protected]  |    Sick    |    0
|2013-11-08       |  [email protected]  |    55      |   13
|2013-11-10       |  [email protected]  |    Attend  |    0

Desired Output

| DATE       | Agent_Email           | Handled | Missed | Business_Critical | Failed |Attendance|Late_min
|:---------- |:-------------:        |:-------:|:------:|:-----------------:|:-----: |:--------:|:------:|
|2013-11-05  |  [email protected]  |    80   |    9   |       2           |   2    |  Attend  |   9
|2013-11-06  |  [email protected]  |    0    |    0   |       1           |   1    |  sick    |   0
|2013-11-07  |  [email protected]  |    0    |    0   |       4           |   4    |  sick    |   0
|2013-11-08  |  [email protected]  |    0    |    0   |       0           |   0    |  sick    |   0
|2013-11-09  |  [email protected]  |    73   |    5   |       0           |   0    |  NA      |   0
|2013-11-10  |  [email protected]  |    64   |   14   |       0           |   0    |  Attend  |   0
sqlgoogle-bigquery


Solution 1:[1]

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

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 Arun

Related Questions