sqlleft-joincasebetween

How do I Join two SQL tables on Multiple date columns


My first table is Called Date_Table this has all the days of the year dating back to January 2020 and goes all the way to 31 Decemeber 2030.The data looks like this:

Date Day
01/01/2020 00:00 Wed
02/01/2020 00:00 Thur

My second table Sales_Table consists of sales stages, each stage has a date assigned when it reaches the stage. Here is what my data looks like:

Sale_ID Enquiry App Rec App Passed Sale Completed
1 01/01/2020 00:00 02/01/2020 00:00 03/01/2020 00:00 05/01/2020 00:00
2 02/01/2020 00:00 03/01/2020 00:00 03/01/2020 00:00 05/01/2020 00:00

My query is this so far:

SELECT COUNT(CASE WHEN [Enquiry] IS NOT NULL THEN 1
 END) AS [Total Enquiries],
      COUNT(CASE WHEN [App Rec] IS NOT NULL THEN 1
 END) AS [Apps Received],
      COUNT(CASE WHEN [App Passed] IS NOT NULL THEN 1
 END) AS [Apps Passed],
      COUNT(CASE WHEN [Sale Completed] IS NOT NULL THEN 1
 END) AS [Completed Sales]
FROM [Date_Table]
      LEFT JOIN [Sales_Table] ON [Date] Between [Enquiry] AND [App Rec] GROUP BY [Date]

This seems to work fine for the first 2 dates but if i add another AND statement in the join I get a parsing of SQL query failed error.

The end report would be a table like this:

Date Enquiry App Rec App Passed Sale Completed
01/01/2020 00:00 3 6 4 2
02/01/2020 00:00 5 1 11 4

I tried to add all the dates fields to the left join using BETWEEN and AND Like this: LEFT JOIN [Sales_Table] ON [Date] Between [Enquiry] AND [App Rec] AND [App Passed] AND [Sale Completed]

This just gave me a parsing error


Solution

  • See example

    with t as(
    select 'Enquiry'event,Enquiry as dt
    from Sales_Table
    union all
    select 'App_Rec',App_Rec
    from Sales_Table
    union all
    select 'App_Passed',App_Passed
    from Sales_Table
    union all
    select 'Sale_Completed',Sale_Completed
    from Sales_Table
    )
    ,totals as(
    select dt
       ,sum(case when event='Enquiry' then 1 else 0 end)Enquiry
       ,sum(case when event='App_Rec' then 1 else 0 end)App_Rec
       ,sum(case when event='App_Passed' then 1 else 0 end)App_Passed
       ,sum(case when event='Sale_Completed' then 1 else 0 end)Sale_Completed
    from t
    group by dt
    )
    select *
    from Date_Table d
    left join totals t on t.dt=d.date
    

    Output is

    Date Day dt Enquiry App_Rec App_Passed Sale_Completed
    2020-01-01 Wedn 2020-01-01 1 0 0 0
    2020-01-02 Thur 2020-01-02 1 1 0 0
    2020-01-03 Frid 2020-01-03 0 1 2 0
    2020-01-04 Satu null null null null null
    2020-01-05 Sund 2020-01-05 0 0 0 2

    with test data

    create table Date_table(Date date,  Day varchar(10));
    insert into Date_Table ([date]) values 
     ('2020-01-01')
    ,('2020-01-02')
    ,('2020-01-03')
    ,('2020-01-04')
    ,('2020-01-05')
    ;
    update Date_Table
      set [Day]=left(datename(dw,[date]),4)
    ;
    create table Sales_Table (Sale_ID int,Enquiry date,App_Rec date, App_Passed date, Sale_Completed date);
    insert into Sales_Table values
     (1,'2020-01-01','2020-01-02','2020-01-03','2020-01-05')
    ,(2,'2020-01-02','2020-01-03','2020-01-03','2020-01-05')
    ;
    

    Fiddle