CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
DVHeld
DVHeld

Reputation: 77

Discarding certain related rows in a query

I have a wide table, but only 5 columns are relevant for the problem. Let's say the table is like this:

Bsns_ID | Vch_ID | Vch_Line | Pay_Status | Pay_ID | Bank | Amnt
COM04   | 00001  |        1 | Received   | 000001 | BK01 | 2970
COM04   | 00001  |        1 | Pending    | NULL   | NULL |   30
COM01   | 00352  |        1 | Received   | 000832 | BK98 | 3000
COM01   | 00352  |        2 | Received   | 000967 | BK98 | 6784
COM01   | 00352  |        2 | Received   | 000834 | BK98 | 6784
COM33   | 00023  |        1 | Received   | 000076 | BK43 | 4950
COM33   | 00023  |        1 | Pending    | NULL   | NULL |   50
COM02   | 00065  |        1 | Pending    | 000804 | BK45 | 9946

Bsns_ID, Vch_ID, Vch_Line are my compound key that identify a specific item, but an item can have multiple payments, so the full ID for a specific row includes the Pay_ID column.

What I need is to sum the amount of any row that satifies Pay_Status = 'Pending' and Pay_ID = NULL with the amount in its related rows with same Bsns_ID, Vch_ID, Vch_Line, and discard the row in question. The result of the sum must replace the Amnt value of the related row. A sample result would be like so:

Bsns_ID | Vch_ID | Vch_Line | Pay_Status | Pay_ID | Bank | Amnt
COM04   | 00001  |        1 | Received   | 000001 | BK01 | 3000 --(2970 + 30)
COM01   | 00352  |        1 | Received   | 000832 | BK98 | 3000
COM01   | 00352  |        2 | Received   | 000967 | BK98 | 6784
COM01   | 00352  |        2 | Received   | 000834 | BK98 | 6784
COM33   | 00023  |        1 | Received   | 000076 | BK43 | 5000 --(4950 + 50)
COM02   | 00065  |        1 | Pending    | 000804 | BK45 | 9946

With the following rows not present:

COM04   | 00001  |        1 | Pending    | NULL   | NULL |   30
COM33   | 00023  |        1 | Pending    | NULL   | NULL |   50

Edit: I'm working in Snowflake.

Upvotes: 1

Views: 47

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

with pendings as (
    select Bsns_ID
        ,Vch_ID
        ,Vch_Line
        ,sum(Amnt) as s_Amnt
    from TABLE_NAME
    where pay_id is null
    group by 1,2,3
)
sekect a.Bsns_ID
    ,a.Vch_ID
    ,a.Vch_Line
    ,a.Pay_status
    ,a.Pay_ID
    ,a.Bank
    ,a.Amnt + coalesce(p.Amnt,0) as Amnt
from TABLE_NAME as a
left join pendings as p on a.bsns_id = p.bsns_id and a.vch_id = p.vch_id and a.Vch_Line = p.Vch_Line;

But like @GordonLinoff implies if COM01 | 00352 | 2 was to have a third line with a 'pending' it will match two lines.. the gap is you imply pay_id should be used as the forth unique key, but then ask to have all null pay_id keys added to all non-null rows which seems dangerous.

Upvotes: 1

Related Questions