October 23, 2012 at 7:06 am
I have a dataset with DateTime and Rowcount. I need to select rows only if 7 days or more have elapsed since a previous qualifying row.
In the following sample, I'd want to get rows 1,2,3,6,7,8,9,11,15
I know I can do this using a cursor, but any ideas on how to write a query without using a cursor? (SQL server 2008)
Rows:
EventDateTimeRow
10/10/2012 9:011
10/2/2012 9:052
9/10/2012 12:153
9/6/2012 9:354 <-- skip because 7 days haven't elapsed since row 3 date/time
9/5/2012 13:235 <-- skip because 7 days haven't elapsed since row 3 date/time
8/29/2012 8:596
8/21/2012 9:437
8/7/2012 9:368
7/3/2012 8:099
6/27/2012 8:1710 <-- skip because 7 days haven't elapsed since row 9 date/time
6/26/2012 8:5911
6/22/2012 9:2012 <-- skip because 7 days haven't elapsed since row 11 date/time
6/21/2012 9:0013 <-- skip because 7 days haven't elapsed since row 11 date/time
6/20/2012 8:1514 <-- skip because 7 days haven't elapsed since row 11 date/time
6/15/2012 10:3415
October 23, 2012 at 8:47 am
Can you post additional information.
Other than trying to select the rows >= 7 Days What problem are you trying to solve here?
Please read this.
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
The requirement is very odd, and given your example.
And why wouldn't row 4 be returned?
It has a preceeding row of 8/29 that is > 7 days?
And why would row 15 be returned? It has No preceeding rows?
EventDateTime Row
10/10/2012 9:01 1
10/2/2012 9:05 2
9/10/2012 12:15 3
9/6/2012 9:35 4 <-- skip because 7 days haven't elapsed since row 3 date/time
9/5/2012 13:23 5 <-- skip because 7 days haven't elapsed since row 3 date/time
8/29/2012 8:59 6
8/21/2012 9:43 7
8/7/2012 9:36 8
7/3/2012 8:09 9
6/27/2012 8:17 10 <-- skip because 7 days haven't elapsed since row 9 date/time
6/26/2012 8:59 11
6/22/2012 9:20 12 <-- skip because 7 days haven't elapsed since row 11 date/time
6/21/2012 9:00 13 <-- skip because 7 days haven't elapsed since row 11 date/time
6/20/2012 8:15 14 <-- skip because 7 days haven't elapsed since row 11 date/time
6/15/2012 10:34 15
October 23, 2012 at 9:08 am
The data is ordered by descending date. I only want data with a minimum of 7 days between the data, but not a minimum of 7 days between consecutive records. So if I have a record every day for 8 days, I want day 1 and day 8 but not all the readings in between.
Here is the code to create the data:
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(DateValue)
SELECT '2012-10-10 09:01:00'
UNION ALL SELECT '2012-10-02 09:05:00'
UNION ALL SELECT '2012-09-10 12:15:00'
UNION ALL SELECT '2012-09-06 09:35:00'
UNION ALL SELECT '2012-09-05 13:23:00'
UNION ALL SELECT '2012-08-29 08:59:00'
UNION ALL SELECT '2012-08-07 09:36:00'
UNION ALL SELECT '2012-07-24 08:58:00'
UNION ALL SELECT '2012-07-10 07:40:00'
UNION ALL SELECT '2012-07-03 08:09:00'
UNION ALL SELECT '2012-06-27 08:17:00'
UNION ALL SELECT '2012-06-26 08:59:00'
UNION ALL SELECT '2012-06-22 09:20:00'
UNION ALL SELECT '2012-06-20 08:15:00'
UNION ALL SELECT '2012-06-15 10:34:00'
October 23, 2012 at 1:28 pm
Using A CTE and some aggregates,
Please test this throroughly, No guarantees implied.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
DateValue DATETIME
)
INSERT INTO #mytable
(DateValue)
SELECT '2012-10-10 09:01:00'
UNION ALL SELECT '2012-10-02 09:05:00'
UNION ALL SELECT '2012-09-10 12:15:00'
UNION ALL SELECT '2012-09-06 09:35:00'
UNION ALL SELECT '2012-09-05 13:23:00'
UNION ALL SELECT '2012-08-29 08:59:00'
UNION ALL SELECT '2012-08-07 09:36:00'
UNION ALL SELECT '2012-07-24 08:58:00'
UNION ALL SELECT '2012-07-10 07:40:00'
UNION ALL SELECT '2012-07-03 08:09:00'
UNION ALL SELECT '2012-06-27 08:17:00'
UNION ALL SELECT '2012-06-26 08:59:00'
UNION ALL SELECT '2012-06-22 09:20:00'
UNION ALL SELECT '2012-06-20 08:15:00'
UNION ALL SELECT '2012-06-15 10:34:00'
;WITH mRow AS (
-- Get the First Date > than 6 Days for each Row
SELECT m.ID, m.DateValue, MAX(o.DateValue) AS mdv, ROW_NUMBER() OVER( ORDER BY MAX(o.DateValue) desc) AS rNum
FROM #mytable m
LEFT OUTER JOIN #mytable o
ON m.DateValue > o.DateValue
WHERE ISNULL(DATEDIFF(dd, o.DateValue, m.DateValue), 7) > 6
GROUP BY m.ID, m.DateValue)
SELECT a.ID, a.DateValue
FROM mRow a
-- JOIN back to itself to exclude rows that were skipped due to previously being < 7 days from prevous row
LEFT OUTER JOIN mRow b
ON a.DateValue = b.mdv
WHERE (a.rNum = 1 -- The first Date.
OR b.rNum IS NOT NULL) -- And not previously skipped
-- Get rid of Duplicates
GROUP BY a.ID, a.DateValue
October 23, 2012 at 2:14 pm
Thank you very much! It works very well! Exactly what I needed!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply