Link to home
Start Free TrialLog in
Avatar of pzozulka
pzozulka

asked on

SQL Row_Number() help

When joining the below tables, I am getting additional (unwanted/duplicate) rows.
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

Open in new window


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/PartyId'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:
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'

Open in new window


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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

quick try, use distinct or group by clause?

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.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'

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.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'
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.PaymentCheckRelatedAccountId) > 1) AND (pc.PaymentSourceId IN (2,3,4)) THEN 0 ELSE PC.FeeAmount END),0)
Avatar of pzozulka
pzozulka

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      PaymentCheckRelatedAccountId
2976                        144138                          103.00                      ??
2976                        144114                          104.00                      ??

will them have same value of PaymentCheckRelatedAccountId ?
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked, thanks.