The input from Excel are key value pair(s)?  Suppose the inputs were inserted into a temporary SQL Server table.  Would/could it look something like this?

create table ...

Nice catch yes I changed/fixed from:

 where s.transactiondate > dateadd(month, -3, dt.t_dt)

to:

where s.transactiondate > eomonth(dt.t_dt, -3)

Using EOMONTH could still be considered risky if the...

The top query has 4x fewer logical reads.  The issue afaik with with combining GROUP BY and CROSS APPLY  in a single SELECT statement is the logical reads are based...

• This reply was modified 3 years, 5 months ago by Steve Collins.

This query returns no rows

with 
gaps_cte(CalDate, [DayName] ,BusinessDay, Multiplier, Comment, gap) as (
    select *, case when BusinessDay<>lag(BusinessDay) over (order by CalDate) then 1...

This is a classic "gaps and islands" type situation imo. Not sure what's going on with SSC tho. There's no text editor and the font is really tiny...

The function dbo.fnTally can be found and explained here

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
      ...

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);