Forum Discussion
Sql question
Hi Everyone, I have one sql problem to be solved 1. Image is the data 2.Image is the output that I should get. You need to output the dates, when Couriers changed their phone numbers. You shoul...
No need for joins. What you want to find is the first date per courier/phonenumber, and then exclude the first phone number for every courier. This can be achieved with two differently partitioned ROW_NUMBER() expressions in a CTE and an outer query to filter for exactly this.
WITH CTE AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY courier_id,phone ORDER BY created_dt) as rn_courier_number,
ROW_NUMBER() OVER(PARTITION BY courier_id ORDER BY created_dt) as rn_courier
FROM courier_phone
)
SELECT
courier_id,
phone as new_phone,
created_dt as changed_dt
FROM
CTE
WHERE
rn_courier_number=1 AND rn_courier>1;