1

I have data as shown below.

SUBSCRIBER_NO CUSTOMER_ID SYS_CREATION_DATE SYS_UPDATE_DATE OPERATOR_ID
61686209 199041640 19-JUN-22 19-JUN-22 611316378
61686209 145781645 24-AUG-20 19-JUN-22 611316378
61686210 199044567 19-JUN-22 19-JUN-22 611316379
61686210 145781234 24-AUG-20 17-JUN-22 611316379

from the data above when sys_update_date is same for a subscriber_no column then query should fetch the latest record based on the column sys_creation_date. similarly, when a subscriber_no has two rows have different date in sys_update_date itslef, then it should fetch the row with latest sys_update_date . please help me achieve this in a sql query .

expected output as below.

SUBSCRIBER_NO CUSTOMER_ID SYS_CREATION_DATE SYS_UPDATE_DATE OPERATOR_ID
61686209 199041640 19-JUN-22 19-JUN-22 611316378
61686210 199044567 19-JUN-22 19-JUN-22 611316379
MT0
  • 143,790
  • 11
  • 59
  • 117
jerry
  • 15
  • 3

2 Answers2

0

Here is a solution for your expected output:

I know this can be optimized further, but for initial draft of your requirements, please review. I hope this helps

Create table #Table1
(
    SUBSCRIBER_NO int, 
    CUSTOMER_ID int, 
    SYS_CREATION_DATE DateTime, 
    SYS_UPDATE_DATE DatetIme, 
    OPERATOR_ID int
)

INSERT INTO #Table1
    VALUES (61686209, 199041640, '19-JUN-22', '19-JUN-22', 611316378)
INSERT INTO #Table1
    VALUES (61686209, 145781645, '24-AUG-20', '19-JUN-22', 611316378)
INSERT INTO #Table1
    VALUES (61686210, 199044567, '19-JUN-22', '19-JUN-22', 611316379)
INSERT INTO #Table1
    VALUES (61686210, 145781234, '24-AUG-20', '17-JUN-22', 611316379)

SELECT
    p1.*
FROM #Table1 p1
WHERE p1.SYS_CREATION_DATE =
CASE
    WHEN (SELECT
                COUNT(p2.SYS_UPDATE_DATE)
            FROM #Table1 p2
            WHERE p2.SYS_UPDATE_DATE = p1.SYS_UPDATE_DATE)
        > 1 THEN (SELECT
                MAX(p2.SYS_CREATION_DATE)
            FROM #Table1 p2
            WHERE p2.SYS_UPDATE_DATE = p1.SYS_UPDATE_DATE)
    ELSE p1.SYS_CREATION_DATE
END
AND p1.SYS_UPDATE_DATE =
CASE
    WHEN (SELECT
                COUNT(p2.SUBSCRIBER_NO)
            FROM #Table1 p2
            WHERE p2.SUBSCRIBER_NO = p1.SUBSCRIBER_NO)
        > 1 THEN (SELECT
                MAX(p2.SYS_UPDATE_DATE)
            FROM #Table1 p2
            WHERE p2.SUBSCRIBER_NO = p1.SUBSCRIBER_NO)
    ELSE p1.SYS_UPDATE_DATE
END
    
If(OBJECT_ID('tempdb..#Table1') Is Not Null)
Begin
DROP TABLE #Table1
END
hassaan mustafa
  • 60
  • 1
  • 6
0
SELECT SUBSCRIBER_NO, CUSTOMER_ID, SYS_CREATION_DATE, SYS_UPDATE_DATE, OPERATOR_ID
FROM
(
 SELECT 
    SUBSCRIBER_NO, 
    CUSTOMER_ID, 
    SYS_CREATION_DATE, 
    SYS_UPDATE_DATE, 
    OPERATOR_ID, 
    row_number() OVER (PARTITION BY SUBSCRIBER_NO ORDER BY SYS_UPDATE_DATE DESC, SYS_CREATION_DATE DESC) RN
    FROM <table>
) subselect
where subselect.RN=1;
Ben Du
  • 64
  • 5