You could try something like this

with rn_cte as (
    select *, case when row_number() over (partition by [Itinerary] order by [CreationDate])=1 and LegSegment is...

Here's 1, 3, and 4

--(1)
select v.sort_mo, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
     cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;

--(3)
declare @b  ...

It's an interesting approach Drew.  As I see it it's a partial self-CROSS JOIN then summarize instead of summarize then index JOIN.  My suspicion was confirmed using Jeff's test harness...

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

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