It appears to be a nonworking attempt at a catch all query.  Imo the easy answer is two procedures or however many are necessary.  In this case imo the OP...

Since the current code uses REPLACE with strings of equal length followed by LEFT(8), it's equivalent to SUBSTRING(input_string, 38, 8)

select v.in_str, substring(v.in_str, 38, 8) inv_num
from (values ('Company...

You could try the PATINDEX function and look for the first occurrence of 8 consecutive integers enclosed in parentheses.  Instead of typing the criteria 8 times this uses REPLICATE.  The...

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

select cast(year(v.sort_mo) as smallint), input_str.mo
from (values ('Jan'),('Feb'),('Mar'),
             ('Apr'),('May'),('Jun'),
       ...

The data types were added.  It says:...

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 4 years, 2 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 4 years, 2 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...