CODEWITHSUNDEEP
sqllag
OFLim
OFLim

Reputation: 11

SQL. Find previous value from previous row(s). lag( )?

What I want to achieve to find the previous payment date for an account based on contact date and payment date.

select account, PaymentDate, contactdate, trans_amt
  ,lag(PaymentDate,1,'10010101') 
  over(PARTITION BY PaymentDate, account ORDER BY PaymentDate ) 
    as Prev_PaymentDate
from Table1

Result of query:

enter image description here

Row account paymentDate contactdate trans_amt   Prev_PaymentDate
1   123 20210104    20210102    29.7    10010101
2   123 20210104    20210104    29.7    20210104
3   123 20210111    20210102    6.3 10010101
4   123 20210111    20210104    6.3 20210111
5   123 20210205    20210102    33  10010101
6   123 20210205    20210104    33  20210205
7   123 20210205    20210128    33  20210205

In the sample, customer made 3 payments. Jan 4, Jan 11 and Feb 5.

What I want to achieve is for Prev_PaymentDate to be: Green column for previous payment

Prev_PaymentDate
10010101
10010101
20210104
20210104
20210111
20210111
20210111

The second part is to write a query to only show a table where the result will be 3 rows. The payment made on Jan 11th is removed because contact was made prior and paid on the 4th. The payment on Feb 5th is attributed to the contact from Jan 28th.

Thank you for any help extended.

Upvotes: 1

Views: 188

Answers (3)

OFLim
OFLim

Reputation: 11

Thank you Mal and Chris for taking the time to reply. Great suggestion from Chris on tackling the data first. With my limited SQL knowledge, I will have to figure this out on how to identify first time vs subsequent.

Upvotes: 0

Mal
Mal

Reputation: 41

I believe partition_by (and it's requirement, order_by) is what you're looking for, looking at the SQL documentation

You'd either use the payment_date or trans_amt, or ideally some sort of payment_id value, which you'd tell your lag function to "group by".

A redshift example I recently used: lead(action) over (partition by product_id, material_id order by id, product_id, material_id) as future_action.

Upvotes: 0

Chris Maurer
Chris Maurer

Reputation: 2580

I would back up a little bit and ask how your contacts got so twisted up in relation to your payments. It looks like someone already queried two tables and presented you with a partial Cartesian product of contacts and payments.

If I look at your desired result, you want the minimum contact date for the first record and the maximum contact date for the second and third record. I think if you could straighten out the relationship rules between contacts and payments this whole task will get a whole lot easier.

Upvotes: 0

Related Questions