I am trying to create a list of dates which correlate to a specific weekday (Wednesdays in this case), where I only have the start date, end date, and what can be described as some sort of tally column. I want to be able to use this list of dates in an SSRS report. The dates reflect the occurrence of a lesson taking place, and I will eventually be adding the room of where the lesson takes place but need to cross this bridge first.
The start date is 6th September 2017, the end date is 24th January 2018, and the tally column is a string a 1's:
There is a total of 21 characters in the above string, which represents the range of 21 weeks between the start and end dates. I suspect that the database structure was designed in this way to conserve disk space. The presence of a 1 in the tally column means that a lesson is scheduled to take place on the correlated date. The underscores in the tally column are not present in the actual tally column, but I've entered them to highlight the fact that it's just a space, which denotes a week/week where no lessons take place.
Those weeks are week 8, for 25th October 2017 and weeks 17 and 18 to represent the Christmas break for weeks 27th December 2017 and 3rd January 2018.
What I am after is that I want to generate a date for the weeks where the position of the string tally column contains a '1', and to not generate a date where there is an underscore (i.e. I want to ignore the weeks which are holidays).
I have been able to generate the dates of interest by tweaking the code contained in the post by kherlad69 at this link - https://www.sqlservercentral.com/forums/topic/get-particular-day-between-two-dates (post #1736478 - thank-you!), but I am not able to exclude the holiday weeks. I have tried to use CROSS APPLY to achieve what I want, but didn't get very far (I probably need a LEFT OUTER JOIN), so am asking if anybody knows how to accomplish what I'm after.
Thanks in advance for your help,
Answer by Jon Crawford ·
use a CTE first to declare your holiday dates, then add a LEFT JOIN to the CTE WHERE someDate IS NULL (so it only gives you the ones NOT in the CTE).
Or, set up a date table (just google that, should be a ton of variations on that for you) and set flags on dates that are holidays. Very useful to have a date table for lots of reasons, won't be time wasted if you go that route.
Answer by Jeff Moden ·
First of all, I strongly recommend NOT using rCTEs (Recursive CTEs) like what kherlad69 used in his script even for small stuff. Please see the following article for WHY I say that with demonstrable proof.
If you download and execute the following code, you'll have a wonderful tool for an extremely fast and readless function that will easily generate numeric sequences from 0 or 1 to some number. Some have called its big brother, the Tally Table, the "Swiss Army Knife of SQL Server".
If I've read your problem description correctly, the problem now becomes child's play. Here's an example with a variable...
DECLARE @pStartDate DATETIME = ' 6 September 2017' ,@pEndDate DATETIME = '24 January 2018' ,@TallyColumn VARCHAR(500) = '1111111_11111111__111' ; SELECT CASE WHEN SUBSTRING(@TallyColumn,t.N,1) = '1' THEN DATEADD(dd,(t.N-1)*7,@pStartDate) ELSE NULL END FROM util.fnTally(1,LEN(@TallyColumn)) t WHERE SUBSTRING(@TallyColumn,t.N,1) = '1' --Comment out if you want to see the NULLs for the missing dates ;
If you provide a small table of data (see the following article on how to make it readily consumable), I'll show you how to do it against the whole table all at once using a CROSS APPLY.
Answer by LSC ·
I ended up ignoring the tally column by adding the following snippet of code to the end of the code in kherald69's post as mentioned in my initial posting (added after WHERE DATEPART(WEEKDAY, [Date]) = @dayOfWeek):
AND [date] NOT BETWEEN '2017-10-23' AND '2017-10-29'
AND [date] NOT BETWEEN '2017-12-25' AND '2018-01-05'
Which gives me the dates that I was originally after, minus the weeks which are holidays:
2017-09-06 00:00:00.000 Wednesday
2017-09-13 00:00:00.000 Wednesday
2017-09-20 00:00:00.000 Wednesday
2017-09-27 00:00:00.000 Wednesday
2017-10-04 00:00:00.000 Wednesday
2017-10-11 00:00:00.000 Wednesday
2017-10-18 00:00:00.000 Wednesday
2017-11-01 00:00:00.000 Wednesday
2017-11-08 00:00:00.000 Wednesday
2017-11-15 00:00:00.000 Wednesday
2017-11-22 00:00:00.000 Wednesday
2017-11-29 00:00:00.000 Wednesday
2017-12-06 00:00:00.000 Wednesday
2017-12-13 00:00:00.000 Wednesday
2017-12-20 00:00:00.000 Wednesday
2018-01-10 00:00:00.000 Wednesday
2018-01-17 00:00:00.000 Wednesday
2018-01-24 00:00:00.000 Wednesday
There is definitely a more elegant way of achieving the above using the tally column, which also means that the code is 'perpetual' (i.e. no need to declare the holiday weeks for each academic year), but I don't know how to do it :(.
The above will help me to go on and create the SSRS report which is needed by Monday 10th February.