Find first payment

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jbiggscc
    New Member
    • Sep 2007
    • 7

    #1

    Find first payment

    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
    Recognized Expert Contributor
    • Jul 2006
    • 802

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

    Comment

    • jbiggscc
      New Member
      • Sep 2007
      • 7

      #3
      Originally posted by mwasif
      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:

      [CODE=mysql]SELECT a.clientid a.effdt, a.amount, a.category
      FROM journal a, journal b
      WHERE a.amount <> (b.amount)*-1
      SORT BY effdt[/CODE]
      Last edited by mwasif; Sep 13 '07, 03:03 PM. Reason: Added code tags.

      Comment

      • jbiggscc
        New Member
        • Sep 2007
        • 7

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

        Comment

        • amitpatel66
          Recognized Expert Top Contributor
          • Mar 2007
          • 2358

          #5
          Originally posted by jbiggscc
          Still looking for an answer to this....anyone?
          Try below query:

          [code=mysql]
          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)
          [/code]

          Comment

          • jbiggscc
            New Member
            • Sep 2007
            • 7

            #6
            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?

            Comment

            • amitpatel66
              Recognized Expert Top Contributor
              • Mar 2007
              • 2358

              #7
              Originally posted by 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?
              Hi,

              Does the date for NSF an PMT will be same?

              Comment

              • amitpatel66
                Recognized Expert Top Contributor
                • Mar 2007
                • 2358

                #8
                Originally posted by amitpatel66
                Hi,

                Does the date for NSF an PMT will be same?
                Try Below Query:

                [code=sql]
                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
                [/code]

                Comment

                • jbiggscc
                  New Member
                  • Sep 2007
                  • 7

                  #9
                  Originally posted by amitpatel66
                  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.

                  Comment

                  • jbiggscc
                    New Member
                    • Sep 2007
                    • 7

                    #10
                    Originally posted by amitpatel66
                    Try Below Query:

                    [code=sql]
                    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
                    [/code]
                    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_cate gory FROM (SELECT j.*, ROW_NUMBER() OVER(PARTITION BY j.amount,j.appl y_to_category ORDER BY j.clientid) rn FROM client_journal j) client_journal e WHERE e.rn > 1;

                    Comment

                    • jbiggscc
                      New Member
                      • Sep 2007
                      • 7

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

                      Comment

                      Working...