Yes, parameters.

There's different school of thoughts over whether the identity column should be a candidate of clustered index or not (mostly in favour of having it as clustered index).

What's your generic...

Thanks a lot Scott. That helped. There's one more query which is not showing any issues but i doubt in future when the table size increases, it will start giving...

I was out because of Covid. Could you please help me with the dynamic query ?

Use the test data you were kind enough to post (nicely done there), here's an answer that uses a CROSSTAB.  If you actually DO want NULL to appear, just...

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 2 years, 9 months ago by  sqlenthu 89358.