CODEWITHSUNDEEP
sqloracle-database
Osama Al-Banna
Osama Al-Banna

Reputation: 1515

How show the last status of a mobile number and old data in the same row ? using SQL

I'm working in a telecom and part of work is to check the last status for a specific mobile number along with that last de-active status,it's easy to get the active number by using the condition ACTIVE int the statement ,but it's not easy to pick the last de-active status because each number might have more than one de-active status or only one status ACTIVE, I use the EXP_DATE as an indicator for the last de-active status,I want to show both new data and old data in one row,but I'm struggling with that ,below my table and my expected result :-

My current table

my expected result

enter image description here

query that I use on daily basis

select * from test where exp_date>sysdate; to get the active numbers , to get the de-active number select * from test where exp_date<sysdate;

Upvotes: 1

Views: 239

Answers (3)

Popeye
Popeye

Reputation: 35920

You just need to do outer join with one subquery containing ACTIVE records and one with latest DE-ACTIVE record as following:

SELECT A.MSISDN, 
A.NAME, 
A.SUB_STATUS,
A.CREATED_DATE, 
A.EXP_DATE,
D.MSISDN AS MSISDN_, 
D.NAME AS OLD_NAME, 
D.SUB_STATUS OLD_STATUS, 
D.CREATED_DATE AS OLD_CREATED_DATE, 
D.EXP_DATE AS OLD_EXP_DATE
FROM
(SELECT * FROM TEST 
    WHERE EXP_DATE > SYSDATE
     AND SUB_STATUS = 'ACTIVE') A -- ACTIVE RECORD 
     -- USE CONDITION TO FETCH ACTIVE RECORD AS PER YOUR REQUIREMENT
FULL OUTER JOIN 
(SELECT * FROM
    (SELECT T.*, 
    ROW_NUMBER() OVER (PARTITION BY T.MSISDN ORDER BY EXP_DATE DESC NULLS LAST) AS RN
    FROM TEST T 
   WHERE T.EXP_DATE < SYSDATE
     AND T.SUB_STATUS='DE-ACTIVE') 
     -- USE CONDITION TO FETCH DEACTIVE RECORD AS PER YOUR REQUIREMENT
 WHERE RN = 1
) D
ON (A.MSISDN = D.MSISDN)

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

How about conditional aggregation?

select msidn,
       max(case when status = 'DE-ACTIVE' then create_date end) as deactive_date,
       max(case when status = 'ACTIVE' then exp_date end) as active_date
from test 
group by msisdn

Upvotes: 1

Hogan
Hogan

Reputation: 70538

Here is an overview of how to do this -- one query to get a distinct list of all the phone numbers, left join to a list of the most recent active on that phone number,left join to a list of the most recent de-active on the phone number

Upvotes: 1

Related Questions