pzozulka
asked on
SQL Row_Number() help
When joining the below tables, I am getting additional (unwanted/duplicate) rows.
The code above produces the following results:
TransactionId PartyId Amount Date PaymentCheckId
653232 144138 103.00 2015-04-02 2976
653233 144114 104.00 2015-04-02 2976
PaymentCheckId CheckNumber FeeAmount
2976 7299 8.00
PaymentCheckId CustomerAccountId Amount
2976 144138 103.00
2976 144114 104.00
Desired Results:
This is an example of bulk payment. Meaning a single PaymentCheck was used to make a payment for multiple CustomerAccountId's/PartyI d's. The FeeAmount related to the PaymentCheck is not a fee for each PartyId. It is a fee for the PaymentCheck. So the first FeeAmount should be the FeeAmount, but all others related to this PaymentCheckId should be 0.00.
TransactionId PartyId Amount Date PaymentCheckId CheckNumber FeeAmount
653232 144138 103.00 2015-04-02 2976 7299 8.00
653233 144114 104.00 2015-04-02 2976 7299 0.00
My only problem is that when I join to the PaymentCheckRelatedAccount , my results are doubled / duplicated.
The ONLY reason I need to even join to this table is because I found a solution to my above problem using ROW_NUMBER(), but now my records are duplicating. Instead of 2 rows, I get 4. I tried using SELECT DISTINCT, and that removes one of the rows, but still, there's an extra row.
Here's the query I'm trying to use:
Results:
TransactionId PartyId Amount Date PaymentCheckId CheckNumber FeeAmount ServiceFees
653232 144138 103.00 2015-04-02 2976 7299 8.00 8.00
653233 144114 104.00 2015-04-02 2976 7299 8.00 0.00
653233 144114 104.00 2015-04-02 2976 7299 8.00 0.00
653232 144138 103.00 2015-04-02 2976 7299 8.00 0.00
How do I properly join without causing duplication of records?
select t.TransactionId, t.PartyId, t.Amount, t.Date, t.PaymentCheckId
from [Transaction] t
where t.Date = '4/2/2015'
select pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount
from PaymentCheck pc
where pc.PaymentCheckId = 2976
select pcra.PaymentCheckId, pcra.CustomerAccountId, pcra.Amount
from PaymentCheckRelatedAccount pcra
where pcra.PaymentCheckId = 2976
The code above produces the following results:
TransactionId PartyId Amount Date PaymentCheckId
653232 144138 103.00 2015-04-02 2976
653233 144114 104.00 2015-04-02 2976
PaymentCheckId CheckNumber FeeAmount
2976 7299 8.00
PaymentCheckId CustomerAccountId Amount
2976 144138 103.00
2976 144114 104.00
Desired Results:
This is an example of bulk payment. Meaning a single PaymentCheck was used to make a payment for multiple CustomerAccountId's/PartyI
TransactionId PartyId Amount Date PaymentCheckId CheckNumber FeeAmount
653232 144138 103.00 2015-04-02 2976 7299 8.00
653233 144114 104.00 2015-04-02 2976 7299 0.00
My only problem is that when I join to the PaymentCheckRelatedAccount
The ONLY reason I need to even join to this table is because I found a solution to my above problem using ROW_NUMBER(), but now my records are duplicating. Instead of 2 rows, I get 4. I tried using SELECT DISTINCT, and that removes one of the rows, but still, there's an extra row.
Here's the query I'm trying to use:
select
t.TransactionId, t.PartyId, t.Amount, t.Date,
pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount
, ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0) as ServiceFees
from [Transaction] t
LEFT JOIN PaymentCheck pc ON pc.PaymentCheckId = t.PaymentCheckId
LEFT JOIN PaymentCheckRelatedAccount pcra ON PC.PaymentCheckId = pcra.PaymentCheckId
where t.Date = '4/2/2015'
Results:
TransactionId PartyId Amount Date PaymentCheckId CheckNumber FeeAmount ServiceFees
653232 144138 103.00 2015-04-02 2976 7299 8.00 8.00
653233 144114 104.00 2015-04-02 2976 7299 8.00 0.00
653233 144114 104.00 2015-04-02 2976 7299 8.00 0.00
653232 144138 103.00 2015-04-02 2976 7299 8.00 0.00
How do I properly join without causing duplication of records?
ASKER
I mentioned in my original post that I tried DISTINCT, but that only removed one row out of the four.
for records in table: PaymentCheckRelatedAccount
PaymentCheckId CustomerAccountId Amount PaymentCheckRelatedAccount Id
2976 144138 103.00 ??
2976 144114 104.00 ??
will them have same value of PaymentCheckRelatedAccount Id ?
PaymentCheckId CustomerAccountId Amount PaymentCheckRelatedAccount
2976 144138 103.00 ??
2976 144114 104.00 ??
will them have same value of PaymentCheckRelatedAccount
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, thanks.
select distinct t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAc
or
select t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAc
group by
t.TransactionId, t.PartyId, t.Amount, t.Date, pc.PaymentCheckId, pc.CheckNumber, pc.FeeAmount , ISNULL((CASE WHEN (ROW_NUMBER() OVER (PARTITION BY pc.CheckNumber, pc.PaymentSourceId ORDER BY pcra.PaymentCheckRelatedAc