Again... if you post some sample data in a readily consumable format, we can help a whole lot more.  See the first link in my signature line below for...

I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

businessday category itemno value
2022-01-01...

I have gone through the two parts of Jeff's articles. But I still couldn't figure out the solution. here's my sample data:

businessday category itemno value
2022-01-01 xyz banana...

My bad Jeff. Apologies and i am making up for that mistake by making you CEO of the example. 🙂 Here is the code:

This is the hierarchy table:

            
September 27, 2021 at 1:27 pm
            
#3933480

            
            
                                                
        

Really that's good. And don't know why i couldn't think of except clause (maybe overthinking).

Apologies but i have another set of question. Query actually is little different as i am...

Lets say this is the second month data with some changes in hierarchy:

insert into dbo.EmpHierarchy
select '2021-09-01', 1, '1001', 'Jeff Moden', '-1', NULL, NULL, NULL, NULL, NULL, NULL,...

My bad Jeff. Apologies and i am making up for that mistake by making you CEO of the example. 🙂 Here is the code:

This is the hierarchy table:

This reply was modified 3 years, 2 months ago by  sqlenthu 89358.