Trying to get the two most recent payments for each sale number from a table of transactions (ARTRANS). Seems like a lateral join would work or maybe a window function.

Structure is fairly simple with ARTRANSID (the PK), SALENO, PAIDDATE, DUEDATE, ACCTNO (more, really, but what does that matter). Of course, with the lateral join, I'd really be doing a self join. Any suggestions on this code?

An aside: It would be cool if there were something like a GROUPLIMIT keyword, so you could limit the returned records to a certain number within the grouping. That would make this easy as pie.

  • 1
    What have you tried? Also, post create table statements and insert statements for sample data together with expected result – Lennart Jul 12 '18 at 15:55

A nice windowing function will do the job.

with phillovesCTEs as (
         ROW_NUMBER() over (
                            group by SALENO 
                            order by PAIDDATE desc
                           ) as RN
  from ARTRANS 
select *
from phillovesCTEs
where RN <=2; 
  • I did upvote this, but I guess I don't hang out here enough for it to count. Thanks for your answer. – Russell Jul 12 '18 at 19:02

I did work out something similar and have some additional code to ensure I only get data for sales with at least two payments:

with q1 as

  artransid, transclass, transtype, saleno, duedate, paiddate, paymentperiod, totalamt
    ROW_NUMBER() OVER (PARTITION BY t.saleno ORDER BY t.paiddate desc) AS r,
    t.artransid, t.transclass, t.transtype, t.saleno, t.duedate, t.paiddate, t.totalamt, s.paymentperiod
    artrans t join sales s
    on t.saleno = s.saleno
    where transtype = 'PAYMENT'
      and transclass <> 'PAYO'
    and s.status = 'SOLD') x
  x.r <= 2),

q2 as

(select saleno, count(saleno) as salenocnt
 from q1
 group by saleno
 order by saleno)

 select q1.*, q1.paiddate::date - q1.duedate::date as dayslate,
   case when paymentperiod = 'MONTHLY' then 30 when paymentperiod = 'WEEKLY' then 7 when paymentperiod = 'BI-WEEKLY' then 14 when paymentperiod = 'SEMI-MONTHLY' then 15 end as daysbetween
    from q1 join q2
    on q1.saleno = q2.saleno
    where q2.salenocnt = 2
    order by saleno

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.