Get some row depending of Date of nexts rows

  • jolann69

    Old Hand

    Points: 334

    Hi,

    Sorry for my bad english. I need to select some rows of a table like that :

    Id Object   |              Start            |             End

    A           | 06/11/2019 09:00 | 06/11/2019 09:15

    A           |06/11/2019 09:05  | 06/11/2019 09:20

    A           |06/11/2019 09:10  | 06/11/2019 09:25

    A            |06/11/2019 09:15  | 06/11/2019 09:30

    B          | 06/11/2019 09:05 | 06/11/2019 09:20

    B           |06/11/2019 09:10  | 06/11/2019 09:25

    B           |06/11/2019 09:15  | 06/11/2019 09:30

    B            |06/11/2019 09:35  | 06/11/2019 09:50

     

    End As result of my select i would like :

    A           | 06/11/2019 09:00 | 06/11/2019 09:15

    A            |06/11/2019 09:15  | 06/11/2019 09:30

    B          | 06/11/2019 09:05 | 06/11/2019 09:20

    B            |06/11/2019 09:35  | 06/11/2019 09:50

    For each object, i take the first date, and then i would like the smallest start date that is >= of end date of previous raw selected.

    Actually i do it with a cursor, but it is not a good solution. I would like to do it with a query, but i don't know how.

    Thanks for help

    Thomas

    EDIT :  In fact, for an object, i would like all start date that is not between start and end date of another row.

     

     

     

     

     

     

    • This topic was modified 6 days, 19 hours ago by  jolann69.
    • This topic was modified 6 days, 7 hours ago by  jolann69.
  • Jonathan AC Roberts

    SSCoach

    Points: 16929

    ;WITH MyTable AS 
    (
    SELECT * FROM (VALUES
    ('A', '06/11/2019 09:00', '06/11/2019 09:15'),
    ('A', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('A', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('A', '06/11/2019 09:15', '06/11/2019 09:30'),
    ('B', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('B', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('B', '06/11/2019 09:15', '06/11/2019 09:30'),
    ('B', '06/11/2019 09:35', '06/11/2019 09:50'))
    T(IdObject, StartDate, EndDate)
    ),
    CTE AS (
    SELECT IdObject, MIN(StartDate) MinStartDate,MAX(EndDate) MaxEndDate
    FROM MyTable
    GROUP BY IdObject
    )
    SELECT t.*
    FROM MyTable t
    INNER JOIN CTE
    ON CTE.IdObject = t.IdObject
    AND (CTE.MaxEndDate = t.EndDate
    OR CTE.MinStartDate = t.StartDate)

    Capture

  • DesNorton

    SSC-Insane

    Points: 22810

    Although Jonathan's solution works for the given sample data, I believe that this is actually an ISLANDS problem.

    Unfortunately, I don't have the time to put together a solution at this point.

  • jolann69

    Old Hand

    Points: 334

    Hi,

    Thanks for your answers.

    Jonathan solution doesn't work for my case. Because in the sample i have just put few rows, but in fact i have hundreds of raw, and i don't only wan't the min and the max start date, but for an object, i need all start date that is not between start and end date of another row.

    • This reply was modified 6 days, 7 hours ago by  jolann69.
  • koti.raavi

    SSCrazy

    Points: 2684

    Below query should works. Change LEAD to LAG if any change in requirement.

    ;WITH MyTable AS 
    (
    SELECT * FROM (VALUES
    ('A', '06/11/2019 09:00', '06/11/2019 09:15'),
    ('A', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('A', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('A', '06/11/2019 09:15', '06/11/2019 09:30'),
    ('B', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('B', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('B', '06/11/2019 09:15', '06/11/2019 09:30'),
    ('B', '06/11/2019 09:35', '06/11/2019 09:50'))
    T(IdObject, StartDate, EndDate)
    )

    ,CTE AS (
    SELECT *, LEAD(Startdate) OVER(PARTITION BY IdObject ORDER BY Startdate ASC) AS NextRow_StartDate
    , LEAD(EndDate) OVER(PARTITION BY IdObject ORDER BY Startdate ASC) AS NextRow_EndDate

    FROM Mytable
    ) SELECT * FROM CTE
    WHERE
    StartDate NOT BETWEEN NextRow_StartDate AND NextRow_EndDate

    OR NextRow_StartDate IS NULL
  • ChrisM@Work

    SSC Guru

    Points: 186053

    ;WITH MyTable AS
    (
    SELECT * FROM (VALUES
    ('A', '06/11/2019 09:00', '06/11/2019 09:15'),
    ('A', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('A', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('A', '06/11/2019 09:15', '06/11/2019 09:30'),

    ('B', '06/11/2019 09:05', '06/11/2019 09:20'),
    ('B', '06/11/2019 09:10', '06/11/2019 09:25'),
    ('B', '06/11/2019 09:15', '06/11/2019 09:30'),
    ('B', '06/11/2019 09:35', '06/11/2019 09:50'))
    T(IdObject, StartDate, EndDate)
    ),
    OrderedData AS (
    SELECT *, RowID = ROW_NUMBER() OVER(PARTITION BY IdObject ORDER BY StartDate)
    FROM MyTable
    )

    SELECT *
    FROM OrderedData m
    WHERE NOT EXISTS (
    SELECT 1
    FROM OrderedData i
    WHERE i.IdObject = m.IdObject
    AND i.RowID <> m.RowID
    AND m.StartDate BETWEEN i.StartDate AND i.EndDate
    )

    • This reply was modified 6 days, 5 hours ago by  ChrisM@Work.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • jolann69

    Old Hand

    Points: 334

    Hi,

    Really Thanks for your help.

    @koti.raavi, i have try your solution : With LEAD, it's return all rows :

    res1

    With LAG we are near the good result, but it's missing one result : (A;06/11/2019 09:15;06/11/2019 09:30):

    res2

    @ChrisM@Work, with your result, we have the same problem, it's missing one result (A;06/11/2019 09:15;06/11/2019 09:30):

    res2

     

    In both case, i was thinking its was because BETWEEN exclude result where EndDate of previous row equals StartDate of the other row. That's why i have try to replace in the query the between with >= AND <= but i have same problem.

    Thanks again, i really appreciate your help !

     

    • This reply was modified 6 days, 3 hours ago by  jolann69.
  • koti.raavi

    SSCrazy

    Points: 2684

    Hello,  below is the data before filtering out, if you see A-06/11/2019 9:15  between A-06/11/2019 9:10 and A-06/11/2019 9:25, that's why it is not showing in end result. It means start date is between start and end dates of previous row. please see full result set for more clarify ..

    Capture

     

     

  • jolann69

    Old Hand

    Points: 334

    Effectivly, it' is between the previous row, but not between the previous row that is return by the select.

    it is for a booking application, i have to return all possible dates, that's why i need for object A return :

    09:00 - 09:15

    09:15 - 09:30

  • ChrisM@Work

    SSC Guru

    Points: 186053

    koti.raavi wrote:

    Hello,  below is the data before filtering out, if you see A-06/11/2019 9:15  between A-06/11/2019 9:10 and A-06/11/2019 9:25, that's why it is not showing in end result. It means start date is between start and end dates of previous row. please see full result set for more clarify ..

    Capture

     

    The start date of row 4 is between the start date and end date of row two, which is why it's excluded from the result set using my query.

    I think you need to more precisely define your requirement.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • jolann69

    Old Hand

    Points: 334

    Hi,

    Sorry but it is not very easy for me to explain clearly what i need in English.

    I need to return for each Object all possible couple of date that are not cross.

    The previous objet must not be the row before, but the previous returned row.

    Here what i do in my cursor :

    I do a select orderby Object, startdate

    I take the first return for an object. Then i boucle of each following row to find another one that startdate is superior or egal than the endDate of the first one. When i find it, the last select row became the reference, and i try to find another one after until all are find or until it's changing of object :

    here are the code of my cursor i would like to replace :

    		DECLARE @dateFrom_old datetime;
    DECLARE @dateTo_old datetime;
    DECLARE @object_old int;


    DECLARE @dateFrom_new datetime;
    DECLARE @dateTo_new datetime;
    DECLARE @object_new int;



    DECLARE insertedDataCursor CURSOR
    FOR SELECT *
    FROM @Planning_Proposal_Temp
    ORDER BY Object, dateFrom


    OPEN insertedDataCursor


    FETCH NEXT FROM insertedDataCursor INTO @dateFrom_new,
    @dateTo_new,
    @object_new


    WHILE @@FETCH_STATUS = 0
    BEGIN

    IF @dateFrom_old IS NOT NULL AND
    @dateTo_old IS NOT NULL AND
    @object_old IS NOT NULL

    BEGIN

    IF ((@object_old <> @object_new) OR (@dateFrom_new >= @dateTo_old))
    BEGIN
    INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @dateFrom_new,
    @dateTo_new,
    @object_new)

    SET @dateFrom_old = @dateFrom_new;
    SET @dateTo_old = @dateTo_new
    SET @object_old = @object_new


    END

    END

    ELSE
    BEGIN
    INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @dateFrom_new,
    @dateTo_new,
    @object_new)

    SET @dateFrom_old = @dateFrom_new;
    SET @dateTo_old = @dateTo_new
    SET @object_old = @object_new
    END



    FETCH NEXT FROM insertedDataCursor INTO @dateFrom_new,
    @dateTo_new,
    @object_new

    END

    CLOSE insertedDataCursor
    DEALLOCATE insertedDataCursor

     

    • This reply was modified 6 days, 3 hours ago by  jolann69.
    • This reply was modified 6 days, 3 hours ago by  jolann69.
    • This reply was modified 6 days, 3 hours ago by  jolann69.
    • This reply was modified 6 days, 3 hours ago by  jolann69.
    • This reply was modified 6 days, 3 hours ago by  jolann69.
  • Jonathan AC Roberts

    SSCoach

    Points: 16929

    You will get better responses if you provide some more test data with your required results.

    Also you should make it consumable so in a format like:

    DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
    INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo )
    SELECT *
    FROM (VALUES
    ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
    ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00')) T(IdObject, StartDate, EndDate)
  • jolann69

    Old Hand

    Points: 334

    Hi,

    The Data :

    DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
    INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo )
    SELECT *
    FROM (VALUES
    ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
    ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
    ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
    ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
    ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
    ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
    ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
    ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
    ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
    ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
    ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
    ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
    ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
    ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
    ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
    ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
    ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
    ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
    ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
    ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
    ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
    ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
    ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
    ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
    ) T(IdObject, StartDate, EndDate)

    Here are a complete and working sample of my cursor with more data and with the result i need :

    DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
    INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo )
    SELECT *
    FROM (VALUES
    ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
    ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
    ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
    ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
    ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
    ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
    ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
    ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
    ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
    ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
    ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
    ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
    ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
    ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
    ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
    ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
    ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
    ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
    ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
    ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
    ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
    ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
    ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
    ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
    ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
    ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
    ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
    ) T(IdObject, StartDate, EndDate)


    DECLARE @dateFrom_old datetime;
    DECLARE @dateTo_old datetime;
    DECLARE @object_old varchar(10);


    DECLARE @dateFrom_new datetime;
    DECLARE @dateTo_new datetime;
    DECLARE @object_new varchar(10);
    DECLARE @Planning_Proposal_Temp_Grouped AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)


    DECLARE insertedDataCursor CURSOR
    FOR SELECT *
    FROM @Planning_Proposal_Temp
    ORDER BY Object, dateFrom


    OPEN insertedDataCursor


    FETCH NEXT FROM insertedDataCursor INTO @object_new,
    @dateFrom_new,
    @dateTo_new



    WHILE @@FETCH_STATUS = 0
    BEGIN

    IF @dateFrom_old IS NOT NULL AND
    @dateTo_old IS NOT NULL AND
    @object_old IS NOT NULL

    BEGIN

    IF ((@object_old <> @object_new) OR (@dateFrom_new >= @dateTo_old))
    BEGIN
    INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
    @dateFrom_new,
    @dateTo_new
    )

    SET @dateFrom_old = @dateFrom_new;
    SET @dateTo_old = @dateTo_new
    SET @object_old = @object_new


    END

    END

    ELSE
    BEGIN
    INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
    @dateFrom_new,
    @dateTo_new
    )

    SET @dateFrom_old = @dateFrom_new;
    SET @dateTo_old = @dateTo_new
    SET @object_old = @object_new
    END



    FETCH NEXT FROM insertedDataCursor INTO @object_new, @dateFrom_new, @dateTo_new


    END

    CLOSE insertedDataCursor
    DEALLOCATE insertedDataCursor

    SELECT * FROM @Planning_Proposal_Temp_Grouped

    And the result needed :

    res2

    Thanks all

    Thomas

    • This reply was modified 5 days, 8 hours ago by  jolann69.
  • Jonathan AC Roberts

    SSCoach

    Points: 16929

    I think it can be done with a recursive CTE:

    ;WITH rCTE AS
    (
    SELECT a.object,
    a.dateFrom,
    a.dateTo,
    CONVERT(bigint,1) RowNum
    FROM @Planning_Proposal_Temp a
    WHERE a.dateFrom = (SELECT MIN(b.dateFrom)
    FROM @Planning_Proposal_Temp b
    WHERE b.object = a.object)
    UNION ALL
    SELECT a.object,
    a.dateFrom,
    a.dateTo,
    ROW_NUMBER() OVER (ORDER BY a.DateFrom) RowNum
    FROM @Planning_Proposal_Temp a
    INNER JOIN rCTE r
    ON r.dateTo <= a.dateFrom
    AND r.object = a.object
    AND r.RowNum = 1
    )
    INSERT INTO @Planning_Proposal_Temp_Grouped
    (
    object,
    dateFrom,
    dateTo
    )
    SELECT x.object,
    x.dateFrom,
    x.dateTo
    FROM rCTE x
    WHERE x.RowNum = 1

    It could be that the cursor method is a more efficient option than this.

  • jolann69

    Old Hand

    Points: 334

    It's working, but cursor is more than 100 times faster than recursive CTE. So it's seems to be a bad idea to try to replace it by a query.

Viewing 15 posts - 1 through 15 (of 17 total)

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