To make more realistic (I guess) I added an additional row to t2 so the split requires 3 rows instead of 2.   Also, I added a UNIQUE CLUSTERED INDEX on...

Hi,

I did the query as bellow i don't know if will perfectly work for another data but with my sample data is work fine just one problem when i...

You're saying "split my data" but you've provided 9 rows of input data of which 8 rows do not (seemingly) require splitting.  Does my query correctly separate rows which DO...

Step1: convert the dates from INT to DATE

In  #table1 the date ranges overlap.  How to get rid of the overlaps?  My code updates t1 (my guess is the ranges are...

It's De Morgan's Law.  The negation of a conjunction is the disjunction of the negations

JObject seems to be a JSON object so you could use JSON_VALUE to select the fields

declare @json          ...

As a single statement something similar to the query posted earlier.  As has been mentioned more than once the WeekNo column in the example data is not sortable and it's...

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 3 years, 3 months 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...