Between dates SQL help needed

  • 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 1 month, 1 week ago by  mageshl79.
  • 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.

  • 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;

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply