Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Cancellation Reason
| Reasons for Cancellations                | Responsible   | Reporting categories     | Controllable   |
|:-----------------------------------------|:--------------|:-------------------------|:---------------|
| Adverse weather                          | others        | Weather and Environment  | No             |
| Asset Condition                          | Aurora        | Asset Condition          | No             |
| Change in work scope                     | Aurora        | Programming              | Yes            |
| Customer Impact                          | others        | Third Party Interference | No             |
| Customer outage mitigation not ready     | Contractor    | Construction planning    | Yes            |
| Customers not notified in time           | Aurora        | Outage Planning          | Yes            |
| External intervention                    | others        | Third Party Interference | No             |
| General planning deficiency              | Contractor    | Construction planning    | Yes            |
| Incident                                 | others        | Health & Safety          | Yes            |
| Incorrect asset data - not matching site | Aurora        | Asset Data               | Yes            |


Table: Date Slicer
| Period       | Date                    |   Sort |
|:-------------|:------------------------|-------:|
| Today        | 2021-05-17 00:00:00.000 |      1 |
| Yesterday    | 2021-05-16 00:00:00.000 |      2 |
| Last 7 Days  | 2021-05-11 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-12 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-13 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-14 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-15 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-16 00:00:00.000 |      3 |
| Last 7 Days  | 2021-05-17 00:00:00.000 |      3 |
| Last 10 Days | 2021-05-08 00:00:00.000 |      4 |


Table: Dates
| Date                    |   Year |   Quarter Of Year |   Month Of Year |   Day Of Month |   DateInt | Month Name   | Month In Calendar   | Quarter In Calendar   |   Day In Week | Day Of Week Name   | Week Ending             |   Week Number |   Month In Year |   QuarternYear |   Short Year | RY   |   Active Dates |   Month Year Sort | RY End Year             |   RY Month Number |   RY Year |   RY Month Year Sort |   RY Qtr | RY Start Year           |
|:------------------------|-------:|------------------:|----------------:|---------------:|----------:|:-------------|:--------------------|:----------------------|--------------:|:-------------------|:------------------------|--------------:|----------------:|---------------:|-------------:|:-----|---------------:|------------------:|:------------------------|------------------:|----------:|---------------------:|---------:|:------------------------|
| 1996-07-07 00:00:00.000 |   1996 |                 3 |               7 |              7 |  19960707 | July         | Jul 1996            | Q3 1996               |             0 | Sunday             | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-08 00:00:00.000 |   1996 |                 3 |               7 |              8 |  19960708 | July         | Jul 1996            | Q3 1996               |             1 | Monday             | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-09 00:00:00.000 |   1996 |                 3 |               7 |              9 |  19960709 | July         | Jul 1996            | Q3 1996               |             2 | Tuesday            | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-10 00:00:00.000 |   1996 |                 3 |               7 |             10 |  19960710 | July         | Jul 1996            | Q3 1996               |             3 | Wednesday          | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-11 00:00:00.000 |   1996 |                 3 |               7 |             11 |  19960711 | July         | Jul 1996            | Q3 1996               |             4 | Thursday           | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-12 00:00:00.000 |   1996 |                 3 |               7 |             12 |  19960712 | July         | Jul 1996            | Q3 1996               |             5 | Friday             | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1996-07-13 00:00:00.000 |   1996 |                 3 |               7 |             13 |  19960713 | July         | Jul 1996            | Q3 1996               |             6 | Saturday           | 1996-07-13 00:00:00.000 |            28 |        19960700 |       19960300 |           96 | RY97 |              0 |            199607 | 1997-03-31 00:00:00.000 |                 4 |      1997 |               199704 |        2 | 1996-04-01 00:00:00.000 |
| 1997-07-06 00:00:00.000 |   1997 |                 3 |               7 |              6 |  19970706 | July         | Jul 1997            | Q3 1997               |             0 | Sunday             | 1997-07-12 00:00:00.000 |            28 |        19970700 |       19970300 |           97 | RY98 |              0 |            199707 | 1998-03-31 00:00:00.000 |                 4 |      1998 |               199804 |        2 | 1997-04-01 00:00:00.000 |
| 1997-07-07 00:00:00.000 |   1997 |                 3 |               7 |              7 |  19970707 | July         | Jul 1997            | Q3 1997               |             1 | Monday             | 1997-07-12 00:00:00.000 |            28 |        19970700 |       19970300 |           97 | RY98 |              0 |            199707 | 1998-03-31 00:00:00.000 |                 4 |      1998 |               199804 |        2 | 1997-04-01 00:00:00.000 |
| 1997-07-08 00:00:00.000 |   1997 |                 3 |               7 |              8 |  19970708 | July         | Jul 1997            | Q3 1997               |             2 | Tuesday            | 1997-07-12 00:00:00.000 |            28 |        19970700 |       19970300 |           97 | RY98 |              0 |            199707 | 1998-03-31 00:00:00.000 |                 4 |      1998 |               199804 |        2 | 1997-04-01 00:00:00.000 |


Table: Field Service Provider
| FieldServiceProvider   |   Index |
|:-----------------------|--------:|
| Delta Central          |       0 |
| Delta Dunedin          |       1 |
| Unison                 |       2 |
| Other                  |       3 |
| Connetics              |       4 |
| NES                    |       5 |
| Electronet             |       6 |
| Ventia                 |       7 |


