The problem is the issue_id.  Just remove it from the GROUP BY.  I used it in the GROUP BY, because it was in your original query.

Also, if your master_ind is...

There are two potential solutions depending on whether the child records have the same category_id and ldk_id as the master record.  (NOTE: this is where sample data would be REALLY...

This gives you the expected results.  You'll probably need to feed the CTE into a MERGE statement to get the updates that you need.

WITH Temp_Changes AS
(
 ...

For best performance, you want to stop using NULL so you don't have to do ISNULL() as part of the WHERE.  Yes, you will have to go back and...

Your ValidFrom and ValidUntil are DATETIME2(7).  GETDATE() returns DATETIME, so you have a mismatch in the precision.  Try using SYSDATETIME() instead of GETDATE().

Drew

This looks like a Packing Intervals problem.  Based on your expected output, I am using closed intervals.  That is, both endpoints are considered to be included in the interval, so...

Instead of repeatedly saying "two or more tables", I would phrase it as "two row sources".  The main benefit of this is to clarify that you are only ever considering...

This needs to be done in the presentation layer.  SQL queries and stored procedures are the data layer, not the presentation layer.  You need to do this in SSRS, Tableau,...

Also, many people hesitate to download files from the Internet.  You should create expected results in the same way that you created your sample data.

Drew

You've made a very common mistake when working with intervals.  You're comparing starts with starts and ends with ends.  Instead, you should be comparing whether each interval starts before the...

Your sample data does not include the value 1900-01-01, yet you expect it in your output.  Those rows are missing, because your sample data does not match your expected output. ...

This is a very basic attempt to solve your problem.  It does not match your expected data, but, as others have pointed out, your expected data may be missing rows. ...

Use ROW_NUMBER() rather than a loop.  You would set up your ROW_NUMBER() so that the latest record is row number 1 and historical records are 2+.  If you want more...

This is based on code by Itzik Ben-Gan.  It uses a cross apply and a sum windowed function to increment the open interval count for start times and decrement it...

The subqueries are disastrous for performance.  Here is a query that gives the same results (I also added the requested SORT_NBR column) with much better performance.

SELECT b.BASKET_ID
...