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: Actual GL Detail
| Source.Name   | Date   |   Document Amount | Document Date   |   GL Account Code |   Fund |   117 |   Source |   Program |   Department Number |   Project |   Sub Project | Document Description   |    Debit |   Credit | SessionID   | Transaction Source   | Name   | VendorID   | MIP DocNum   | Microix DocNum   | TransDesc   | Type   |   GL Values |
|:--------------|:-------|------------------:|:----------------|------------------:|-------:|------:|---------:|----------:|--------------------:|----------:|--------------:|:-----------------------|---------:|---------:|:------------|:---------------------|:-------|:-----------|:-------------|:-----------------|:------------|:-------|------------:|
| 7-31-20.CSV   |        |                 0 |                 |            120190 |    100 |    10 |   999999 |     99999 |              122000 |      9999 |           999 |                        |  1107    |        0 |             |                      |        |            |              |                  |             |        |     1107    |
| 7-31-20.CSV   |        |                 0 |                 |            120191 |    100 |    10 |   999999 |     99999 |              128000 |      9999 |           999 |                        | 41714.6  |        0 |             |                      |        |            |              |                  |             |        |    41714.6  |
| 7-31-20.CSV   |        |                 0 |                 |            120192 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        |   600    |        0 |             |                      |        |            |              |                  |             |        |      600    |
| 7-31-20.CSV   |        |                 0 |                 |            170100 |    100 |    10 |   999999 |     99999 |              152000 |      9999 |           999 |                        |  4871.34 |        0 |             |                      |        |            |              |                  |             |        |     4871.34 |
| 7-31-20.CSV   |        |                 0 |                 |            170140 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        |   432    |        0 |             |                      |        |            |              |                  |             |        |      432    |
| 7-31-20.CSV   |        |                 0 |                 |            180131 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        |   794.52 |        0 |             |                      |        |            |              |                  |             |        |      794.52 |
| 7-31-20.CSV   |        |                 0 |                 |            180132 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        |  1744    |        0 |             |                      |        |            |              |                  |             |        |     1744    |
| 7-31-20.CSV   |        |                 0 |                 |            201010 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        | 24816.3  |        0 |             |                      |        |            |              |                  |             |        |    24816.3  |
| 7-31-20.CSV   |        |                 0 |                 |            201051 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        | 50215.6  |        0 |             |                      |        |            |              |                  |             |        |    50215.6  |
| 7-31-20.CSV   |        |                 0 |                 |            201250 |    100 |    10 |   999999 |     99999 |              999999 |      9999 |           999 |                        |     1.17 |        0 |             |                      |        |            |              |                  |             |        |        1.17 |


Table: Budget
| Source.Name                               |   Department Number |   GL Account Code | Department Name   | GL Account Name                   | Budget Version   | Date                    |   Amount |   Fund |
|:------------------------------------------|--------------------:|------------------:|:------------------|:----------------------------------|:-----------------|:------------------------|---------:|-------:|
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            311010 | Research          | Grants Direct                     | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            320010 | Research          | Contracts Direct                  | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            320100 | Research          | Recovery of Overhead              | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            350370 | Research          | Sales Miscellaneous               | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            390010 | Research          | Misc. Income                      | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            391010 | Research          | Dept Transfers                    | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            392010 | Research          | NA Released from Prog Restriction | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            393010 | Research          | Endowment Transfers               | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            411010 | Research          | Salaries and Wages Regular        | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |
| Revised - 1-1-14 - 12-31-20 - Revised.CSV |              134000 |            413010 | Research          | Allocated Fringe Benefits         | Revised          | 2020-07-31 00:00:00.000 |        0 |    100 |


Table: Date Table
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | MonthName   | MonthInCalendar   | QuarterInCalendar   |   DayInWeek | DayOfWeekName   | WeekEnding              |   Week Number |   MonthnYear |   QuarternYear |   ShortYear | FY   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:------------|:------------------|:--------------------|------------:|:----------------|:------------------------|--------------:|-------------:|---------------:|------------:|:-----|
| 2014-07-01 00:00:00.000 |   2014 |               3 |             7 |            1 |  20140701 | July        | Jul 2014          | Q3 2014             |           2 | Tuesday         | 2014-07-05 00:00:00.000 |            27 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-02 00:00:00.000 |   2014 |               3 |             7 |            2 |  20140702 | July        | Jul 2014          | Q3 2014             |           3 | Wednesday       | 2014-07-05 00:00:00.000 |            27 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-03 00:00:00.000 |   2014 |               3 |             7 |            3 |  20140703 | July        | Jul 2014          | Q3 2014             |           4 | Thursday        | 2014-07-05 00:00:00.000 |            27 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-04 00:00:00.000 |   2014 |               3 |             7 |            4 |  20140704 | July        | Jul 2014          | Q3 2014             |           5 | Friday          | 2014-07-05 00:00:00.000 |            27 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-05 00:00:00.000 |   2014 |               3 |             7 |            5 |  20140705 | July        | Jul 2014          | Q3 2014             |           6 | Saturday        | 2014-07-05 00:00:00.000 |            27 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-06 00:00:00.000 |   2014 |               3 |             7 |            6 |  20140706 | July        | Jul 2014          | Q3 2014             |           0 | Sunday          | 2014-07-12 00:00:00.000 |            28 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-07 00:00:00.000 |   2014 |               3 |             7 |            7 |  20140707 | July        | Jul 2014          | Q3 2014             |           1 | Monday          | 2014-07-12 00:00:00.000 |            28 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-08 00:00:00.000 |   2014 |               3 |             7 |            8 |  20140708 | July        | Jul 2014          | Q3 2014             |           2 | Tuesday         | 2014-07-12 00:00:00.000 |            28 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-09 00:00:00.000 |   2014 |               3 |             7 |            9 |  20140709 | July        | Jul 2014          | Q3 2014             |           3 | Wednesday       | 2014-07-12 00:00:00.000 |            28 |     20140700 |       20140300 |          14 | FY15 |
| 2014-07-10 00:00:00.000 |   2014 |               3 |             7 |           10 |  20140710 | July        | Jul 2014          | Q3 2014             |           4 | Thursday        | 2014-07-12 00:00:00.000 |            28 |     20140700 |       20140300 |          14 | FY15 |


