SQL Script to join to dates

SQLSearcher
SQLSearcher used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>from [Bank_Activity_Declared_At_Time] BA  RIGHT JOIN calendardates c
Assuming this is a calendar table like what's in this article, then you need the FROM clause to be calendardates c LEFT JOIN {your other tables}, which will insure that all rows in calendardates are in the final set.  The RIGHT JOIN might work the way you have it though, you'll have to test..

>where branch = 227 and declared_at >='2016-03-06'  and declared_at <= '2016-03-20'
Not sure what table these columns are from, but you might also need to move these from WHERE to a JOIN..ON criteria for the LEFT/RIGHT JOIN to include all calendardates rows.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
< code to match the above comments, cleaned up a little >
DECLARE @startdate DATETIME= '2016-03-06', @enddate DATETIME= '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 calendardates c
   LEFT JOIN [Bank_Activity_Declared_At_Time] BA 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


>DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at))
btw explain this

>, @enddate DATETIME= '2016-03-20'
also be advised that the above value is a date but the column is a datetime, so your expresison will not include any values of the same date with a time involved other than midnight, such as '2016-03-20 12:34:56 PM'

Author

Commented:
Hello Jim
I tried your code and I still did not get records for the 13th and 18th.

DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at))
This is because declared_at field has a time of when the day was declared so I had to strip the time off so that it would match up with date1.

Regards

SQLSearcher
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
The WHERE conditions are effectively converting the JOIN to an INNER JOIN.  You need to specify all conditions for the left-table in the JOIN clause, not in the WHERE clause.  I also removed the function around the BA column to keep that argument "sargable".  Btw, I couldn't leave the recursive cte in my code as the filter at works "thinks" any "UNION ALL" is SQL injection(!), but just include that code there normally, as you had it in your original query.


DECLARE @startdate datetime
,@enddate datetime;
 
SET @startdate = '20160306';
SET @enddate = '20160320';
WITH calendardates
AS ( ...same as before...
)
Select c.date1,BA.[declared_at],BA.[branch],BA.[Day_Time2],BA.[Shop_Time],BA.[Shop_Time_Declared_At],BA.[Store_Close_Time]
from [Bank_Activity_Declared_At_Time] BA
right outer join calendardates c on BA.branch = 227
and BA.declared_at >= c.date1
and BA.declared_at < DATEADD(DAY, 1, c.date1)

Author

Commented:
Hello Jim
I got the query working, but I want to join to more tables and its stopped working.

Here is my other script;

DECLARE @startdate DATETIME= '2016-03-06', @enddate DATETIME= '2016-03-19';
 
WITH calendardates
AS ( 
   SELECT date1 = @startdate
   UNION ALL
   SELECT DATEADD(DAY, 1, date1)
   FROM calendardates
   WHERE DATEADD(DAY, 1, date1) <= @enddate
)
Select '5757' + BA.[branch] as LOC_LOC_CODE,LOC_NAME, Count([Store_Close_Time]) as Store_Close_Time From calendardates c 
	left join [Bank_Activity_Declared_At_Time] BA on
   DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at)) = c.date1
   inner join LOCATIONS L on
   '5757' + BA.[branch]  = L.LOC_LOC_CODE
   inner join [LOCATION_GROUPS] LG on
   L.LOC_ID = LG.LG_LOC_ID
   where (BA.[Store_Close_Time] = 1 or BA.[Store_Close_Time] is null)
   and LG.[LGT_DESCRIPTION] = 'SCOTLAND'
and declared_at >='2016-03-06' 
and declared_at <= '2016-03-19'
Group by '5757' + BA.[branch]

Open in new window


Do you know how I can fix?

Regards

SQLSearcher
Top Expert 2012

Commented:
Just curious, did you not see Scott's comments or did you not understand them?

Author

Commented:
Hello Anthony
I did not see Scott's Comments before my post but I have test his script and it did not work.

How do I get this script to work?

DECLARE @startdate DATETIME= '2016-03-06', @enddate DATETIME= '2016-03-19';
 
WITH calendardates
AS ( 
   SELECT date1 = @startdate
   UNION ALL
   SELECT DATEADD(DAY, 1, date1)
   FROM calendardates
   WHERE DATEADD(DAY, 1, date1) <= @enddate
)
Select '5757' + BA.[branch] as LOC_LOC_CODE,LOC_NAME, Count([Store_Close_Time]) as Store_Close_Time From calendardates c 
	left join [Bank_Activity_Declared_At_Time] BA on
   DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at)) = c.date1
   inner join LOCATIONS L on
   '5757' + BA.[branch]  = L.LOC_LOC_CODE
   inner join [LOCATION_GROUPS] LG on
   L.LOC_ID = LG.LG_LOC_ID
   where (BA.[Store_Close_Time] = 1 or BA.[Store_Close_Time] is null)
   and LG.[LGT_DESCRIPTION] = 'SCOTLAND'
and declared_at >='2016-03-06' 
and declared_at <= '2016-03-19'
Group by '5757' + BA.[branch]

Open in new window


Regards

SQLSearcher
Top Expert 2012

Commented:
I did not see Scott's Comments before my post but I have test his script and it did not work.
Since you have not stated what "did not work" it is very difficult to know what to respond.  Did you:
1. Get an error message, if so what was it.
2. Did you get the wrong output, if so what was it and what was the desired output.
3. Something else.

In the meantime I would strongly urge you to re-read Scott's solution, because you appear to be ignoring the very first sentence.  Here it goes again, in case you missed it:
The WHERE conditions are effectively converting the JOIN to an INNER JOIN.  

Author

Commented:
Hi Anthony
Thank you for your comment, I have managed to get the right SQL to get what I want.

Here it is;

DECLARE @startdate DATETIME= '2016-03-06', @enddate DATETIME= '2016-03-19';
 
WITH calendardates
AS ( 
   SELECT date1 = @startdate, Right(Loc_Loc_Code,3) as Loc_Loc_Code, Loc_Name, Loc_ID from Locations L inner join [LOCATION_GROUPS] LG on LOC_ID = LG.LG_LOC_ID  Where Loc_Loc_Code like '5757%' and LG.[LGT_DESCRIPTION] = 'SCOTLAND'
   UNION ALL
   SELECT DATEADD(DAY, 1, date1),Loc_Loc_Code,Loc_Name,Loc_ID
   FROM calendardates
   WHERE DATEADD(DAY, 1, date1) <= @enddate
)
SELECT '5757' + c.Loc_Loc_Code as LOC_LOC_CODE,LOC_NAME, Count(c.date1) as Store_Close_Time 
FROM calendardates c
   left JOIN [Bank_Activity_Declared_At_Time] BA ON 
   DATEADD(dd, 0, DATEDIFF(dd, 0, BA.declared_at)) = c.date1
   and c.Loc_Loc_Code = BA.branch
Where (BA.[Store_Close_Time] = 1 or BA.[Store_Close_Time] is null)
Group by '5757' + c.Loc_Loc_Code,LOC_NAME

Open in new window


Regards

SQLSearcher

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for SQLSearcher's comment #a41537768

for the following reason:

Found solution by myself.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Close request cancelled.  Thanks for posting your solution, but it seems like you've been given a ton of help here to just dismiss everyone with a 'I figured it out on my own, thanks for playing...' closing.  Did the above answers at least help you enough to warrant splitting points?

Author

Commented:
Yes Jim is right, I did get there with your help, so thank you for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial