Calculate Date Based on Start and End Date plus Existence of Specific Character in Tally Column

Question

question

LSC asked Feb 7, '20

Calculate Date Based on Start and End Date plus Existence of Specific Character in Tally Column

Hi there,

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:

1111111_11111111__111

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,

Andrew

date tally calculated column start on

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-02-08T02:49:56Z

Jon Crawford answered Feb 8, '20

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-02-07T15:34:41Z

LSC answered Feb 7, '20

Hi,

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2020-02-08T10:02:13Z

LSC answered Feb 8, '20

Hi Jon,

Thank-you for your reply, and for confirming that it's a LEFT JOIN plus another CTE that I needed.

Will definitely try out your suggestions.

Kind regards,

Andrew

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2020-03-11T17:30:25Z

Jeff Moden answered Mar 11, '20

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.

https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

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".

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

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.
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question