This gives your expected results.  I partitioned and joined on the Sponsor, Pharmacy, and Drug.  You may need to change this.

WITH RunningTotals AS
(
    SELECT...

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

It doesn't appear that you've tried to incorporate the techniques that you've already been given.  Your latest post only has minor differences from your first post in this thread.  You...

I think you just need to add a partition to your ROW_NUMBER().

SELECT * FROM
(
    SELECT YEAR(OrderDate) AS OrderYear,
      ...

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

This seems to give your expected results despite not referring to the "dis" column at all.

WITH test_resets AS
(
    SELECT *,  SUM(CASE WHEN ompt...

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, 3 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...