ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Help - Select just the latest payment for each customer

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Help - Select just the latest payment for each customer

    I have a file with customer account transactions. Each customer account may have none, one, or more payment transactions, as well as other transactions, in the file. I'm interested in retrieving just the latest, i.e. most recent, payment transaction for each customer account, and I'll be using this data in a CTE as part of a more complex SQL statement.

    Here's a simple SQL to retrieve the account and payment date and amount for the payment transactions:

    Code:
    [FONT=courier new]select Acct, (TranYY * 10000 + TranMM * 100 + TranDD) as PmtDate, TranAmt as PmtAmt from MyFile
    where TranTyp = 'P'[/FONT]
    Since each account can have multiple payment transactions but I only want the most recent one, I think maybe I need to group these by account and transaction type and then limit it to somehow only select the most recent date... maybe sort by date in descending order and then select just one for each customer?

    What is the best way to accomplish this? Thanks.

  • #2
    Viking,
    There are two ways that I can think of to accomplish what you are desiring.

    1.) Row_Number OLAP specification. (Primarily used if the payment information is the basis of the query.)
    Code:
    [FONT=courier new]Select * From
    (Select Acct,TranYY*10000+TranMM*100+TranDD as PmtDate,TranAmt as PmtAmt,
    Row_Number()
    Over(Partition By Acct
         Order By Acct,TranYY Desc,TranMM Desc,TranDD Desc) As PMTORD
    From MyFile
    Where TranTyp = 'P') As A
    Where PMTORD = 1[/FONT]
    [FONT=lucida sans unicode][/FONT]

    2.) Lateral Sub-Select using Fetch First 1 Row Only (Used if trying to find last payment of known customer.
    Code:
    [FONT=courier new]Select * From OurCust As A
    Left Join Lateral
    (Select B.Acct,B.TranYY*10000+B.TranMM*100+B.TranDD as PmtDate,B.TranAmt as PmtAmt
    From
    MyFile As B
    Where B.TranTyp = 'P' And A.Acct = B.Acct
    Order By B.Acct,B.TranYY Desc,B.TranMM Desc,B.TranDD Desc
    Fetch First 1 Row Only) As B
    On A.Acct = B.Acct[/FONT]
    [FONT=lucida sans unicode][/FONT]

    Jim

    Comment


    • #3
      Is there ever more than one payment on a given date? I'm curious why the separate fields for month, day, year rather than using a timestamp or date field, but be that as it may I'd create a view that put those fields into the date format (whether it be numeric or date field isn't important). I would then do all of my queries against that view. This keeps your queries simpler and straightforward.


      Code:
      create view myview as (
      select a.*,tranyy*10000 + tranmm * 100 + trandd as trandte)
      Code:
      create view custlastorder as 
      with f1 as (
      select acct,max(trandte) trandte from myview group by cct
      )
      select a.* from myview a join f1 on (a.acct=f1acct and a.trandte=f1.trandte)
      Now you select against custlastorder it will give you the last order for each customer.

      Comment


      • #4
        Ok, got it working with a variant of Jim's 2nd suggestion above. Thanks!

        I'm also liking Rocky's idea and will play with that a bit. Rocky, yes there could be two payments on the same date, but for the purposes of my current requirement that wouldn't matter. The reason the date fields are split up like that in the file is because it's an old file and that's how it was created. Anyway, I need to leverage views in order to simplify things as you suggest. Thanks!

        Comment

        Working...
        X