0

I have 3 columns(CustomerId, Amount, ProcessDate) in a table (Customer).

Values are inserted daily in this table.

I want to get all the rows whose current day Amount is greater than previous day Amount.

CustomerId Amount Process_date

1 20 12/05/2021

2 30 12/05/2021

1 40 13/05/2021

2 25 13/05/2022

We have to print (1 40 13/05/2021) as 20 (previous day amount) is smaller than 40 (next day amount).

Query which I tried :-

select b.customerId, b.amount, b.process_date from customer a
 join (select customerId, amount, process_date from customer where process_date = current_date ) as b
on
a.customerId = b.customerId and 
a.process_date = current_date - 1 and a.amount < b.amount
Kota Mori
  • 6,510
  • 1
  • 21
  • 25
Saurabh Prakash
  • 2,715
  • 1
  • 11
  • 17
  • Which part are you struggling with, and what have you tried? – Andrew Jun 11 '21 at 14:34
  • 1
    Seems like you may want LAG function and QUALIFY clause. Try to give a simple example with a few rows of data, what you have tried (and any error messages or results that don't meet the requirement), and what was desired as output. – Fred Jun 11 '21 at 17:47
  • Append `qualify Amount > lag(Amount) over (partition by CustomerId order by ProcessDate)` to your current select. – dnoeth Jun 11 '21 at 19:06

0 Answers0