Not to could/would come up with it to begin with.  Refactored to remove the CTE's.  The test returns no rows

DROP FUNCTION IF EXISTS dbo.CalendarByDateFirstRefactored;
go
CREATE FUNCTION dbo.CalendarByDateFirstRefactored
 ...

The function seems to generate the correct calendar.  Nicely done Jeff 🙂

select * from dbo.CalendarByDateFirst('20211201', '20220131', 7);

When I paste the OP's latest code into VS and run it it generates an incorrect calendar table.  My interpretation of the OP's unresponsiveness amounts to "just make my kludge...

The tricky part of normalization is populating the tables imo.  A lot of times it's an attempt to establish foreign key referential integrity post priori (after the fact).   You could...

• This reply was modified 4 years ago by Steve Collins.

I'm still thrown off by week 53.  What?  How does this work?  This topic is the same as this other one I'm assuming

These are ISO weeks?  If so filling in gaps using a tally function is probably not a good approach imo.  Needs a calendar table

In the original question above the WeekNo column is CHAR(3).  In your response to Phil I did not read past the word "Yes" (which is the 1st word) because it...

Suppose your 'WeekNo' column contains a 'W' and maybe/(maybe not) a '-' and the leading 0's and spaces are all over the place.  The key calculation is the numeric wk_num...

In which table is TechnologyId primary key?   Is there a linear dependency between PartId and TechnologyId?  Typically in general, if it's necessary to count (or rank) rows in order to...

Back in the  200#'s I had my own home server with 2008R2 installed.  It was a Dell box and I bought different disk drives, memory, and cards to experiment with...

One graceful solution could be for the vendor to address the issue they caused.  Another graceful way could be to rewrite the application so the DB Engine manages locks and...

Ok I updated the query so the ORDER BY is using the numeric 'wk_num' column instead of 'WeekNo' which is CHAR(3) and other slight changes too

with lead_cte...

• This reply was modified 4 years ago by Steve Collins.

You could fill in the missing weeks by using the LEAD(WeekNo) function to determine start/end points of the ranges.  To expand rows across the ranges you could use the...

• This reply was modified 4 years ago by Steve Collins.

Yes it's just an encoding.  How could one perform such an encoding without XML?  That's the hijacked topic 🙂  As an aside: I associated the encoding with a conversion because...

Something similar iiirc.  As I recall HASHBYTES were applied too.  It was to make offers which expired