I am trying to pull only the most recent record for a customer, but I also want to include the discount and date of the last rate change. Is it possible to get lag data and only the single row of data? I am joining this to other data and cannot do so with numerous entries. I have tried Max and Last on the variables but received errors each time.
SQL code:
select
customer_key,
start_time_key,
end_time_key,
retail_rate_total,
discount_amount_total,
lag(discount_amount_total) over (order by start_time_key asc) as lag1_disc_amt_by_start,
lag(start_time_key) over (order by start_time_key asc) as lag1_start
from cdm.F_CUST_RECUR_AMOUNT_CHARGED
where customer_key=xxxxxxxxx
