Well well, this is the first time I can recall ever using a RIGHT JOIN in a "real" query.  It seems to make sense here.

declare @dt ...

The data looks something like this?

drop table if exists #conditions;
go
create table #conditions(
   some_dt      date not null,
   code ...

DECLARE @v_order_id INT = 1;

WITH
myData AS(
SELECT 'all_codes_set' AS code_type, 
1 AS start_id,
100 AS end_id,
1 AS order_id
UNION ALL
SELECT 'all_codes_set' AS code_type, 
101 AS start_id,
110 AS end_id,
2 AS order_id
UNION...

Ok, the non-tvf same approach uses an additional CTE and SELECT TOP(n).  The following code is to be embedded in the SQL to replace dbo.fnTally.  The maximum # of rows...

The fastest way to reliably get an answer is not necessarily the "best" way but it's functional.  Afaik something like this would work.  It uses a tally function to expand...

Yes the LAST_VALUE function is a windowing function.  One way to summarize would be to use a common table expression.

with lv_cte(PrimaryKey, SearchValue, OtherNumericValue, LastValue) as (
 ...

There's the LAST_VALUE function.  In order to return the "last value" (when the set is ordered by:  OVER (ORDER BY ...)) across the entire window of rows (in this case...

Style-wise, the joins are specified in "more specific to less specific" order which imo is not the standard way and it's less read-able.  Without knowing anything about the underlying data...

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

The query references "a couple of joined tables" but the code listed only has 1 table 't' in the FROM clause.  The error message "multi-part identifer ..." happens when the...

The nested subqueries seem not to be necessary imo.  What is the point of selecting the top 15 rows only to select the top 1 from that subset using the...

It appears the OP is referencing Excel functions and column arrays.  In this query the calculated NewColumn equals the DesiredColumn.

drop table if exists #tTable;
go
create table #tTable(
 ...

Yes, the other 13 start with 0, meaning 'dbo.fnTally(0,...

I use 2 monster .sql scripts to answer SSC/SO questions.  One currently has 13,787 lines and contains 32 occurrences of 'fnTally' of which 24 specify the sequence begin with 1. ...

Those are my two choices so...

Getting back to Itzik's challenge, I had a question form in my mind.  As a pre-amble to the question, I've found that I use "1" as a starting number...