Table: Scheduled Works
| JobNumber   | ProjectManager   | Gate      | FieldServiceProvider   | DateRequestReceived     | OutagePlannedStart      | OutagePlannedEnd        | WorkType               | GridExitPoint   | JobDescription           | AreaofOutage   |   NumberofCustomersAffected | OutageNotificationRequired   | HasOutageBeenNotified   | CancellationDeferralReason   | ReasonDescription   |   DeferralNumber | DeferredDateandTime   | DeferredBy   | GateJobDeferredAt   | Date                    | Time                    |
|:------------|:-----------------|:----------|:-----------------------|:------------------------|:------------------------|:------------------------|:-----------------------|:----------------|:-------------------------|:---------------|----------------------------:|:-----------------------------|:------------------------|:-----------------------------|:--------------------|-----------------:|:----------------------|:-------------|:--------------------|:------------------------|:------------------------|
| J-1549-G    | Michelle Bone    | Completed | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-04-26 20:30:00.000 | 2021-04-28 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain NV T1 TX        |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-04-26 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1549-G    | Michelle Bone    | Active    | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-04-26 20:30:00.000 | 2021-04-28 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain NV T1 TX        |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-04-26 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1552-G    | Michelle Bone    | Active    | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-04-28 20:30:00.000 | 2021-04-30 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain NV T2 TX        |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-04-28 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1552-G    | Michelle Bone    | Active    | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-04-28 20:30:00.000 | 2021-04-30 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain NV T2 TX        |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-04-28 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1552-G    | Michelle Bone    | Completed | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-04-28 20:30:00.000 | 2021-04-30 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain NV T2 TX        |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-04-28 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1555-G    | Michelle Bone    | Active    | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-05-02 20:30:00.000 | 2021-05-03 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain E750, 752 & 753 |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-05-02 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1555-G    | Michelle Bone    | Completed | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-05-02 20:30:00.000 | 2021-05-03 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain E750, 752 & 753 |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-05-02 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1558-G    | Michelle Bone    | Active    | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-05-04 20:30:00.000 | 2021-05-06 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain E751, 754 & 755 |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-05-04 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1558-G    | Michelle Bone    | Completed | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-05-04 20:30:00.000 | 2021-05-06 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain E751, 754 & 755 |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-05-04 00:00:00.000 | 1899-12-30 20:30:00.000 |
| J-1558-G    | Michelle Bone    | Completed | Delta Dunedin          | 2021-03-10 00:00:00.000 | 2021-05-04 20:30:00.000 | 2021-05-06 04:30:00.000 | Substation Maintenance | Halfway Bush    | Maintain E751, 754 & 755 |                |                           0 | No                           | False                   |                              |                     |                0 |                       |              |                     | 2021-05-04 00:00:00.000 | 1899-12-30 20:30:00.000 |


Table: Time
| Time                    | Hour                    | Half Hour               | Quarter Hour            |   Minute |   Hour Number |   Half Hour Number | Next Hour   | Next Quarter Hour   | Period of Day   |   PeriodSort |   TimeKey |
|:------------------------|:------------------------|:------------------------|:------------------------|---------:|--------------:|-------------------:|:------------|:--------------------|:----------------|-------------:|----------:|
| 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        0 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         0 |
| 1899-12-30 00:01:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        1 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         1 |
| 1899-12-30 00:02:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        2 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         2 |
| 1899-12-30 00:03:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        3 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         3 |
| 1899-12-30 00:04:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        4 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         4 |
| 1899-12-30 00:05:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        5 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         5 |
| 1899-12-30 00:06:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        6 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         6 |
| 1899-12-30 00:07:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        7 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         7 |
| 1899-12-30 00:08:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        8 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         8 |
| 1899-12-30 00:09:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 | 1899-12-30 00:00:00.000 |        9 |             0 |                  0 | 1:00:00 AM  | 12:15:00 AM         | After Midnight  |            0 |         9 |


Table: Work Type
| WorkType                   |   Index |
|:---------------------------|--------:|
| Ring Main Unit Maintenance |       0 |
| Substation Maintenance     |       1 |
| Conductor/Cable Upgrade    |       2 |
| Other                      |       3 |
| Asset upgrade              |       4 |
| New Asset Installation     |       5 |
| Switch Maintenance         |       6 |
| Pole Replacement           |       7 |
| Streetlights               |       8 |
| Pole Repair                |       9 |

 


tables: name: Cancellation Reason
data: Cancellation Reason.csv
name: Date Slicer
data: Date Slicer.csv
name: Dates
data: Dates.csv
name: Field Service Provider
data: Field Service Provider.csv
name: Scheduled Works
data: Scheduled Works.csv
name: Time
data: Time.csv
name: Work Type
data: Work Type.csv
label: Date Slicer
Date
Dates
Date
Scheduled Works
Time
Time
Time
Scheduled Works
Date
Dates
Date
Scheduled Works
FieldServiceProvider
Field Service Provider
FieldServiceProvider
Scheduled Works
WorkType
Work Type
WorkType
Scheduled Works
CancellationDeferralReason
Cancellation Reason
Reasons for Cancellations
output: {
    "joins": [
        {
            "from_table": "Date Slicer",
            "from_column": "Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Scheduled Works",
            "from_column": "Time",
            "to_table": "Time",
            "to_column": "Time"
        },
        {
            "from_table": "Scheduled Works",
            "from_column": "Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Scheduled Works",
            "from_column": "FieldServiceProvider",
            "to_table": "Field Service Provider",
            "to_column": "FieldServiceProvider"
        },
        {
            "from_table": "Scheduled Works",
            "from_column": "WorkType",
            "to_table": "Work Type",
            "to_column": "WorkType"
        },
        {
            "from_table": "Scheduled Works",
            "from_column": "CancellationDeferralReason",
            "to_table": "Cancellation Reason",
            "to_column": "Reasons for Cancellations"
        }
    ]
}