July 19, 2010 at 10:04 am
Hi!
i have a table that contains a datetime column.
That column contains the date and hour of each five minutes.
sample.
value name datetime
27,3346 Caudal 18-05-2010 9:12:51
27,9530 Caudal 18-05-2010 9:17:47
27,2727 Caudal 18-05-2010 9:22:49
28,0767 Caudal 18-05-2010 9:27:52
28,0767 Caudal 18-05-2010 9:32:54
I need to filter by datetime but get each hour of each day.
This is what i expect to get;
value name datetime
27,3346 Caudal 18-05-2010 9:12:51
27,9530 Caudal 18-05-2010 10:12:47
27,2727 Caudal 18-05-2010 11:12:49
28,0767 Caudal 18-05-2010 12:12:52
28,0767 Caudal 18-05-2010 12:12:54
Thanks in advance.
July 19, 2010 at 10:43 am
On what basis would you want to return
'28,0767 Caudal 18-05-2010 12:12:52'
from the record that is stored as
28,0767 Caudal 18-05-2010 9:27:52?'
Without explaining exactly what you're trying to do and why, it seems very random.
July 19, 2010 at 12:14 pm
im sorry if i wasnt clear.
Here are actuals records.
26,4069 Caudal 20-05-2010 1:02:12
26,5306 Caudal 20-05-2010 1:07:13
26,8398 Caudal 20-05-2010 1:12:16
26,8398 Caudal 20-05-2010 1:17:18
26,9017 Caudal 20-05-2010 1:22:21
27,1490 Caudal 20-05-2010 1:27:23
26,9635 Caudal 20-05-2010 1:32:30
27,1490 Caudal 20-05-2010 1:37:32
27,0872 Caudal 20-05-2010 1:42:31
26,9635 Caudal 20-05-2010 1:47:33
26,9017 Caudal 20-05-2010 1:52:35
27,0872 Caudal 20-05-2010 1:57:38
27,0254 Caudal 20-05-2010 2:02:41
26,8398 Caudal 20-05-2010 2:07:45
27,0254 Caudal 20-05-2010 2:12:46
26,7780 Caudal 20-05-2010 2:17:52
26,8398 Caudal 20-05-2010 2:22:53
26,5925 Caudal 20-05-2010 2:32:56
27,1490 Caudal 20-05-2010 2:32:57
26,8398 Caudal 20-05-2010 2:37:58
26,3451 Caudal 20-05-2010 2:43:05
26,8398 Caudal 20-05-2010 2:48:07
26,4069 Caudal 20-05-2010 2:53:06
26,5306 Caudal 20-05-2010 2:58:08
26,4688 Caudal 20-05-2010 3:03:10
I want the records closer to a full hour. This would be the output from the above data.
26,4069 Caudal 20-05-2010 1:02:12
27,0872 Caudal 20-05-2010 1:57:38
26,5306 Caudal 20-05-2010 2:58:08
Hope that makes it clear.
July 19, 2010 at 3:15 pm
Here is one way: -- Setup Data
SET DATEFORMAT DMY
DECLARE @Foo TABLE (Val1 VARCHAR(50), Val2 VARCHAR(50), DateVal DATETIME)
INSERT @Foo
SELECT '26,4069', 'Caudal', '20-05-2010 1:02:12'
UNION ALL SELECT '26,5306', 'Caudal', '20-05-2010 1:07:13'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 1:12:16'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 1:17:18'
UNION ALL SELECT '26,9017', 'Caudal', '20-05-2010 1:22:21'
UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 1:27:23'
UNION ALL SELECT '26,9635', 'Caudal', '20-05-2010 1:32:30'
UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 1:37:32'
UNION ALL SELECT '27,0872', 'Caudal', '20-05-2010 1:42:31'
UNION ALL SELECT '26,9635', 'Caudal', '20-05-2010 1:47:33'
UNION ALL SELECT '26,9017', 'Caudal', '20-05-2010 1:52:35'
UNION ALL SELECT '27,0872', 'Caudal', '20-05-2010 1:57:38'
UNION ALL SELECT '27,0254', 'Caudal', '20-05-2010 2:02:41'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:07:45'
UNION ALL SELECT '27,0254', 'Caudal', '20-05-2010 2:12:46'
UNION ALL SELECT '26,7780', 'Caudal', '20-05-2010 2:17:52'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:22:53'
UNION ALL SELECT '26,5925', 'Caudal', '20-05-2010 2:32:56'
UNION ALL SELECT '27,1490', 'Caudal', '20-05-2010 2:32:57'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:37:58'
UNION ALL SELECT '26,3451', 'Caudal', '20-05-2010 2:43:05'
UNION ALL SELECT '26,8398', 'Caudal', '20-05-2010 2:48:07'
UNION ALL SELECT '26,4069', 'Caudal', '20-05-2010 2:53:06'
UNION ALL SELECT '26,5306', 'Caudal', '20-05-2010 2:58:08'
UNION ALL SELECT '26,4688', 'Caudal', '20-05-2010 3:03:10'
-- Select Rows
SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0)
ORDER BY ABS(DATEDIFF(SECOND, DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0), DateVal))
) AS RowNum
FROM
) AS T
WHERE
RowNum = 1
July 19, 2010 at 3:43 pm
Wow, you beat me to it and with 10 less lines of code
I made one adjustment to your code (in the order by)
ORDER BY ABS(CAST(DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60, 0) - DateVal AS DECIMAL(18, 17)))
because a second is an eternity!
[edit] actually:
ORDER BY ABS(DATEDIFF(MS, DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, DateVal) / 60.0, 0) * 60,0), DateVal))
would be better
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply