Between dates SQL help needed

• mageshl79

  SSC Veteran

  Points: 286

  More actions

  May 12, 2021 at 1:57 pm

  #3882347

  Hi,

  I required duplicate values in the below table

  For e.g  staff date 10192 first record To date is between the second record's From date and To Date

  

  • This topic was modified 3 years, 6 months ago by  mageshl79.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736172

  More actions

  May 12, 2021 at 3:05 pm

  #3882380

  First, there is no first and second records. Records in a database have no ordering. You'll have to better explain what you are looking for here, or what results are you trying to achieve. What do you want to return?

  It's helpful to show this in text, and use the "Insert/edit code sample" button above to insert results as plain text and help us understand what you are trying to query.

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  May 12, 2021 at 3:08 pm

  #3882381

  In future please post consumable test data with dates in ISO format:

  CREATE TABLE #t
  (
  Staff_Number int NOT NULL
  ,FromDate date NOT NULL
  ,ToDate date NOT NULL
  );
  INSERT INTO  #t
  VALUES (10192, '20200706', '20220429')
  ,(10192, '20210301', '20220601')
  ,(10192, '20220501', '20220507')
  ,(1112, '20210112', '20210118')
  ,(10192, '20100706', '20120429');

  Try:

  WITH SeqBreaks
  AS
  (
  SELECT Staff_Number, FromDate, ToDate
  ,CASE
  WHEN FromDate >
  COALESCE
  (
  MAX(ToDate) OVER
  (
  PARTITION BY Staff_Number
  ORDER BY FromDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  )
  ,'19000101'
  )
  THEN 1
  ELSE 0
  END AS SeqBreak
  FROM #t
  )
  ,Grps
  AS
  (
  SELECT Staff_Number, FromDate, ToDate
  ,SUM(SeqBreak) OVER (PARTITION BY Staff_Number ORDER BY FromDate) AS Grp
  FROM SeqBreaks
  )
  SELECT Staff_Number
  ,MIN(FromDate) AS FromDate
  ,MAX(ToDate) AS ToDate
  FROM Grps
  GROUP BY Staff_Number, Grp;