Find first payment

[jbiggscc]

I have a payment journal table that is similar to the following:

ClientID Date Amount Category
123 1/2/01 -100 PMT
123 1/5/01 100 NSF
123 1/20/01 -35 PMT


I need to be able to find the first payment from each clientid that does NOT have a corresponding NSF (which will always be Amount *-1). I've tried to do this with a JOIN but I'm not having any luck.

[mwasif]

Post the query you have made so far. What is the data type of Date column?

[jbiggscc]

Post the query you have made so far. What is the data type of Date column?

The Date is a date type field. The query I have so far looks like:

SELECT a.clientid a.effdt, a.amount, a.category
FROM journal a, journal b
WHERE a.amount <> (b.amount)*-1
SORT BY effdt

[jbiggscc]

Still looking for an answer to this....anyone?

[amitpatel66]

Still looking for an answer to this....anyone?

Try below query:

SELECT j.* from jnl j where j.caty = 'PMT' AND abs(j.amt) NOT IN (SELECT amt from jnl where caty = 'NSF' and cid = j.cid)
 

[jbiggscc]

This is close to what I need. Here is the issue, if there is one NSF for $160 and then 3 payments for $160, the one NSF will cancel out all three payments.

Is there any easy way to compare them ledger style (compare 1st payment to 1st NSF, if they are the same ignore) and loop until it finds the 1st time when they do not match up.

Is there an easier way to do this? Should I be using some PHP with a more basic query?

[amitpatel66]

This is close to what I need. Here is the issue, if there is one NSF for $160 and then 3 payments for $160, the one NSF will cancel out all three payments.

Is there any easy way to compare them ledger style (compare 1st payment to 1st NSF, if they are the same ignore) and loop until it finds the 1st time when they do not match up.

Is there an easier way to do this? Should I be using some PHP with a more basic query?

Hi,

Does the date for NSF an PMT will be same?

[amitpatel66]

Hi,

Does the date for NSF an PMT will be same?

Try Below Query:

SELECT E.cid,E.ddate,E.amt,E.caty FROM 
(SELECT j.*,ROW_NUMBER() OVER(PARTITION BY j.amt,j.caty ORDER BY j.CID) rn FROM jnl j) E
WHERE E.rn > 1
 

[jbiggscc]

Hi,

Does the date for NSF an PMT will be same?

No, they would not be the same. An NSF might be within 7-10 days of a PMT.

The problem is, if they pay weekly, there could also be another PMT within that timeframe.

[jbiggscc]

Try Below Query:

SELECT E.cid,E.ddate,E.amt,E.caty FROM 
(SELECT j.*,ROW_NUMBER() OVER(PARTITION BY j.amt,j.caty ORDER BY j.CID) rn FROM jnl j) E
WHERE E.rn > 1
 

I think I am messing up the syntax somewhere. I'm not familiar with some of the functions you are using, here is what I tried to run:

SELECT e.clientid, e.effdt, e.amount, e.apply_to_category FROM (SELECT j.*, ROW_NUMBER() OVER(PARTITION BY j.amount,j.apply_to_category ORDER BY j.clientid) rn FROM client_journal j) client_journal e WHERE e.rn > 1;

[jbiggscc]

amitpatel - can you give me any help with the syntax? Thanks!