March 10, 2010 at 4:59 am
I have the following rows in a table:
Date From|Date To
04 January 2010|03 February 2010
06 January 2010|05 February 2010
26 January 2010|25 February 2010
01 February 2010|03 March 2010
02 February 2010|04 March 2010
08 February 2010|10 March 2010
09 February 2010|11 March 2010
03 March 2010|02 April 2010
15 March 2010 |14 April 2010
I need to just keep the following:
Date From|Date To
04 January 2010|03 February 2010
08 February 2010|10 March 2010
15 March 2010 |14 April 2010
And delete the rest.
For example: any "date froms" that overlap between the "first date from" -> "first date to" should be removed (i.e. any "date froms" between 04 January 2010 and 03 February 2010). then do the same for the next valid date range (i.e. any "dates froms" between 08 February 2010 and 10 March 2010). and so on...
Any help on this would be great, thanks
March 10, 2010 at 5:51 am
What about this row?
06 January 2010 |05 February 2010
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
March 10, 2010 at 5:57 am
That has a "date from" = "06 January 2010" so falls between the first valid date range of "04 January 2010" and "03 February 2010", and I would need to delete that row.
March 10, 2010 at 6:05 am
Try this one..
DECLARE@tblTable TABLE
(
DateFromSMALLDATETIME,
DateToSMALLDATETIME
)
INSERT@tblTable
SELECT'04 January 2010', '03 February 2010' UNION ALL
SELECT'06 January 2010', '05 February 2010' UNION ALL
SELECT'26 January 2010', '25 February 2010' UNION ALL
SELECT'01 February 2010', '03 March 2010' UNION ALL
SELECT'02 February 2010', '04 March 2010' UNION ALL
SELECT'08 February 2010', '10 March 2010' UNION ALL
SELECT'09 February 2010', '11 March 2010' UNION ALL
SELECT'03 March 2010', '02 April 2010' UNION ALL
SELECT'15 March 2010', '14 April 2010'
; WITH cteTable AS
(
SELECTROW_NUMBER() OVER( ORDER BY DateFrom ) Row, *
FROM@tblTable
), cteTable1 AS
(
SELECTDateFrom, DateTo, 1 Row
FROMcteTable
WHERERow = 1
UNION ALL
SELECTDateFrom, DateTo, CAST( Row AS INT )
FROM(
SELECT ROW_NUMBER() OVER( ORDER BY T.DateFrom, T.DateTo ) RowNum, T.DateFrom, T.DateTo, T.Row
FROM cteTable T
INNER JOIN cteTable1 T1
ON T.Row > T1.Row
AND T.DateFrom NOT BETWEEN T1.DateFrom AND T1.DateTo
) T
WHERERowNum = 1
)
SELECT*
FROMcteTable1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 10, 2010 at 6:14 am
yep that works great thanks
March 10, 2010 at 6:58 am
Written independently of Kingston's solution, this is my take on the recursive CTE, without ROW_NUMBER:
DECLARE @T
TABLE (
DateFrom DATETIME NOT NULL PRIMARY KEY,
DateTo DATETIME NOT NULL
);
INSERT @T (DateFrom, DateTo)
SELECT '04 January 2010', '03 February 2010' UNION ALL
SELECT '06 January 2010', '05 February 2010' UNION ALL
SELECT '26 January 2010', '25 February 2010' UNION ALL
SELECT '01 February 2010', '03 March 2010' UNION ALL
SELECT '02 February 2010', '04 March 2010' UNION ALL
SELECT '08 February 2010', '10 March 2010' UNION ALL
SELECT '09 February 2010', '11 March 2010' UNION ALL
SELECT '03 March 2010', '02 April 2010' UNION ALL
SELECT '15 March 2010', '14 April 2010';
WITH [Recursive]
AS (
-- Anchor: first row, as ordered by DateFrom
SELECT TOP (1)
DateFrom, DateTo
FROM @T T1
ORDER BY
T1.DateFrom ASC
UNION ALL
-- Recursive:
SELECT T1.DateFrom, T1.DateTo
FROM Recursive R
JOIN @T T1
-- Follows on from the last DateTo
ON T1.DateFrom > R.DateTo
WHERE NOT EXISTS
(
-- No other row that meets the same
-- criteria, and has and earlier
-- DateFrom
SELECT *
FROM @T T2
WHERE T2.DateFrom > R.DateTo
AND T2.DateFrom < T1.DateFrom
)
)
-- Delete the other records
DELETE T
FROM @T T
WHERE NOT EXISTS
(
SELECT DateFrom, DateTo
FROM Recursive R
WHERE R.DateFrom = T.DateFrom
);
-- Show the records that are left
SELECT DateFrom, DateTo
FROM @T
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 7:04 am
yeah that works great too, cheers for both your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply