Split Date into From and ToDate

  • Hi friends,

    I would like to split the date into from and to date range based up on Quantity change and also need to ignore the record if Date is missing in between them. Any suggestions would be really appreciated.


    DECLARE @T TABLE (RecordID int, dateID datetime2(0), Quantity int);

    INSERT INTO @T (RecordID, dateID, Quantity) VALUES
    (1, '2017-02-03' , 2),
    (1, '2017-02-04' , 2),
    (2, '2017-02-03' , 3),
    (2, '2017-02-05' , 4),
    (2, '2017-02-06' , 4),
    (3, '2017-02-05' , 4),
    (3, '2017-02-06' , 5),
    (4, '2017-02-03' , 7),
    (4, '2017-02-04' , 7),
    (4, '2017-02-06' , 7)

    SELECT * FROM @T

    /* Expected Output */
    SELECT 1 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-04 00:00:00' AS ToDate, 2 AS Quantity
    UNION
    SELECT 2 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-03 00:00:00' AS ToDate, 3 AS Quantity
    UNION
    SELECT 2 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-06 00:00:00' AS ToDate, 4 AS Quantity
    UNION
    SELECT 3 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-05 00:00:00' AS ToDate, 4 AS Quantity
    UNION
    SELECT 3 AS RecordID, '2017-02-06 00:00:00' AS FromDate, '2017-02-06 00:00:00' AS ToDate, 5 AS Quantity
    UNION
    SELECT 4 AS RecordID, '2017-02-03 00:00:00' AS FromDate, '2017-02-04 00:00:00' AS ToDate, 7 AS Quantity
    UNION
    SELECT 4 AS RecordID, '2017-02-05 00:00:00' AS FromDate, '2017-02-05 00:00:00' AS ToDate, 7 AS Quantity

    Thanks,
    Charmer

  • Use MIN and MAX dateID, grouped by RecordID and Quantity.  That'll work for your sample data, but it'll give strange results if you ever go back to the same Quantity for the same RecordID.

    John

  • Why do you have 2 results for record ID 4 the quantity didn't change?

  • ZZartin - Thursday, February 21, 2019 9:50 AM

    Why do you have 2 results for record ID 4 the quantity didn't change?

    It's the stock count. Client send us of what's the stock Count for a day. So its getting tracked everyday. Let's say that tracking what is the count of an item in a store on a daily basis.. I think I need to change the sample data. So that you will have a clear picture. My bad. Sorry.

    Thanks,
    Charmer

  • SELECT t.RecordId,
            t.dateId FromDate,
            x.dateID ToDate,
            t.Quantity
       FROM @T t
      CROSS APPLY(SELECT TOP(1) *
                    FROM @T x
                   WHERE x.RecordID = t.RecordID
                     AND x.dateID >= t.dateID
                     AND NOT EXISTS(SELECT *
                                      FROM @T z
                                     WHERE z.RecordID = x.RecordID
                                       AND z.dateID > t.dateID
                                       AND z.dateID < x.DateId
                                       AND z.Quantity <> t.Quantity)
                   ORDER BY x.dateID DESC) x
     WHERE NOT EXISTS(SELECT *
                        FROM @T z
                       WHERE z.RecordID = t.RecordID
                         AND z.dateID < t.dateID
                         AND z.Quantity = t.Quantity
                         AND NOT EXISTS(SELECT *
                                          FROM @T y
                                         WHERE y.RecordID = z.RecordID
                                           AND y.Quantity <> t.Quantity
                                           AND y.dateID > z.dateID
                                           AND y.dateId < t.dateID))

  • This is a fairly standard gaps and islands problem.

    WITH gaps_and_islands AS
    (
        SELECT *, DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY RecordID, Quantity ORDER BY dateID), dateID) AS grp
        FROM @T
    )
    SELECT RecordID, MIN(dateID) AS FromDate, MAX(dateID) AS ToDate, Quantity
    FROM gaps_and_islands
    GROUP BY RecordID, Quantity, grp
    ORDER BY RecordID, MIN(dateID), Quantity

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Thursday, February 21, 2019 12:15 PM

    This is a fairly standard gaps and islands problem.

    WITH gaps_and_islands AS
    (
        SELECT *, DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY RecordID, Quantity ORDER BY dateID), dateID) AS grp
        FROM @T
    )
    SELECT RecordID, MIN(dateID) AS FromDate, MAX(dateID) AS ToDate, Quantity
    FROM gaps_and_islands
    GROUP BY RecordID, Quantity, grp
    ORDER BY RecordID, MIN(dateID), Quantity

    Drew

    Wow, this is working. Great. Thank you , Drew.

    Thanks,
    Charmer

Viewing 7 posts - 1 through 6 (of 6 total)

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