How do we reset 'OVER(ORDER BY <FIELD> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Based on Criteria?

Question

I am trying to reset the

    SUM(ISNULL(T1.Debit,0) - ISNULL(T1.Credit,0)) OVER(ORDER BY T1.RefDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 'Balance at Current Time'

every time the current row's cell of column 1 is different than the previous one, without having to create a CURSOR that makes the query execution time take about 10 minutes. What seems to be the most efficient way to perform this action in Terms of Speed.

I Converted the query to run with a cursor that loops through the changing field and the SUM resets when it has to.

Initial Query Without Reset

DECLARE @fd datetime
DECLARE @td datetime
SET @fd='20001231'
SET @td='20191231'
SELECT 
    T2.CardCode,
    T2.CardName,
    T1.TransId,
    T1.TransType,
    T1.BaseRef,
    CASE 
    WHEN T1.TransType=13 THEN
    (SELECT Z1.CANCELED FROM OINV Z1 WHERE Z1.TransId=T1.TransId)
    WHEN T1.TransType=14 THEN
    (SELECT Z1.CANCELED FROM ORIN Z1 WHERE Z1.TransId=T1.TransId)
    WHEN T1.TransType=18 THEN
    (SELECT Z1.CANCELED FROM OPCH Z1 WHERE Z1.TransId=T1.TransId)
    WHEN T1.TransType=19 THEN
    (SELECT Z1.CANCELED FROM ORPC Z1 WHERE Z1.TransId=T1.TransId)
    WHEN T1.TransType=24 THEN
    (SELECT Z1.CANCELED FROM ORCT Z1 WHERE Z1.TransId=T1.TransId)
    WHEN T1.TransType=46 THEN
    (SELECT Z1.CANCELED FROM OVPM Z1 WHERE Z1.TransId=T1.TransId)
    ELSE 'N' END [Canceled],
    T1.RefDate,
    T1.DueDate,
    T1.TaxDate,
    T1.LineMemo,
    ISNULL(T1.Debit,0) [Debit],
    ISNULL(T1.Credit,0) [Credit],
    ISNULL(
    (SELECT SUM(ISNULL(Z1.Debit,0) - ISNULL(Z1.Credit,0)) 
        FROM OJDT Z0 LEFT OUTER JOIN JDT1 Z1 ON Z1.TransId=Z0.TransID 
        WHERE Z1.ShortName=T2.CardCode AND (Z1.TransType=-2 OR Z0.RefDate<=@td)),0) [Balance],


        SUM(ISNULL(T1.Debit,0) - ISNULL(T1.Credit,0)) OVER(ORDER BY T1.RefDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 'Balance at Current Time'




FROM OJDT T0
LEFT OUTER JOIN JDT1 T1 ON T1.TransId=T0.TransId
LEFT OUTER JOIN OCRD T2 ON T2.CardCode=T1.ShortName

WHERE ((T0.RefDate>=@fd AND T0.RefDate<=@td)

AND ISNULL(T1.ShortName,'') in (Select CardCode from OCRD where CardType = 'C'))
ORDER BY CardCode, RefDate

Cursor Query Includes the Above within the loop area and fetches each CardCode

With the Cursor the query is still executing as we speak and it has been over 10 minutes. The base query i posted above finishes running in 1-2 seconds max.

Answer 1

It seems like what you're looking for is a 'partition by' clause. This will reset the sum window for every new card code encountered

SUM(ISNULL(T1.Debit,0) - ISNULL(T1.Credit,0)) OVER( partition by T2.CardCode  ORDER BY T1.RefDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 'Balance at Current Time'