Fetch latest start date, if there are two minimum start dates in a table-CodePudding

I'm trying to build a query for the following scenario,

Group records by license ID and get min and max dates
For a given license ID, if there are two earliest start dates, then start date of the particular ID has to be updated as latest start date in that grouping.

Since I'm new to sql, I need help to satisfy condition 2. Any help is greatly appreciated. Thanks

Actual data

LicenseID	StartDate	EndDate
100	4/3/2000	3/1/2013
100	4/3/2000	2/2/2017
100	3/1/2013	1/23/2015
100	1/23/2015	2/2/2017
100	2/2/2017	2/9/2018
100	2/2/2017	12/18/2018
100	12/18/2018	2/16/2021

Expected output

LicenseID	StartDate	EndDate
100	12/18/2018	2/16/2021

CodePudding user response：

Here's one option; read comments within code.

Sample data:

SQL> with test (id, start_date, end_date) as
  2    (select 100, date '2000-04-03', date '2013-03-01' from dual union all
  3     select 100, date '2000-04-03', date '2017-02-02' from dual union all
  4     select 100, date '2018-12-18', date '2021-02-16' from dual
  5    ),

Query begins here:

  6  -- rank start dates per each ID
  7  temp as
  8    (select id,
  9       min(start_date) over (partition by id) min_sd,
 10       max(start_date) over (partition by id) max_sd,
 11       rank() over (partition by id order by start_date) rnk_sd,
 12       --
 13       max(end_date) over (partition by id) max_ed
 14     from test
 15    ),
 16  -- count number of the 1st start dates
 17  temp2 as
 18    (select id,
 19       sum(case when rnk_sd = 1 then 1 else 0 end) cnt_sd
 20     from temp
 21     group by id
 22    )
 23  -- if number of the 1st start dates is 1, take MIN_SD. Otherwise, take MAX_SD
 24  select distinct
 25    b.id,
 26    case when b.cnt_sd = 1 then a.min_sd else a.max_sd end start_date,
 27    a.max_ed end_date
 28  from temp2 b join temp a on a.id = b.id;

Result:

        ID START_DATE END_DATE
---------- ---------- ----------
       100 12/18/2018 02/16/2021

SQL>

CodePudding user response：

This can filter them:

WITH sample_data AS
(
  SELECT 100 AS LicenseID, TO_DATE('04/03/2000','MM/DD/YYYY') AS StartDate, TO_DATE('03/01/2013','MM/DD/YYYY') AS EndDate FROM DUAL UNION ALL
  SELECT 100, TO_DATE('04/03/2000','MM/DD/YYYY'), TO_DATE('02/02/2017','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 100, TO_DATE('03/01/2013','MM/DD/YYYY'), TO_DATE('01/23/2015','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 100, TO_DATE('01/23/2015','MM/DD/YYYY'), TO_DATE('02/02/2017','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 100, TO_DATE('02/02/2017','MM/DD/YYYY'), TO_DATE('02/09/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 100, TO_DATE('02/02/2017','MM/DD/YYYY'), TO_DATE('12/18/2018','MM/DD/YYYY') FROM DUAL UNION ALL
  SELECT 100, TO_DATE('12/18/2018','MM/DD/YYYY'), TO_DATE('02/16/2021','MM/DD/YYYY') FROM DUAL
)
SELECT dat.licenseID, CASE WHEN dups.licenseID IS NOT NULL THEN MAX(StartDate)
                          ELSE MIN(StartDate)
                     END,
                     CASE WHEN dups.licenseID IS NOT NULL THEN MAX(EndDate)
                          ELSE MIN(EndDate)
                     END
  FROM sample_data dat
       LEFT OUTER JOIN (SELECT COUNT(1), sd.LicenseID
                          FROM sample_data sd
                               INNER JOIN (SELECT MIN(StartDate) AS StartDate, LicenseID
                                             FROM sample_data
                                            GROUP BY LicenseID) mins
                                  ON sd.LicenseID = mins.LicenseID AND sd.startDate = mins.StartDate
                         GROUP BY sd.LicenseID
                         HAVING COUNT(1) > 1) dups
                    ON dups.LicenseID = dat.licenseID
 GROUP BY dat.licenseID, dups.licenseID;

CodePudding user response：

You can use:

SELECT licenseid,
       MAX(startdate) AS startdate,
       MAX(enddate) KEEP (DENSE_RANK LAST ORDER BY startdate) AS enddate
FROM   table_name
GROUP BY licenseid
HAVING COUNT(*) KEEP (DENSE_RANK FIRST ORDER BY startdate) > 1;

or:

SELECT licenseid,
       max_startdate AS startdate,
       max_enddate As enddate
FROM   (
  SELECT licenseid,
         RANK()
           OVER (PARTITION BY licenseid ORDER BY startdate) AS rnk,
         ROW_NUMBER()
           OVER (PARTITION BY licenseid, startdate ORDER BY enddate) AS rn,
         MAX(startdate)
           OVER (PARTITION BY licenseid) AS max_startdate,
         MAX(enddate)
           KEEP (DENSE_RANK LAST ORDER BY startdate)
           OVER (PARTITION BY licenseid) AS max_enddate
  FROM   table_name t
)
WHERE  rnk = 1
AND    rn  = 2;

Which, for the sample data:

CREATE TABLE table_name (licenseid, startdate, enddate) AS
  SELECT 100, DATE'2000-04-03', DATE'2013-03-01' FROM DUAL UNION ALL
  SELECT 100, DATE'2000-04-03', DATE'2017-02-02' FROM DUAL UNION ALL
  SELECT 100, DATE'2013-03-01', DATE'2015-01-23' FROM DUAL UNION ALL
  SELECT 100, DATE'2015-01-23', DATE'2017-02-02' FROM DUAL UNION ALL
  SELECT 100, DATE'2017-02-02', DATE'2018-02-09' FROM DUAL UNION ALL
  SELECT 100, DATE'2018-02-02', DATE'2018-12-18' FROM DUAL UNION ALL
  SELECT 100, DATE'2018-12-18', DATE'2021-02-16' FROM DUAL;

Both output:

LICENSEID STARTDATE ENDDATE

100 2018-12-18 00:00:00 2021-02-16 00:00:00

LICENSEID	STARTDATE	ENDDATE
100	2018-12-18 00:00:00	2021-02-16 00:00:00

If you do want to perform an UPDATE of that second row then:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         max_startdate,
         max_enddate
  FROM   (
    SELECT RANK()
             OVER (PARTITION BY licenseid ORDER BY startdate) AS rnk,
           ROW_NUMBER()
             OVER (PARTITION BY licenseid, startdate ORDER BY enddate) AS rn,
           MAX(startdate)
             OVER (PARTITION BY licenseid) AS max_startdate,
           MAX(enddate)
             KEEP (DENSE_RANK LAST ORDER BY startdate)
             OVER (PARTITION BY licenseid) AS max_enddate
    FROM   table_name t
  )
  WHERE  rnk = 1
  AND    rn  = 2
)src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET startdate = src.max_startdate,
      enddate   = src.max_enddate;

db<>fiddle here