CODEWITHSUNDEEP
sql
Melinda
Melinda

Reputation: 1531

Issue with SQL logic to use against my temp table

enter image description here

I am having a problem with what SQL logic to use in order to find in my temp table the policy no's (2nd column), like EAB1507017, where an Agent Refund was issued AFTER there was a balance of 0.00 (7th column, second from the end). You'll notice that the second policy listed, EAB1507600, was done correctly but EAB1507017 was NOT and I'm looking for those records in this temp table where the running balance was 0 BEFORE the Agent Refund entry was entered.

Here is my SQL code:

SELECT glJournalID, PolicyNo, Description, IsReceipt, TransactionDate, TransactionAmount, 
SUM(TransactionAmount) OVER (PARTITION BY PolicyNo ORDER BY glJournalID asc ROWS UNBOUNDED PRECEDING) as RunningBalance, 
UserID
FROM #tmpTableResults
ORDER BY PolicyNo asc, glJournalID asc;

Any help/direction would be appreciated. Thanks.

Upvotes: 0

Views: 45

Answers (1)

plalx
plalx

Reputation: 43718

Haven't tested, but I think this will give you the distinct PolicyNo that had an 'Agent Refund' immediately after the balance was 0.00. The key is to use ROW_NUMBER() in order to create a non-gapped sequence per PolicyNo allowing you to have precise lookaheads or lookbehinds.

WITH T AS (
    SELECT 
        PolicyNo, 
        SUM(TransactionAmount) OVER (
            PARTITION BY PolicyNo 
            ORDER BY glJournalID asc 
            ROWS UNBOUNDED PRECEDING
        ) AS RunningBalance,
        Description,
        ROW_NUMBER() OVER(PARTITION BY PolicyNo ORDER BY glJournalID) AS RowNum
    FROM #tmpTableResults
)
SELECT DISTINCT T.PolicyNo
FROM T
INNER JOIN T T2
    ON 
        T2.PolicyNo = T.PolicyNo
        AND T2.RowNum = T.RowNum + 1
        AND T2.Description = 'Agent Refund'
WHERE T.RunningBalance = 0.00

Upvotes: 1

Related Questions