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
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')
;