Forum Discussion

Neha11's avatar
Neha11
Copper Contributor
Dec 25, 2022

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 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

Analysis Services
Analytics
Big Data
Business Intelligence
Data Warehouse
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Dec 27, 2022
    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
  • MagnusAhlkvist's avatar
    MagnusAhlkvist
    Copper Contributor
    Dec 30, 2022

    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;

     

Resources