Quick suggestion for a solution, if I got the description right;-)
😎
;WITH BASE_DATA(
DEP_ACCOUNT_NO
,DEP_SERVICED_AMT
,DEP_TRANSACTION_VALUE_DATE
,DEP_SERVICED_FLAG
,CRED_ACCOUNT_NO
,CRED_TRAN_AMT
,CRED_SERVICED_FLAG
,CRED_TRANSACTION_VALUE_DATE)
AS (
SELECT * FROM (VALUES
('33105126375',286533.00,'2014-08-30 00:00:00.000',0,'33105126375',865800.00,0,'2014-06-27 00:00:00.000')
,('33105126375',286533.00,'2014-08-30 00:00:00.000',0,'33105126375',130000.00,0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',1767.29, 0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',9801.14, 0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',85452.96,0,'2014-06-27 00:00:00.000')
,('45505000661',1013088.00,'2014-08-30 00:00:00.000',0,'45505000661',10706.27,0,'2014-06-27 00:00:00.000')
,('45605107980',19522.00,'2014-08-30 00:00:00.000',0,'45605107980',15442.81,0,'2014-06-27 00:00:00.000')
) AS X(
DEP_ACCOUNT_NO
,DEP_SERVICED_AMT
,DEP_TRANSACTION_VALUE_DATE
,DEP_SERVICED_FLAG
,CRED_ACCOUNT_NO
,CRED_TRAN_AMT
,CRED_SERVICED_FLAG
,CRED_TRANSACTION_VALUE_DATE)
)
SELECT
BD.DEP_ACCOUNT_NO
,BD.DEP_SERVICED_AMT
,BD.DEP_TRANSACTION_VALUE_DATE
,CASE WHEN BD.DEP_SERVICED_AMT - SUM(BD.CRED_TRAN_AMT) OVER
(
PARTITION BY BD.CRED_ACCOUNT_NO
ORDER BY BD.CRED_TRANSACTION_VALUE_DATE
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) > 0 THEN 0 ELSE 1 END AS DEP_SERVICED_FLAG
,BD.CRED_ACCOUNT_NO
,BD.CRED_TRAN_AMT
,CASE WHEN BD.DEP_SERVICED_AMT - SUM(BD.CRED_TRAN_AMT) OVER
(
PARTITION BY BD.CRED_ACCOUNT_NO
ORDER BY BD.CRED_TRANSACTION_VALUE_DATE
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) > 0 THEN 0 ELSE 1 END AS CRED_SERVICED_FLAG
,BD.CRED_TRANSACTION_VALUE_DATE
FROM BASE_DATA BD;
Results
DEP_ACCOUNT_NO DEP_SERVICED_AMT DEP_TRANSACTION_VALUE_DATE DEP_SERVICED_FLAG CRED_ACCOUNT_NO CRED_TRAN_AMT CRED_SERVICED_FLAG CRED_TRANSACTION_VALUE_DATE
-------------- ----------------- -------------------------- ----------------- --------------- -------------- ------------------ ---------------------------
33105126375 286533.00 2014-08-30 00:00:00.000 0 33105126375 130000.00 0 2014-06-27 00:00:00.000
33105126375 286533.00 2014-08-30 00:00:00.000 1 33105126375 865800.00 1 2014-06-27 00:00:00.000
45505000661 1013088.00 2014-08-30 00:00:00.000 0 45505000661 10706.27 0 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 85452.96 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 9801.14 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 15442.81 1 2014-06-27 00:00:00.000
45605107980 19522.00 2014-08-30 00:00:00.000 1 45605107980 1767.29 1 2014-06-27 00:00:00.000