The math could look something like this

declare @data table (app_time varchar(8), 
               ...

Here's a simple way which avoids counting

with
a_cte as (select distinct PartId from #FeaturesvalueA),
b_cte as (select distinct PartId from #FeaturesvalueB),
x_cte as ((select * from a_cte except select *...

Based on the data that's been provided the two tmp_day values are '01' and '02' which don't seem to vary by month.  As to which column is subordinate to the...

An alternative to PIVOT is conditional aggregation.  The sample data provided only contains 1 value for 'tmp_day' which seems to not agree with the output.  So I changed the sample...

In the first paragraph it says "Windowing functions are limited to the SELECT and OVER clause..."  Correction, windowing functions are permitted in both the SELECT list as well as the...

If the array is really JSON then Mark Cowne's answer seems correct.  If it's just a delimited string with repeating fixed length segments then something like this could work

            
February 24, 2021 at 4:31 pm
            
#3849639

            
            
                                                
        

A middle way could be to use the very nice integer division Jeff uses for the time parts but then use framework functions to handle type conversions and calculation.  Supposedly,...

One "brute force" way to do this would be to expand all the date ranges and use the set operator EXCEPT to remove the (also expanded) gaps.  Then the results...

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