Thanks for your feedback Phil,

The reason for the layout of my sample data was just for simplicity.  I inserted my sample data into a physical table and then just...

Here is what I came up with.  If I had more time, I'd rewrite it to use closed-open intervals rather than open-closed intervals.  (That is, I'd include the start date,...

I prefer to use OFFSET/FETCH NEXT to run batches of records in a loop.  I've updated your query to reflect that.

• I replaced your @RowCount, @StartRow, and @EndRow with a...

I didn't see anything specifying a filter based on order.  The OP...

• This reply was modified 1 year, 11 months ago by drew.allen.

When posting sample data to the web, you do not need to post EVERY SINGLE FIELD IN YOUR TABLE.  More importantly, you could be in trouble for posting PII

Any data type can be converted to binary, and it's impossible to tell from what you have given us what data type to convert back to.  I tried VARCHAR, NVARCHAR,...

I can never...

Instead of using CONCAT() use QUOTENAME(), in case your text contains single quotes.  It will automatically create escaped forms of single quotes in...

For a running total - you can use: thisBalance = SUM(thisAmt) OVER(PARTITION BY Batch_Id ORDER BY Id)

If you want to break out the totals even further, add those to...

I've updated the code to a) use a temp table instead of a permanent table.  Here is the setup code:

DROP TABLE IF EXISTS #charges;

CREATE TABLE #charges (
...

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