Get some row depending of Date of nexts rows
-
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.
-
;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)
-
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.
-
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.
-
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 -
;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
)“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
Hi,
Really Thanks for your help.
@koti.raavi, i have try your solution : With LEAD, it's return all rows :
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):
@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):
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 !
-
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 ..
-
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
- 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 ShawFor 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 -
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 -
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) -
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_GroupedAnd the result needed :
Thanks all
Thomas
-
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 = 1It could be that the cursor method is a more efficient option than this.
-
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 16 total)
You must be logged in to reply to this topic. Login to reply