Table: Financial Statement Format
|   Sort Column | Revenue VS Expense   | Earned Income VS Contributed Income   |   Sort Earned Income VS Contributed Income | Financial Statement Grouping   |   GL Account Code | GL Account Name                | SOP Actuals Category   |
|--------------:|:---------------------|:--------------------------------------|-------------------------------------------:|:-------------------------------|------------------:|:-------------------------------|:-----------------------|
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421010 | Legal Fees                     | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421020 | Audit Fees                     | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421030 | Consultant Fees                | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421040 | Recruitment Fees               | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421050 | Fund Raising Fees              | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421060 | Market Research Fees           | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Professional Fees              |            421070 | Other Fees                     | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Membership and Subscriptions   |            481010 | Professional Membership Fees   | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Membership and Subscriptions   |            481020 | Business Subcriptions          | Expense                |
|             2 | Expense              | Non-Labor                             |                                          5 | Membership and Subscriptions   |            481030 | Organazational Membership Fees | Expense                |


Table: Project Title Helper Table
|   Project | Status   | Description                      | Project & Description                 |
|----------:|:---------|:---------------------------------|:--------------------------------------|
|        60 | D        | Bridge Scholarship Fund          | 60 - Bridge Scholarship Fund          |
|        61 | D        | Council Activities               | 61 - Council Activities               |
|        63 | D        | Special Sponsorship Project Fund | 63 - Special Sponsorship Project Fund |
|      1019 | D        | Lobster Room                     | 1019 - Lobster Room                   |
|      1080 | D        | Lutcavage/Tuna Funds             | 1080 - Lutcavage/Tuna Funds           |
|      1089 | D        | Bluefin Tuna Funding             | 1089 - Bluefin Tuna Funding           |
|      1153 | D        | NFWF/N. Atl RW Consortium        | 1153 - NFWF/N. Atl RW Consortium      |
|      1154 | D        | NSF/Behavioral Neuroscience      | 1154 - NSF/Behavioral Neuroscience    |
|      1157 | D        | NOAA/RW Catalog 02/03            | 1157 - NOAA/RW Catalog 02/03          |
|      1168 | D        | Kraus Discretionary              | 1168 - Kraus Discretionary            |


Table: SOP Actuals Measures - Harsh



Table: SOP Budgets Measures - Harsh



Table: SOP Comparison Measure - Harsh



Table: SOP Index
| Financial Statement Grouping   | Revenue VS Expense   |   Index |
|:-------------------------------|:---------------------|--------:|
| Gifts                          | Revenue              |       1 |
| Grants - Sponsored Programs    | Revenue              |       2 |
| Grants - Operational           | Revenue              |       3 |
| Contracts - Operational        | Revenue              |       4 |
| Admissions - Individual        | Revenue              |       5 |
| Admissions - Groups            | Revenue              |       6 |
| Admissions - Misc Programs     | Revenue              |       7 |
| Membership                     | Revenue              |       8 |
| Corp Sponsorship               | Revenue              |       9 |
| Events - Rentals               | Revenue              |      10 |

 


tables: name: Actual GL Detail
data: Actual GL Detail.csv
name: Budget
data: Budget.csv
name: Date Table
data: Date Table.csv
name: Financial Statement Format
data: Financial Statement Format.csv
name: Project Title Helper Table
data: Project Title Helper Table.csv
name: SOP Actuals Measures - Harsh
data: SOP Actuals Measures - Harsh.csv
name: SOP Budgets Measures - Harsh
data: SOP Budgets Measures - Harsh.csv
name: SOP Comparison Measure - Harsh
data: SOP Comparison Measure - Harsh.csv
name: SOP Index
data: SOP Index.csv
label: Financial Statement Format
Financial Statement Grouping
SOP Index
Financial Statement Grouping
Actual GL Detail
Date
Date Table
Date
Actual GL Detail
GL Account Code
Financial Statement Format
GL Account Code
Actual GL Detail
Project
Project Title Helper Table
Project
Budget
GL Account Code
Financial Statement Format
GL Account Code
Budget
Date
Date Table
Date
output: {
    "joins": [
        {
            "from_table": "Financial Statement Format",
            "from_column": "Financial Statement Grouping",
            "to_table": "SOP Index",
            "to_column": "Financial Statement Grouping"
        },
        {
            "from_table": "Actual GL Detail",
            "from_column": "Date",
            "to_table": "Date Table",
            "to_column": "Date"
        },
        {
            "from_table": "Actual GL Detail",
            "from_column": "GL Account Code",
            "to_table": "Financial Statement Format",
            "to_column": "GL Account Code"
        },
        {
            "from_table": "Actual GL Detail",
            "from_column": "Project",
            "to_table": "Project Title Helper Table",
            "to_column": "Project"
        },
        {
            "from_table": "Budget",
            "from_column": "GL Account Code",
            "to_table": "Financial Statement Format",
            "to_column": "GL Account Code"
        },
        {
            "from_table": "Budget",
            "from_column": "Date",
            "to_table": "Date Table",
            "to_column": "Date"
        }
    ]
}