Ugh it's dependent on calculated row values.  Although painful to create, here's a WHILE loop which populates a new table '#myNewTable' with the ID and the derived theoretical_cycle column.  It's...

Does it also restart at 0 when aggregating the days?

The 'max_val' column matches the 'Days_Cycle' column in the table provided.  The 'theoritical_cycle' (in rows where ID is greater than 19) doesn't seem to follow the rules you're suggesting

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