SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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')
;
12 rows affected

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


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


SELECT [date],
  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]


date
Total Enquiries
Apps Received
Apps Passed
Completed Sales


2020-01-01
1
1
1
1

2020-01-02
2
2
2
2

2020-01-03
1
1
1
1

2020-01-04
0
0
0
0

2020-01-05
0
0
0
0


Warning: Null value is eliminated by an aggregate or other SET operation.

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