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
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>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 DudeCommented:
< 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'
SQLSearcherAuthor 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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott PletcherSenior DBACommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor 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
Anthony PerkinsCommented:
Just curious, did you not see Scott's comments or did you not understand them?
SQLSearcherAuthor 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
Anthony PerkinsCommented:
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.  
SQLSearcherAuthor 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
SQLSearcherAuthor 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 DudeCommented:
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?
SQLSearcherAuthor Commented:
Yes Jim is right, I did get there with your help, so thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.