Get some row depending of Date of nexts rows

Question

Get some row depending of Date of nexts rows

jolann69

Old Hand

Points: 334
More actions
November 6, 2019 at 8:45 pm
Go to Answer
#3695565
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 4 years, 6 months ago by jolann69.
- This topic was modified 4 years, 6 months ago by jolann69.

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

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

Jonathan AC Roberts SSCoach Points: 18269 More actions · Answer 1

;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: 24284 More actions · Answer 2

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.

How to post data/code on a forum to get the best help.

jolann69 Old Hand Points: 334 More actions · Answer 3

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 4 years, 6 months ago by jolann69.

koti.raavi SSCrazy Points: 2756 More actions · Answer 4

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: 186127 More actions · Answer 5

;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 4 years, 6 months ago by ChrisM@Work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

jolann69 Old Hand Points: 334 More actions · Answer 6

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 4 years, 6 months ago by jolann69.

koti.raavi SSCrazy Points: 2756 More actions · Answer 7

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 More actions · Answer 8

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: 186127 More actions · Answer 9

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 ..

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

jolann69 Old Hand Points: 334 More actions · Answer 10

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 4 years, 6 months ago by jolann69.
This reply was modified 4 years, 6 months ago by jolann69.
This reply was modified 4 years, 6 months ago by jolann69.
This reply was modified 4 years, 6 months ago by jolann69.
This reply was modified 4 years, 6 months ago by jolann69.

Jonathan AC Roberts SSCoach Points: 18269 More actions · Answer 11

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 More actions · Answer 12

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 4 years, 6 months ago by jolann69.

Jonathan AC Roberts SSCoach Points: 18269 More actions · Answer 13

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 More actions · Answer 14

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.