Merge spans with Dates Logic

• dhanekulakalyan

  Default port

  Points: 1468

  More actions

  May 13, 2025 at 9:44 pm

  #4590822

  --for a given member if the startdate and endate is continous we need to keep in single record and if start date and end date is not continous i need to keep in separate record for a given member.

   

  drop table #test

  create table #test

  (ID int,

  startdate datetime,

  enddate datetime

  )

  insert into #test

  values (1,'01/01/2024','01/31/2024'),

  (1,'02/01/2024','04/30/2024'),

  (2,'01/01/2024','01/31/2024'),

  (2,'02/01/2024','02/29/2024'),

  (2,'04/01/2024','04/30/2024'),

  (2,'06/01/2024','06/30/2024'),

  (3,'07/01/2024','12/31/2024')

  Select * from #test

   

  --expected output

  (1,'01/01/2024','04/30/2024'),

  (2,'01/01/2024','02/29/2024'),

  (2,'04/01/2024','04/30/2024'),

  (2,'06/01/2024','06/30/2024'),

  (3,'07/01/2024','12/31/2024')

• Johan Bijnens

  SSC Guru

  Points: 135503

  More actions

  May 14, 2025 at 1:58 pm

  #4591163

  What have you tested ?

  Here is an example ( but you'd still have to validate performancewise in your environment )

  drop table #test
  
  CREATE TABLE #test
               (MemberID        INT
              , startdate DATETIME
              , enddate   DATETIME
               );
  
  insert into #test
  values (1,'01/01/2024','01/31/2024'),
  (1,'02/01/2024','04/30/2024'),
  (2,'01/01/2024','01/31/2024'),
  (2,'02/01/2024','02/29/2024'),
  (2,'03/01/2024','03/31/2024'),
  (2,'04/01/2024','04/30/2024'),
  (2,'05/01/2024','05/31/2024'),
  
  (2,'07/01/2024','07/31/2024'),
  
  (3,'07/01/2024','08/31/2024'),
  (3,'09/01/2024','12/31/2024')
  ;
  
  ;WITH ctePrevEnddate AS (
      SELECT 
          MemberID,
          startdate,
          enddate,
          LAG(enddate) OVER (PARTITION BY MemberID ORDER BY startdate) AS prev_enddate
      FROM #test
  ),
  cteCheckContinous AS (
      SELECT
          MemberID,
          startdate,
          enddate,
          CASE 
              WHEN prev_enddate IS NULL THEN 1  -- First record for member
              WHEN DATEDIFF(DAY, prev_enddate, startdate) = 1 THEN 0  -- Continuous
              ELSE 1  -- Not Continuous
          END AS IsNonContinuous
      FROM ctePrevEnddate
  ),
  cteAssemblies AS (
      SELECT
          MemberID,
          startdate,
          enddate,
          SUM(IsNonContinuous) OVER (PARTITION BY MemberID ORDER BY startdate) AS Ranges
      FROM cteCheckContinous
  )
  -- Select * from cteAssemblies order by MemberID, startdate, Ranges
  SELECT
      MemberID,
      MIN(startdate) AS startdate,
      MAX(enddate) AS enddate
  FROM cteAssemblies
  GROUP BY MemberID, Ranges
  ORDER BY MemberID, startdate;

  Keep in min I've added rows to your test data ! ( and altered ID to MemberID )

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me