Sql question

[Neha11]

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 shouldn't output the first phone number of a courier

Through lead function I'm able to acheive,  but I want to achieve it using joins

3. Image is the query that I used

4. Image is the output that I get

I'm not sure how to get the output, using the query that I used, I have stucked in between 

Please help

Thanks,

Neha

[#2]

I don't have your database and I can't query "images" to test it.
Please post table design as DDL, some sample data as DML statement and the expected result.

Olaf

[#3]

@Neha11 

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;