December 14, 2009 at 4:54 am
I have a sequence of DateTime values that are written every minutes into the db:
IMEI, Date
3 2009-12-10 11:49:09.000
3 2009-12-10 14:30:08.000
3 2009-12-10 14:31:07.000
3 2009-12-10 14:44:15.000
3 2009-12-10 14:45:12.000
3 2009-12-10 14:46:12.000
3 2009-12-10 14:47:14.000
3 2009-12-10 14:48:14.000
3 2009-12-10 14:49:15.000
3 2009-12-10 14:50:13.000
I need to represent them as:
3 2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
3 2009-12-10 14:30:08.000 2009-12-10 14:31:07.000
3 2009-12-10 14:44:15.000 2009-12-10 14:50:13.000
Thanks!
December 14, 2009 at 5:00 am
Can you be more specific and explain how you decide which values to show and which values not to show? How do you decide which records should be grouped by together? Also can you post the table’s creation scrip and the insert statements to the table to make it easier for the people that are trying to help you?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2009 at 5:11 am
Ok. here it the script to populate data:
create table #t (IMEI int, Date datetime)
insert into #t
select
3, '20091210 11:49:09.000'
union select
3, '20091210 14:30:08.000'
union select
3, '20091210 14:31:07.000'
union select
3, '20091210 14:44:15.000'
union select
3, '20091210 14:45:12.000'
union select
3, '20091210 14:46:12.000'
union select
3, '20091210 14:47:14.000'
union select
3, '20091210 14:48:14.000'
union select
3, '20091210 14:49:15.000'
union select
3, '20091210 14:50:13.000'
union select
3, '20091210 14:51:14.000'
union select
3, '20091210 14:52:15.000'
union select
3, '20091210 14:53:17.000'
union select
3, '20091210 14:59:17.000'
union select
3, '20091210 17:24:06.000'
union select
3, '20091210 17:25:09.000'
union select
3, '20091210 17:26:09.000'
union select
3, '20091210 17:27:20.000'
union select
3, '20091210 17:28:16.000'
union select
3, '20091210 17:29:06.000'
union select
3, '20091210 17:30:08.000'
union select
3, '20091210 17:31:12.000'
union select
3, '20091210 17:32:14.000'
union select
3, '20091210 17:33:08.000'
union select
3, '20091210 17:35:24.000'
union select
3, '20091210 17:37:31.000'
union select
3, '20091210 17:38:14.000'
union select
3, '20091210 17:39:15.000'
union select
3, '20091210 17:40:14.000'
union select
3, '20091210 17:41:15.000'
union select
3, '20091210 17:42:13.000'
union select
3, '20091210 17:43:07.000'
union select
3, '20091210 17:49:09.000'
union select
3, '20091210 17:50:16.000'
union select
3, '20091210 17:54:09.000'
union select
3, '20091210 17:58:07.000'
union select
3, '20091210 18:00:17.000'
union select
3, '20091210 18:03:18.000'
union select
3, '20091210 18:14:09.000'
union select
3, '20091210 19:04:10.000'
union select
3, '20091210 19:05:11.000'
union select
3, '20091210 19:21:13.000'
union select
3, '20091210 19:22:08.000'
union select
3, '20091210 19:23:13.000'
December 14, 2009 at 5:17 am
We are grouping values that are written in a sequence of 1 minute, for this values we are show start datetime of a sequence and end datetime of sequence. For example, the following datetime values are written in a sequence:
2009-12-10 14:44:15.000
2009-12-10 14:45:12.000
2009-12-10 14:46:12.000
2009-12-10 14:47:14.000
2009-12-10 14:48:14.000
2009-12-10 14:49:15.000
2009-12-10 14:50:13.000
2009-12-10 14:51:14.000
2009-12-10 14:52:15.000
2009-12-10 14:53:17.000
So we should show them as 2009-12-10 14:44:15.000 2009-12-10 14:53:17.000
If the there is a gap then we should show gaps in both columns like
2009-12-10 11:49:09.000 2009-12-10 11:49:09.000
Hope it's clear now.
December 14, 2009 at 5:19 am
What about an explanation about how to know which values should be discarded and which values should be on the same records?
Adi
EDIT - Posted the question before I saw Hal's third post
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2009 at 7:01 am
There is a contradiction between the explanation and the example. I fallowed the explanation. In my code check for difference that is above 60 seconds. I used 2 common table expressions in order to find the boundaries of the islands and assign a row number to each boundary. Then I joined both CTEs using the value that I got in the row_number function. I have to admit that I’m sure that there are better ways to get this information. Maybe if I’ll have more time later, I’ll try to improve it.
;with LowerBoundry as (
select date, row_number() over (order by date) as RowNum
from #t as t
where not exists (SELECT date from #t as t2 where t.date > t2.date and datediff(ss,t2.date,t.date) <= 60)),
UpperBoundry as (
select Date, row_number() over (order by date) as RowNum
from #t as t
where not exists (SELECT date from #t as t2 where t.date < t2.date and datediff(ss,t.date,t2.date) <= 60))
select LB.date, UB.date
from LowerBoundry LB inner join UpperBoundry UB ON LB.RowNum = UB.RowNum
order by LB.Date
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply