Integer division comes in handy when creating groupings based on ranges of integers,  aka "buckets".  Something like this

select t.*, v.*, cycle.*
from #mytable t
    ...

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

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, 8 months ago by Steve Collins.
• This reply was modified 5 years, 8 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...