Avatar of SQLSearcher
SQLSearcher

asked on 

SQL Script to join to dates

Hello Experts Exchange
I have the following script;

DECLARE @startdate DATETIME
,@enddate DATETIME;
 
SET @startdate = '2016-03-06';
SET @enddate = '2016-03-20';
WITH calendardates
AS ( SELECT date1 = @startdate
UNION ALL
SELECT DATEADD(DAY, 1, date1)
FROM calendardates
WHERE DATEADD(DAY, 1, date1) <= @enddate
)
Select c.date1,[declared_at],[branch],[Day_Time2],[Shop_Time],[Shop_Time_Declared_At],[Store_Close_Time]
from [Bank_Activity_Declared_At_Time] BA
right JOIN calendardates c
ON DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at)) = c.date1
where branch = 227
and declared_at >='2016-03-06' 
and declared_at <= '2016-03-20'

Open in new window


There is two missing records for the 13th and the 18th, I want the date to return with all the other columns as null, I thought this query would do it but I must be missing something.

How do I fix the script?

Regards

SQLSearcher
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
SQLSearcher

8/22/2022 - Mon