To generate a sequence why not use an explicit virtual table of the numbers themselves?   Here's a .txt file with a sql script and it defines a tvf called dbo.fn1k...

Yes plenty of time.  Lots of time.  Eons and eons.  More than Ten Centuries 🙂  Enough to fill BIGINT.  It could be dialed back as appropriate.

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

The place to start imo is with this very awesome article from Jeff.  I agree the code is not efficient.  The key is to have a 'row goal' in...

There were a few issues with the data which required fixing before a query could run.  The VARCHAR columns require a character length, in this case I made them both...

Hi Lonnie, thanks for the follow up.  Since inside the WHILE loop the variable @i represents the theoretical cycle, perhaps it makes sense to change the WHERE clause to

            
February 16, 2021 at 6:32 pm
            
#3845778

            
            
                                                
        

To the OP it makes clear sense no doubt.  In this case the OP says they have part of the solution but only offer pictures of the problem.  Maybe we...

It seems dfp(start_dt) should've been dfp(dt) in two places.   This is a really slow way to debug 🙂

with
dt_cte(dt) as (
    select dateadd(month, fn.n, dfp.start_dt)
...

dfp(start_dt) is (or was intended to be) the alias (with column label) for the object defined by the VALUES table value constructor.  It appears there some were missing parentheses in...

By "regroup"  does it mean "round and expand"?  Otherwise, how to handle the decimal precision?  To create 1 foot increments based on rounded ranges you could use a tally...

ktflash, afaik it's almost a correct "quick fix".  TBL1 is a recursive CTE which generates a sequence of months (1 to 12).  Presumably this date range is to be adjusted...

Afaik this helps going in the other direction, from @thedate to iso week.  What about iso week, iso month, iso year to calendar date?

Thanks.  I'll look at it in a bit more detail later but my first impression is that the following line of code in the CROSSAPPLY is incorrect.

            
February 8, 2021 at 6:44 pm
            
#3842597

            
            
                                                
        

Is this still the best way to do this in 2021?  Google landed me at another SSC Forum from 2005, which I can't find anymore, and now I'm...

Is this still the best way to do this in 2021?  Google landed me at another SSC Forum from 2005, which I can't find anymore, and now I'm using this

This seems to return output similar to the picture.  Is it just missing a total row?  What's not working?