September 16, 2015 at 12:43 am
Hello,
I have following problem and I hope someone could help:
I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
2015-06-05 19:24:53.0001
2015-06-05 19:26:06.0001
2015-06-05 19:31:35.0002
2015-06-05 19:33:02.0003
2015-06-05 19:36:14.0001
2015-06-05 19:38:07.0001
2015-06-05 19:39:51.0001
2015-06-05 19:41:16.0001
2015-06-05 19:44:11.0002
2015-06-05 19:47:13.0001
2015-06-05 19:51:07.0002
Thanks
toni
September 16, 2015 at 12:50 am
toni 667 (9/16/2015)
Hello,I have following problem and I hope someone could help:
I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
2015-06-05 19:24:53.0001
2015-06-05 19:26:06.0001
2015-06-05 19:31:35.0002
2015-06-05 19:33:02.0003
2015-06-05 19:36:14.0001
2015-06-05 19:38:07.0001
2015-06-05 19:39:51.0001
2015-06-05 19:41:16.0001
2015-06-05 19:44:11.0002
2015-06-05 19:47:13.0001
2015-06-05 19:51:07.0002
Thanks
toni
Please explain the relationship between 'value' and your data – I cannot see a 5 in your data.
For a full coded response to your question, please follow the advice in the link in my signature on how best to post a question here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2015 at 12:54 am
the value of 5 was meant as an example for the minimum number of recurrences with the same item
September 16, 2015 at 1:03 am
And the ordering of the data is purely by date/time?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2015 at 1:07 am
yes
September 16, 2015 at 4:06 am
Here's some setup SQL for anyone who wants to try this.
Hint: please do this in future when you submit forum questions 🙂
if object_id('tempdb..#tab1', 'U') is not null
drop table #tab1;
create table #tab1
(
Dt datetime primary key clustered
,Item int not null
);
insert #tab1
(Dt, Item)
values ('2015-06-05 19:05:03.000', 2),
('2015-06-05 19:08:31.000', 2),
('2015-06-05 19:14:50.000', 1),
('2015-06-05 19:19:33.000', 1),
('2015-06-05 19:20:46.000', 1),
('2015-06-05 19:24:53.000', 1),
('2015-06-05 19:26:06.000', 1),
('2015-06-05 19:31:35.000', 2),
('2015-06-05 19:33:02.000', 3),
('2015-06-05 19:36:14.000', 1),
('2015-06-05 19:38:07.000', 1),
('2015-06-05 19:39:51.000', 1),
('2015-06-05 19:41:16.000', 1),
('2015-06-05 19:44:11.000', 2),
('2015-06-05 19:47:13.000', 1),
('2015-06-05 19:51:07.000', 2);
select * from #tab1 t;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2015 at 5:49 am
So given a value of 2, what would you expect as results? All rows that are a member of an "unchanged" group of 2 or more? Or do you need the individual groups of 2 to be indicated somehow, like with a GroupID?
I'm guessing these rows (broken into three sections here for visual clarity only) would be desired for an input value of 2:
2015-06-05 19:05:03.000 2
2015-06-05 19:08:31.000 2
2015-06-05 19:14:50.000 1
2015-06-05 19:19:33.000 1
2015-06-05 19:20:46.000 1
2015-06-05 19:24:53.000 1
2015-06-05 19:26:06.000 1
2015-06-05 19:36:14.000 1
2015-06-05 19:38:07.000 1
2015-06-05 19:39:51.000 1
2015-06-05 19:41:16.000 1
September 16, 2015 at 5:59 am
Yes, this is my desired result. They don't need to be indicated by an ID.
September 16, 2015 at 6:14 am
DECLARE @value int = 2;
WITH cte AS (
SELECT Dt,Item,
ROW_NUMBER() OVER (ORDER BY Dt ASC) -
ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Dt ASC) AS [Grp]
FROM #tab1 t
),
cte2 AS (
SELECT Grp
FROM cte
GROUP BY Grp
HAVING COUNT(*) >= @value
)
SELECT cte.Grp,cte.Dt,cte.Item
FROM cte2
JOIN cte ON cte.Grp = cte2.Grp
ORDER BY cte.Grp ASC,cte.Dt ASC;
Far away is close at hand in the images of elsewhere.
Anon.
September 16, 2015 at 7:31 am
Hello David,
this looks very promising, unfortunately sometimes the value Grp repeats itself on different datetime / item combinations.
Thanks
September 16, 2015 at 7:35 am
toni 667 (9/16/2015)
Hello David,this looks very promising, unfortunately sometimes the value Grp repeats itself on different datetime / item combinations.
Thanks
Please expand the sample data query which I provided, to illustrate this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2015 at 8:01 am
Here you'll find some more sample data.
For example the Grp value 421 is calculated several times.
Thanks
if object_id('tempdb..#tab1', 'U') is not null
drop table #tab1;
create table #tab1
(
Dt datetime primary key clustered
,Item int not null
);
insert #tab1
(Dt, Item)
VALUES
(CAST('20150101 17:58:39.000' as DATETIME),1),
(CAST('20150101 18:02:17.000' as DATETIME),1),
(CAST('20150101 18:05:09.000' as DATETIME),1),
(CAST('20150101 18:08:02.000' as DATETIME),1),
(CAST('20150101 18:10:08.000' as DATETIME),1),
(CAST('20150101 18:12:18.000' as DATETIME),1),
(CAST('20150101 18:14:03.000' as DATETIME),2),
(CAST('20150101 18:16:45.000' as DATETIME),2),
(CAST('20150101 18:19:38.000' as DATETIME),1),
(CAST('20150101 18:22:15.000' as DATETIME),1),
(CAST('20150101 18:28:15.000' as DATETIME),1),
(CAST('20150101 18:36:06.000' as DATETIME),2),
(CAST('20150101 18:42:00.000' as DATETIME),2),
(CAST('20150101 18:46:38.000' as DATETIME),2),
(CAST('20150101 18:50:26.000' as DATETIME),1),
(CAST('20150101 18:55:33.000' as DATETIME),1),
(CAST('20150101 18:59:21.000' as DATETIME),2),
(CAST('20150101 19:00:57.000' as DATETIME),2),
(CAST('20150101 19:02:16.000' as DATETIME),2),
(CAST('20150101 19:03:04.000' as DATETIME),2),
(CAST('20150101 19:04:30.000' as DATETIME),1),
(CAST('20150101 19:05:42.000' as DATETIME),2),
(CAST('20150101 19:06:53.000' as DATETIME),2),
(CAST('20150101 19:09:04.000' as DATETIME),1),
(CAST('20150101 19:10:59.000' as DATETIME),2),
(CAST('20150101 19:16:26.000' as DATETIME),2),
(CAST('20150101 19:19:30.000' as DATETIME),2),
(CAST('20150101 19:40:13.000' as DATETIME),1),
(CAST('20150101 19:40:17.000' as DATETIME),2),
(CAST('20150101 19:43:25.000' as DATETIME),2),
(CAST('20150101 19:47:21.000' as DATETIME),1),
(CAST('20150101 19:49:19.000' as DATETIME),3),
(CAST('20150101 19:52:01.000' as DATETIME),2),
(CAST('20150101 19:55:05.000' as DATETIME),2),
(CAST('20150101 19:56:35.000' as DATETIME),1),
(CAST('20150101 19:59:18.000' as DATETIME),2),
(CAST('20150101 20:00:31.000' as DATETIME),2),
(CAST('20150101 20:01:32.000' as DATETIME),2),
(CAST('20150101 20:03:02.000' as DATETIME),1),
(CAST('20150101 20:04:27.000' as DATETIME),1),
(CAST('20150101 20:06:09.000' as DATETIME),1),
(CAST('20150101 20:09:35.000' as DATETIME),2),
(CAST('20150101 20:11:21.000' as DATETIME),2),
(CAST('20150101 20:15:27.000' as DATETIME),2),
(CAST('20150101 20:19:45.000' as DATETIME),1),
(CAST('20150101 20:22:14.000' as DATETIME),1),
(CAST('20150101 20:24:10.000' as DATETIME),2),
(CAST('20150101 20:26:59.000' as DATETIME),2),
(CAST('20150101 20:28:32.000' as DATETIME),1),
(CAST('20150101 20:30:17.000' as DATETIME),2),
(CAST('20150101 20:31:56.000' as DATETIME),1),
(CAST('20150101 20:32:54.000' as DATETIME),2),
(CAST('20150101 20:35:22.000' as DATETIME),1),
(CAST('20150101 20:41:31.000' as DATETIME),1),
(CAST('20150101 20:44:34.000' as DATETIME),2),
(CAST('20150101 20:49:12.000' as DATETIME),1),
(CAST('20150101 20:53:36.000' as DATETIME),2),
(CAST('20150101 20:57:40.000' as DATETIME),1),
(CAST('20150101 21:00:57.000' as DATETIME),2),
(CAST('20150101 21:05:10.000' as DATETIME),1),
(CAST('20150101 21:07:28.000' as DATETIME),1),
(CAST('20150101 21:10:41.000' as DATETIME),1),
(CAST('20150101 21:13:30.000' as DATETIME),2),
(CAST('20150101 21:17:37.000' as DATETIME),1),
(CAST('20150101 21:20:09.000' as DATETIME),2),
(CAST('20150101 21:22:39.000' as DATETIME),2),
(CAST('20150101 21:25:26.000' as DATETIME),1),
(CAST('20150101 21:30:23.000' as DATETIME),3),
(CAST('20150101 21:34:39.000' as DATETIME),2),
(CAST('20150101 21:36:06.000' as DATETIME),2),
(CAST('20150101 21:38:52.000' as DATETIME),1),
(CAST('20150101 21:42:18.000' as DATETIME),1),
(CAST('20150101 21:45:32.000' as DATETIME),1),
(CAST('20150101 21:49:15.000' as DATETIME),2),
(CAST('20150101 21:51:25.000' as DATETIME),2),
(CAST('20150101 21:54:17.000' as DATETIME),2),
(CAST('20150101 21:56:30.000' as DATETIME),2),
(CAST('20150101 21:59:12.000' as DATETIME),1),
(CAST('20150101 22:02:25.000' as DATETIME),2),
(CAST('20150101 22:06:22.000' as DATETIME),2),
(CAST('20150101 22:10:57.000' as DATETIME),2),
(CAST('20150101 22:14:27.000' as DATETIME),1),
(CAST('20150101 22:17:38.000' as DATETIME),1),
(CAST('20150101 22:20:00.000' as DATETIME),1),
(CAST('20150101 22:22:53.000' as DATETIME),2),
(CAST('20150101 22:25:29.000' as DATETIME),2),
(CAST('20150101 22:26:55.000' as DATETIME),2),
(CAST('20150101 22:29:00.000' as DATETIME),1),
(CAST('20150101 22:30:34.000' as DATETIME),2),
(CAST('20150101 22:31:42.000' as DATETIME),1),
(CAST('20150101 22:33:30.000' as DATETIME),2),
(CAST('20150101 22:34:51.000' as DATETIME),1),
(CAST('20150101 22:36:20.000' as DATETIME),2),
(CAST('20150101 22:37:31.000' as DATETIME),2),
(CAST('20150101 22:39:15.000' as DATETIME),2),
(CAST('20150101 22:40:41.000' as DATETIME),1),
(CAST('20150101 22:42:06.000' as DATETIME),1),
(CAST('20150101 22:43:12.000' as DATETIME),1),
(CAST('20150101 22:44:43.000' as DATETIME),1),
(CAST('20150101 22:45:52.000' as DATETIME),2),
(CAST('20150101 22:47:10.000' as DATETIME),2),
(CAST('20150101 22:48:28.000' as DATETIME),2),
(CAST('20150101 22:49:42.000' as DATETIME),1),
(CAST('20150101 22:50:52.000' as DATETIME),2),
(CAST('20150101 22:52:10.000' as DATETIME),1),
(CAST('20150101 22:53:44.000' as DATETIME),2),
(CAST('20150101 22:54:44.000' as DATETIME),2),
(CAST('20150101 22:56:00.000' as DATETIME),2),
(CAST('20150101 22:57:42.000' as DATETIME),1),
(CAST('20150101 22:59:05.000' as DATETIME),2),
(CAST('20150101 23:00:02.000' as DATETIME),2),
(CAST('20150101 23:01:20.000' as DATETIME),2),
(CAST('20150101 23:02:03.000' as DATETIME),1),
(CAST('20150101 23:03:04.000' as DATETIME),1),
(CAST('20150101 23:04:09.000' as DATETIME),2),
(CAST('20150101 23:05:16.000' as DATETIME),2),
(CAST('20150101 23:07:04.000' as DATETIME),2),
(CAST('20150101 23:08:57.000' as DATETIME),1),
(CAST('20150101 23:10:16.000' as DATETIME),3),
(CAST('20150101 23:11:36.000' as DATETIME),1),
(CAST('20150101 23:13:10.000' as DATETIME),1),
(CAST('20150101 23:14:30.000' as DATETIME),2),
(CAST('20150101 23:16:41.000' as DATETIME),1),
(CAST('20150101 23:18:06.000' as DATETIME),1),
(CAST('20150101 23:19:39.000' as DATETIME),1),
(CAST('20150101 23:21:08.000' as DATETIME),1),
(CAST('20150101 23:22:15.000' as DATETIME),2),
(CAST('20150101 23:23:24.000' as DATETIME),2),
(CAST('20150101 23:24:27.000' as DATETIME),2),
(CAST('20150101 23:25:35.000' as DATETIME),1),
(CAST('20150101 23:27:01.000' as DATETIME),1),
(CAST('20150101 23:28:48.000' as DATETIME),2),
(CAST('20150101 23:29:40.000' as DATETIME),1),
(CAST('20150101 23:30:29.000' as DATETIME),1),
(CAST('20150101 23:31:17.000' as DATETIME),1),
(CAST('20150101 23:32:14.000' as DATETIME),2),
(CAST('20150101 23:33:15.000' as DATETIME),1),
(CAST('20150101 23:34:17.000' as DATETIME),2),
(CAST('20150101 23:35:29.000' as DATETIME),2),
(CAST('20150101 23:36:45.000' as DATETIME),1),
(CAST('20150101 23:38:03.000' as DATETIME),2),
(CAST('20150101 23:39:07.000' as DATETIME),1),
(CAST('20150101 23:40:44.000' as DATETIME),2),
(CAST('20150101 23:42:10.000' as DATETIME),1),
(CAST('20150101 23:43:41.000' as DATETIME),2),
(CAST('20150101 23:45:37.000' as DATETIME),3),
(CAST('20150101 23:46:48.000' as DATETIME),1),
(CAST('20150101 23:48:10.000' as DATETIME),2),
(CAST('20150101 23:49:48.000' as DATETIME),2),
(CAST('20150101 23:51:56.000' as DATETIME),1),
(CAST('20150101 23:53:54.000' as DATETIME),3),
(CAST('20150101 23:56:18.000' as DATETIME),1),
(CAST('20150101 23:58:01.000' as DATETIME),2),
(CAST('20150101 23:59:48.000' as DATETIME),2),
(CAST('20150102 00:01:33.000' as DATETIME),1),
(CAST('20150102 00:03:33.000' as DATETIME),2),
(CAST('20150102 00:04:40.000' as DATETIME),1),
(CAST('20150102 00:06:51.000' as DATETIME),1),
(CAST('20150102 00:10:18.000' as DATETIME),2),
(CAST('20150102 00:11:52.000' as DATETIME),2),
(CAST('20150102 00:13:13.000' as DATETIME),1),
(CAST('20150102 00:15:19.000' as DATETIME),1),
(CAST('20150102 00:17:22.000' as DATETIME),1),
(CAST('20150102 00:20:30.000' as DATETIME),2),
(CAST('20150102 00:22:14.000' as DATETIME),2),
(CAST('20150102 00:24:06.000' as DATETIME),2),
(CAST('20150102 00:27:19.000' as DATETIME),1),
(CAST('20150102 00:29:19.000' as DATETIME),1),
(CAST('20150102 00:32:00.000' as DATETIME),2),
(CAST('20150102 00:34:38.000' as DATETIME),2),
(CAST('20150102 00:36:40.000' as DATETIME),2),
(CAST('20150102 00:39:50.000' as DATETIME),2),
(CAST('20150102 00:41:57.000' as DATETIME),1),
(CAST('20150102 00:44:14.000' as DATETIME),1),
(CAST('20150102 00:49:23.000' as DATETIME),1),
(CAST('20150102 00:50:45.000' as DATETIME),2),
(CAST('20150102 00:52:07.000' as DATETIME),2),
(CAST('20150102 00:53:50.000' as DATETIME),2),
(CAST('20150102 00:55:20.000' as DATETIME),1),
(CAST('20150102 00:57:03.000' as DATETIME),1),
(CAST('20150102 00:58:30.000' as DATETIME),1),
(CAST('20150102 00:59:13.000' as DATETIME),1),
(CAST('20150102 00:59:58.000' as DATETIME),1),
(CAST('20150102 01:01:02.000' as DATETIME),2),
(CAST('20150102 01:02:39.000' as DATETIME),2),
(CAST('20150102 01:05:26.000' as DATETIME),2),
(CAST('20150102 01:07:09.000' as DATETIME),3),
(CAST('20150102 01:09:38.000' as DATETIME),1),
(CAST('20150102 01:11:15.000' as DATETIME),1),
(CAST('20150102 01:12:49.000' as DATETIME),2),
(CAST('20150102 01:14:07.000' as DATETIME),1),
(CAST('20150102 01:16:08.000' as DATETIME),1),
(CAST('20150102 01:17:31.000' as DATETIME),2),
(CAST('20150102 01:19:46.000' as DATETIME),2),
(CAST('20150102 01:21:53.000' as DATETIME),1),
(CAST('20150102 01:23:22.000' as DATETIME),2),
(CAST('20150102 01:25:57.000' as DATETIME),1),
(CAST('20150102 01:27:22.000' as DATETIME),1),
(CAST('20150102 01:28:14.000' as DATETIME),1),
(CAST('20150102 01:29:07.000' as DATETIME),1),
(CAST('20150102 17:59:44.000' as DATETIME),2),
(CAST('20150102 18:00:54.000' as DATETIME),1),
(CAST('20150102 18:02:03.000' as DATETIME),1),
(CAST('20150102 18:02:58.000' as DATETIME),1),
(CAST('20150102 18:03:56.000' as DATETIME),1),
(CAST('20150102 18:04:40.000' as DATETIME),1),
(CAST('20150102 18:05:33.000' as DATETIME),2),
(CAST('20150102 18:06:54.000' as DATETIME),1),
(CAST('20150102 18:07:51.000' as DATETIME),2),
(CAST('20150102 18:08:48.000' as DATETIME),2),
(CAST('20150102 18:09:52.000' as DATETIME),1),
(CAST('20150102 18:12:32.000' as DATETIME),1),
(CAST('20150102 18:13:44.000' as DATETIME),2),
(CAST('20150102 18:14:40.000' as DATETIME),1),
(CAST('20150102 18:15:45.000' as DATETIME),2),
(CAST('20150102 18:16:47.000' as DATETIME),2),
(CAST('20150102 18:17:53.000' as DATETIME),2),
(CAST('20150102 18:18:54.000' as DATETIME),1),
(CAST('20150102 18:19:58.000' as DATETIME),1),
(CAST('20150102 18:20:48.000' as DATETIME),1),
(CAST('20150102 18:22:16.000' as DATETIME),1),
(CAST('20150102 18:23:23.000' as DATETIME),2),
(CAST('20150102 18:25:07.000' as DATETIME),2),
(CAST('20150102 18:26:15.000' as DATETIME),2),
(CAST('20150102 18:27:53.000' as DATETIME),1),
(CAST('20150102 18:29:15.000' as DATETIME),1),
(CAST('20150102 18:30:33.000' as DATETIME),2),
(CAST('20150102 18:31:27.000' as DATETIME),1),
(CAST('20150102 18:32:28.000' as DATETIME),1),
(CAST('20150102 18:33:31.000' as DATETIME),1),
(CAST('20150102 18:34:30.000' as DATETIME),2),
(CAST('20150102 18:35:23.000' as DATETIME),2),
(CAST('20150102 18:36:18.000' as DATETIME),1),
(CAST('20150102 18:37:16.000' as DATETIME),2),
(CAST('20150102 18:38:10.000' as DATETIME),1),
(CAST('20150102 18:40:12.000' as DATETIME),1),
(CAST('20150102 18:41:02.000' as DATETIME),2),
(CAST('20150102 18:41:53.000' as DATETIME),1),
(CAST('20150102 18:43:13.000' as DATETIME),2),
(CAST('20150102 18:44:04.000' as DATETIME),1),
(CAST('20150102 18:44:59.000' as DATETIME),1),
(CAST('20150102 18:45:45.000' as DATETIME),3),
(CAST('20150102 18:46:30.000' as DATETIME),2),
(CAST('20150102 18:47:40.000' as DATETIME),1),
(CAST('20150102 18:48:30.000' as DATETIME),2),
(CAST('20150102 18:49:16.000' as DATETIME),1),
(CAST('20150102 18:50:06.000' as DATETIME),1),
(CAST('20150102 18:51:16.000' as DATETIME),1),
(CAST('20150102 18:52:02.000' as DATETIME),1),
(CAST('20150102 18:52:45.000' as DATETIME),2),
(CAST('20150102 18:53:28.000' as DATETIME),1),
(CAST('20150102 18:54:15.000' as DATETIME),2),
(CAST('20150102 18:56:01.000' as DATETIME),2),
(CAST('20150102 18:57:25.000' as DATETIME),1),
(CAST('20150102 18:59:18.000' as DATETIME),2),
(CAST('20150102 19:00:50.000' as DATETIME),1),
(CAST('20150102 19:03:03.000' as DATETIME),1),
(CAST('20150102 19:04:51.000' as DATETIME),2),
(CAST('20150102 19:07:28.000' as DATETIME),2),
(CAST('20150102 19:09:18.000' as DATETIME),2),
(CAST('20150102 19:11:16.000' as DATETIME),2),
(CAST('20150102 19:13:05.000' as DATETIME),2),
(CAST('20150102 19:15:22.000' as DATETIME),1),
(CAST('20150102 19:17:45.000' as DATETIME),1),
(CAST('20150102 19:19:11.000' as DATETIME),1),
(CAST('20150102 19:20:40.000' as DATETIME),2),
(CAST('20150102 19:22:49.000' as DATETIME),2),
(CAST('20150102 19:24:07.000' as DATETIME),3),
(CAST('20150102 19:25:30.000' as DATETIME),2),
(CAST('20150102 19:27:01.000' as DATETIME),2),
(CAST('20150102 19:28:46.000' as DATETIME),1),
(CAST('20150102 19:30:29.000' as DATETIME),2),
(CAST('20150102 19:32:16.000' as DATETIME),1),
(CAST('20150102 19:36:46.000' as DATETIME),2),
(CAST('20150102 19:37:59.000' as DATETIME),1),
(CAST('20150102 19:39:03.000' as DATETIME),2),
(CAST('20150102 19:41:08.000' as DATETIME),1),
(CAST('20150102 19:42:18.000' as DATETIME),1),
(CAST('20150102 19:43:47.000' as DATETIME),2),
(CAST('20150102 19:45:08.000' as DATETIME),2),
(CAST('20150102 19:46:13.000' as DATETIME),1),
(CAST('20150102 19:47:31.000' as DATETIME),2),
(CAST('20150102 19:48:34.000' as DATETIME),2),
(CAST('20150102 19:49:46.000' as DATETIME),2),
(CAST('20150102 19:51:32.000' as DATETIME),1),
(CAST('20150102 19:53:05.000' as DATETIME),2),
(CAST('20150102 19:55:47.000' as DATETIME),1),
(CAST('20150102 19:58:41.000' as DATETIME),1),
(CAST('20150102 20:02:34.000' as DATETIME),2),
(CAST('20150102 20:04:18.000' as DATETIME),2),
(CAST('20150102 20:05:34.000' as DATETIME),2),
(CAST('20150102 20:07:02.000' as DATETIME),2),
(CAST('20150102 20:08:57.000' as DATETIME),1),
(CAST('20150102 20:11:54.000' as DATETIME),2),
(CAST('20150102 20:13:27.000' as DATETIME),1),
(CAST('20150102 20:15:06.000' as DATETIME),1),
(CAST('20150102 20:16:36.000' as DATETIME),1),
(CAST('20150102 20:18:47.000' as DATETIME),1),
(CAST('20150102 20:20:54.000' as DATETIME),2),
(CAST('20150102 20:24:41.000' as DATETIME),1),
(CAST('20150102 20:26:23.000' as DATETIME),2),
(CAST('20150102 20:28:23.000' as DATETIME),2),
(CAST('20150102 20:32:01.000' as DATETIME),1),
(CAST('20150102 20:37:25.000' as DATETIME),1),
(CAST('20150102 20:39:25.000' as DATETIME),1),
(CAST('20150102 20:41:48.000' as DATETIME),1),
(CAST('20150102 20:44:47.000' as DATETIME),2),
(CAST('20150102 20:48:14.000' as DATETIME),2),
(CAST('20150102 20:50:14.000' as DATETIME),2),
(CAST('20150102 20:52:05.000' as DATETIME),1),
(CAST('20150102 20:55:16.000' as DATETIME),2),
(CAST('20150102 20:58:03.000' as DATETIME),2),
(CAST('20150102 21:01:36.000' as DATETIME),2),
(CAST('20150102 21:06:13.000' as DATETIME),2),
(CAST('20150102 21:08:46.000' as DATETIME),1),
(CAST('20150102 21:11:23.000' as DATETIME),2),
(CAST('20150102 21:14:09.000' as DATETIME),2),
(CAST('20150102 21:23:07.000' as DATETIME),2),
(CAST('20150102 21:26:20.000' as DATETIME),2),
(CAST('20150102 21:30:33.000' as DATETIME),2),
(CAST('20150102 21:32:44.000' as DATETIME),2),
(CAST('20150102 21:37:59.000' as DATETIME),1),
(CAST('20150102 21:40:52.000' as DATETIME),1),
(CAST('20150102 21:44:58.000' as DATETIME),1),
(CAST('20150102 21:49:10.000' as DATETIME),2),
(CAST('20150102 21:54:26.000' as DATETIME),2),
(CAST('20150102 21:57:14.000' as DATETIME),1),
(CAST('20150102 22:01:34.000' as DATETIME),2),
(CAST('20150102 22:03:57.000' as DATETIME),1),
(CAST('20150102 22:07:01.000' as DATETIME),1),
(CAST('20150102 22:10:11.000' as DATETIME),2),
(CAST('20150102 22:12:28.000' as DATETIME),1),
(CAST('20150102 22:14:34.000' as DATETIME),2),
(CAST('20150102 22:18:57.000' as DATETIME),2),
(CAST('20150102 22:21:15.000' as DATETIME),2),
(CAST('20150102 22:23:51.000' as DATETIME),2),
(CAST('20150102 22:28:23.000' as DATETIME),2),
(CAST('20150102 22:30:56.000' as DATETIME),2),
(CAST('20150102 22:33:26.000' as DATETIME),1),
(CAST('20150102 22:35:34.000' as DATETIME),1),
(CAST('20150102 22:37:36.000' as DATETIME),2),
(CAST('20150102 22:39:15.000' as DATETIME),1),
(CAST('20150102 22:40:59.000' as DATETIME),2),
(CAST('20150102 22:42:48.000' as DATETIME),1),
(CAST('20150102 22:44:45.000' as DATETIME),2),
(CAST('20150102 22:46:13.000' as DATETIME),2),
(CAST('20150102 22:48:45.000' as DATETIME),2),
(CAST('20150102 22:51:08.000' as DATETIME),1),
(CAST('20150102 22:54:31.000' as DATETIME),1),
(CAST('20150102 22:57:21.000' as DATETIME),1),
(CAST('20150102 23:00:44.000' as DATETIME),1),
(CAST('20150102 23:04:53.000' as DATETIME),1),
(CAST('20150102 23:07:05.000' as DATETIME),1),
(CAST('20150102 23:09:24.000' as DATETIME),2),
(CAST('20150102 23:11:38.000' as DATETIME),2),
(CAST('20150102 23:15:03.000' as DATETIME),1),
(CAST('20150102 23:17:03.000' as DATETIME),2),
(CAST('20150102 23:20:30.000' as DATETIME),1),
(CAST('20150102 23:22:31.000' as DATETIME),2),
(CAST('20150102 23:25:22.000' as DATETIME),2),
(CAST('20150102 23:28:28.000' as DATETIME),1),
(CAST('20150102 23:33:10.000' as DATETIME),2),
(CAST('20150102 23:36:17.000' as DATETIME),2),
(CAST('20150102 23:38:32.000' as DATETIME),2),
(CAST('20150102 23:41:22.000' as DATETIME),2),
(CAST('20150102 23:44:03.000' as DATETIME),2),
(CAST('20150102 23:46:03.000' as DATETIME),1),
(CAST('20150102 23:48:00.000' as DATETIME),2),
(CAST('20150102 23:50:14.000' as DATETIME),2),
(CAST('20150102 23:53:52.000' as DATETIME),2),
(CAST('20150102 23:57:19.000' as DATETIME),2),
(CAST('20150103 00:00:31.000' as DATETIME),2),
(CAST('20150103 00:06:42.000' as DATETIME),1),
(CAST('20150103 00:12:44.000' as DATETIME),2),
(CAST('20150103 00:15:32.000' as DATETIME),2),
(CAST('20150103 00:17:33.000' as DATETIME),2),
(CAST('20150103 00:18:49.000' as DATETIME),1),
(CAST('20150103 00:20:05.000' as DATETIME),2),
(CAST('20150103 00:22:42.000' as DATETIME),1),
(CAST('20150103 00:24:27.000' as DATETIME),2),
(CAST('20150103 00:26:10.000' as DATETIME),2),
(CAST('20150103 00:27:57.000' as DATETIME),2),
(CAST('20150103 00:29:30.000' as DATETIME),1),
(CAST('20150103 00:31:10.000' as DATETIME),2),
(CAST('20150103 00:32:54.000' as DATETIME),1),
(CAST('20150103 00:36:08.000' as DATETIME),1),
(CAST('20150103 00:38:05.000' as DATETIME),2),
(CAST('20150103 00:39:23.000' as DATETIME),2),
(CAST('20150103 00:40:58.000' as DATETIME),2),
(CAST('20150103 00:43:18.000' as DATETIME),2),
(CAST('20150103 00:45:23.000' as DATETIME),1),
(CAST('20150103 00:47:15.000' as DATETIME),1),
(CAST('20150103 00:49:59.000' as DATETIME),1),
(CAST('20150103 00:52:23.000' as DATETIME),1),
(CAST('20150103 00:54:15.000' as DATETIME),1),
(CAST('20150103 00:56:24.000' as DATETIME),2),
(CAST('20150103 00:58:09.000' as DATETIME),1),
(CAST('20150103 01:01:17.000' as DATETIME),2),
(CAST('20150103 01:03:11.000' as DATETIME),1),
(CAST('20150103 01:05:42.000' as DATETIME),2),
(CAST('20150103 01:07:37.000' as DATETIME),2),
(CAST('20150103 01:09:10.000' as DATETIME),2),
(CAST('20150103 01:10:48.000' as DATETIME),1),
(CAST('20150103 01:13:07.000' as DATETIME),2),
(CAST('20150103 01:14:50.000' as DATETIME),1),
(CAST('20150103 01:16:20.000' as DATETIME),2),
(CAST('20150103 01:19:08.000' as DATETIME),1),
(CAST('20150103 01:20:33.000' as DATETIME),1),
(CAST('20150103 01:23:20.000' as DATETIME),1),
(CAST('20150103 01:27:50.000' as DATETIME),2),
(CAST('20150103 01:30:40.000' as DATETIME),2),
(CAST('20150103 01:33:16.000' as DATETIME),1),
(CAST('20150103 01:37:00.000' as DATETIME),2),
(CAST('20150103 01:41:38.000' as DATETIME),2),
(CAST('20150103 01:43:40.000' as DATETIME),1),
(CAST('20150103 01:47:35.000' as DATETIME),2),
(CAST('20150103 01:50:17.000' as DATETIME),2),
(CAST('20150103 01:54:05.000' as DATETIME),2),
(CAST('20150103 01:56:58.000' as DATETIME),1),
(CAST('20150103 01:59:30.000' as DATETIME),2),
(CAST('20150103 02:01:14.000' as DATETIME),2),
(CAST('20150103 02:02:49.000' as DATETIME),2),
(CAST('20150103 02:08:05.000' as DATETIME),2),
(CAST('20150103 02:12:25.000' as DATETIME),1),
(CAST('20150103 02:15:31.000' as DATETIME),2),
(CAST('20150103 02:18:48.000' as DATETIME),3),
(CAST('20150103 02:22:31.000' as DATETIME),2),
(CAST('20150103 02:24:46.000' as DATETIME),2),
(CAST('20150103 02:26:56.000' as DATETIME),1),
(CAST('20150103 02:28:44.000' as DATETIME),1),
(CAST('20150103 02:30:32.000' as DATETIME),2),
(CAST('20150103 02:32:49.000' as DATETIME),2),
(CAST('20150103 02:34:31.000' as DATETIME),1),
(CAST('20150103 02:38:27.000' as DATETIME),1),
(CAST('20150103 02:41:16.000' as DATETIME),1),
(CAST('20150103 02:44:24.000' as DATETIME),1),
(CAST('20150103 02:46:42.000' as DATETIME),1),
(CAST('20150103 02:49:21.000' as DATETIME),2),
(CAST('20150103 02:51:56.000' as DATETIME),1),
(CAST('20150103 02:55:22.000' as DATETIME),2),
(CAST('20150103 02:57:33.000' as DATETIME),1),
(CAST('20150103 03:01:00.000' as DATETIME),2),
(CAST('20150103 18:01:09.000' as DATETIME),2),
(CAST('20150103 18:01:56.000' as DATETIME),1),
(CAST('20150103 18:03:04.000' as DATETIME),2),
(CAST('20150103 18:04:04.000' as DATETIME),1),
(CAST('20150103 18:05:05.000' as DATETIME),1),
(CAST('20150103 18:06:09.000' as DATETIME),1),
(CAST('20150103 18:07:09.000' as DATETIME),2),
(CAST('20150103 18:08:07.000' as DATETIME),2),
(CAST('20150103 18:09:06.000' as DATETIME),2),
(CAST('20150103 18:10:01.000' as DATETIME),2),
(CAST('20150103 18:11:06.000' as DATETIME),2),
(CAST('20150103 18:12:05.000' as DATETIME),1),
(CAST('20150103 18:13:07.000' as DATETIME),2),
(CAST('20150103 18:13:56.000' as DATETIME),1),
(CAST('20150103 18:14:54.000' as DATETIME),1),
(CAST('20150103 18:15:51.000' as DATETIME),1),
(CAST('20150103 18:16:54.000' as DATETIME),2),
(CAST('20150103 18:18:07.000' as DATETIME),2),
(CAST('20150103 18:19:06.000' as DATETIME),1),
(CAST('20150103 18:19:58.000' as DATETIME),1),
(CAST('20150103 18:22:01.000' as DATETIME),2),
(CAST('20150103 18:22:55.000' as DATETIME),1),
(CAST('20150103 18:23:57.000' as DATETIME),2),
(CAST('20150103 18:25:10.000' as DATETIME),1),
(CAST('20150103 18:26:16.000' as DATETIME),2),
(CAST('20150103 18:27:13.000' as DATETIME),1),
(CAST('20150103 18:28:03.000' as DATETIME),2),
(CAST('20150103 18:29:00.000' as DATETIME),1),
(CAST('20150103 18:29:51.000' as DATETIME),2),
(CAST('20150103 18:31:03.000' as DATETIME),2),
(CAST('20150103 18:32:09.000' as DATETIME),2),
(CAST('20150103 18:33:00.000' as DATETIME),1),
(CAST('20150103 18:33:47.000' as DATETIME),1),
(CAST('20150103 18:34:49.000' as DATETIME),1),
(CAST('20150103 18:35:36.000' as DATETIME),1),
(CAST('20150103 18:36:29.000' as DATETIME),2),
(CAST('20150103 18:37:49.000' as DATETIME),2),
(CAST('20150103 18:38:40.000' as DATETIME),1),
(CAST('20150103 18:39:31.000' as DATETIME),2),
(CAST('20150103 18:42:59.000' as DATETIME),2),
(CAST('20150103 18:46:50.000' as DATETIME),1),
(CAST('20150103 18:48:39.000' as DATETIME),1),
(CAST('20150103 18:50:25.000' as DATETIME),1),
(CAST('20150103 18:53:10.000' as DATETIME),1),
(CAST('20150103 18:55:09.000' as DATETIME),2),
(CAST('20150103 18:57:11.000' as DATETIME),1),
(CAST('20150103 19:00:05.000' as DATETIME),1),
(CAST('20150103 19:02:24.000' as DATETIME),1),
(CAST('20150103 19:03:55.000' as DATETIME),2),
(CAST('20150103 19:05:42.000' as DATETIME),2),
(CAST('20150103 19:09:06.000' as DATETIME),1),
(CAST('20150103 19:11:27.000' as DATETIME),2),
(CAST('20150103 19:13:34.000' as DATETIME),1),
(CAST('20150103 19:15:51.000' as DATETIME),1),
(CAST('20150103 19:19:25.000' as DATETIME),1),
(CAST('20150103 19:22:17.000' as DATETIME),2),
(CAST('20150103 19:24:03.000' as DATETIME),2),
(CAST('20150103 19:27:02.000' as DATETIME),1),
(CAST('20150103 19:28:41.000' as DATETIME),2),
(CAST('20150103 19:30:40.000' as DATETIME),1),
(CAST('20150103 19:32:54.000' as DATETIME),2),
(CAST('20150103 19:34:55.000' as DATETIME),2),
(CAST('20150103 19:36:26.000' as DATETIME),1),
(CAST('20150103 19:37:56.000' as DATETIME),2),
(CAST('20150103 19:39:25.000' as DATETIME),1),
(CAST('20150103 19:41:35.000' as DATETIME),2),
(CAST('20150103 19:45:00.000' as DATETIME),1),
(CAST('20150103 19:49:07.000' as DATETIME),1),
(CAST('20150103 19:51:46.000' as DATETIME),1),
(CAST('20150103 19:54:18.000' as DATETIME),1),
(CAST('20150103 19:58:35.000' as DATETIME),2),
(CAST('20150103 20:00:43.000' as DATETIME),2),
(CAST('20150103 20:03:40.000' as DATETIME),1),
(CAST('20150103 20:06:15.000' as DATETIME),2),
(CAST('20150103 20:07:25.000' as DATETIME),1),
(CAST('20150103 20:08:30.000' as DATETIME),1),
(CAST('20150103 20:09:33.000' as DATETIME),1),
(CAST('20150103 20:10:42.000' as DATETIME),1),
(CAST('20150103 20:11:57.000' as DATETIME),2),
(CAST('20150103 20:13:48.000' as DATETIME),1),
(CAST('20150103 20:14:54.000' as DATETIME),1),
(CAST('20150103 20:18:04.000' as DATETIME),1),
(CAST('20150103 20:20:44.000' as DATETIME),2),
(CAST('20150103 20:21:59.000' as DATETIME),2),
(CAST('20150103 20:23:20.000' as DATETIME),2),
(CAST('20150103 20:25:01.000' as DATETIME),1),
(CAST('20150103 20:26:21.000' as DATETIME),3),
(CAST('20150103 20:27:08.000' as DATETIME),2),
(CAST('20150103 20:29:09.000' as DATETIME),1),
(CAST('20150103 20:30:39.000' as DATETIME),2),
(CAST('20150103 20:31:51.000' as DATETIME),1),
(CAST('20150103 20:33:05.000' as DATETIME),1),
(CAST('20150103 20:34:27.000' as DATETIME),2),
(CAST('20150103 20:36:30.000' as DATETIME),1),
(CAST('20150103 20:39:36.000' as DATETIME),2),
(CAST('20150103 20:41:01.000' as DATETIME),2),
(CAST('20150103 20:41:54.000' as DATETIME),2),
(CAST('20150103 20:43:03.000' as DATETIME),1),
(CAST('20150103 20:44:22.000' as DATETIME),2),
(CAST('20150103 20:45:34.000' as DATETIME),1),
(CAST('20150103 20:46:44.000' as DATETIME),2),
(CAST('20150103 20:48:13.000' as DATETIME),1),
(CAST('20150103 20:49:40.000' as DATETIME),1),
(CAST('20150103 20:50:54.000' as DATETIME),1),
(CAST('20150103 20:51:29.000' as DATETIME),2),
(CAST('20150103 20:56:13.000' as DATETIME),1),
(CAST('20150103 20:58:04.000' as DATETIME),2),
(CAST('20150103 21:04:48.000' as DATETIME),2),
(CAST('20150103 21:07:12.000' as DATETIME),2),
(CAST('20150103 21:09:44.000' as DATETIME),2),
(CAST('20150103 21:11:53.000' as DATETIME),2),
(CAST('20150103 21:14:51.000' as DATETIME),1),
(CAST('20150103 21:19:23.000' as DATETIME),1),
(CAST('20150103 21:22:49.000' as DATETIME),1),
(CAST('20150103 21:26:39.000' as DATETIME),1),
(CAST('20150103 21:30:55.000' as DATETIME),1),
(CAST('20150103 21:35:42.000' as DATETIME),2),
(CAST('20150103 21:42:08.000' as DATETIME),1),
(CAST('20150103 21:45:28.000' as DATETIME),2),
(CAST('20150103 21:47:50.000' as DATETIME),2),
(CAST('20150103 21:49:08.000' as DATETIME),2),
(CAST('20150103 21:51:43.000' as DATETIME),1),
(CAST('20150103 21:53:58.000' as DATETIME),1),
(CAST('20150103 21:56:01.000' as DATETIME),3),
(CAST('20150103 21:57:46.000' as DATETIME),2),
(CAST('20150103 22:00:16.000' as DATETIME),1),
(CAST('20150103 22:02:24.000' as DATETIME),2),
(CAST('20150103 22:04:14.000' as DATETIME),2),
(CAST('20150103 22:05:42.000' as DATETIME),1),
(CAST('20150103 22:08:09.000' as DATETIME),2),
(CAST('20150103 22:11:34.000' as DATETIME),1),
(CAST('20150103 22:14:13.000' as DATETIME),2),
(CAST('20150103 22:16:51.000' as DATETIME),1),
(CAST('20150103 22:19:26.000' as DATETIME),1),
(CAST('20150103 22:22:55.000' as DATETIME),1),
(CAST('20150103 22:27:02.000' as DATETIME),2),
(CAST('20150103 22:29:58.000' as DATETIME),1),
(CAST('20150103 22:32:23.000' as DATETIME),2),
(CAST('20150103 22:38:42.000' as DATETIME),1),
(CAST('20150103 22:43:35.000' as DATETIME),3),
(CAST('20150103 22:50:55.000' as DATETIME),2),
(CAST('20150103 22:57:08.000' as DATETIME),1),
(CAST('20150103 22:59:32.000' as DATETIME),1),
(CAST('20150103 23:04:27.000' as DATETIME),2),
(CAST('20150103 23:07:30.000' as DATETIME),2),
(CAST('20150103 23:11:30.000' as DATETIME),2),
(CAST('20150103 23:14:22.000' as DATETIME),2),
(CAST('20150103 23:20:42.000' as DATETIME),1),
(CAST('20150103 23:25:54.000' as DATETIME),1),
(CAST('20150103 23:28:31.000' as DATETIME),2),
(CAST('20150103 23:34:26.000' as DATETIME),3),
(CAST('20150103 23:42:12.000' as DATETIME),1),
(CAST('20150103 23:46:45.000' as DATETIME),2),
(CAST('20150103 23:52:03.000' as DATETIME),1),
(CAST('20150103 23:55:38.000' as DATETIME),1),
(CAST('20150103 23:59:23.000' as DATETIME),1),
(CAST('20150104 00:02:49.000' as DATETIME),2),
(CAST('20150104 00:06:17.000' as DATETIME),2),
(CAST('20150104 00:09:55.000' as DATETIME),2),
(CAST('20150104 00:12:57.000' as DATETIME),1),
(CAST('20150104 00:17:37.000' as DATETIME),1),
(CAST('20150104 00:21:53.000' as DATETIME),2),
(CAST('20150104 00:25:04.000' as DATETIME),1),
(CAST('20150104 00:30:28.000' as DATETIME),2),
(CAST('20150104 00:36:19.000' as DATETIME),2),
(CAST('20150104 00:40:13.000' as DATETIME),1),
(CAST('20150104 00:46:21.000' as DATETIME),1),
(CAST('20150104 00:50:33.000' as DATETIME),2),
(CAST('20150104 00:58:53.000' as DATETIME),1),
(CAST('20150104 01:03:57.000' as DATETIME),1),
(CAST('20150104 01:07:22.000' as DATETIME),2),
(CAST('20150104 01:11:06.000' as DATETIME),2),
(CAST('20150104 01:15:44.000' as DATETIME),2),
(CAST('20150104 01:19:52.000' as DATETIME),2),
(CAST('20150104 01:24:46.000' as DATETIME),1),
(CAST('20150104 01:28:56.000' as DATETIME),2),
(CAST('20150104 01:32:31.000' as DATETIME),2),
(CAST('20150104 01:36:08.000' as DATETIME),1),
(CAST('20150104 01:39:23.000' as DATETIME),1),
(CAST('20150104 01:42:31.000' as DATETIME),2),
(CAST('20150104 01:44:57.000' as DATETIME),1),
(CAST('20150104 01:46:24.000' as DATETIME),2),
(CAST('20150104 01:49:47.000' as DATETIME),2),
(CAST('20150104 01:52:53.000' as DATETIME),1),
(CAST('20150104 01:55:39.000' as DATETIME),2),
(CAST('20150104 01:59:18.000' as DATETIME),2),
(CAST('20150104 02:04:22.000' as DATETIME),2),
(CAST('20150104 02:08:14.000' as DATETIME),2),
(CAST('20150104 02:12:23.000' as DATETIME),1),
(CAST('20150104 02:18:36.000' as DATETIME),1),
(CAST('20150104 02:21:19.000' as DATETIME),1),
(CAST('20150104 02:25:50.000' as DATETIME),1),
(CAST('20150104 02:31:54.000' as DATETIME),2),
(CAST('20150104 02:37:02.000' as DATETIME),1),
(CAST('20150104 02:39:44.000' as DATETIME),2),
(CAST('20150104 02:44:09.000' as DATETIME),1),
(CAST('20150104 02:46:38.000' as DATETIME),2),
(CAST('20150104 02:51:32.000' as DATETIME),2),
(CAST('20150104 02:54:33.000' as DATETIME),1),
(CAST('20150104 03:01:03.000' as DATETIME),1),
(CAST('20150104 17:58:09.000' as DATETIME),2),
(CAST('20150104 17:59:23.000' as DATETIME),2),
(CAST('20150104 18:01:16.000' as DATETIME),1),
(CAST('20150104 18:03:07.000' as DATETIME),1),
(CAST('20150104 18:04:54.000' as DATETIME),1),
(CAST('20150104 18:07:52.000' as DATETIME),3),
(CAST('20150104 18:09:43.000' as DATETIME),2),
(CAST('20150104 18:11:25.000' as DATETIME),2),
(CAST('20150104 18:14:34.000' as DATETIME),3),
(CAST('20150104 18:16:48.000' as DATETIME),3),
(CAST('20150104 18:19:56.000' as DATETIME),1),
(CAST('20150104 18:22:38.000' as DATETIME),2),
(CAST('20150104 18:24:41.000' as DATETIME),2),
(CAST('20150104 18:28:42.000' as DATETIME),2),
(CAST('20150104 18:32:24.000' as DATETIME),2),
(CAST('20150104 18:35:40.000' as DATETIME),1),
(CAST('20150104 18:39:43.000' as DATETIME),1),
(CAST('20150104 18:44:05.000' as DATETIME),1),
(CAST('20150104 18:48:34.000' as DATETIME),2),
(CAST('20150104 18:53:35.000' as DATETIME),1),
(CAST('20150104 18:59:32.000' as DATETIME),2),
(CAST('20150104 19:03:34.000' as DATETIME),1),
(CAST('20150104 19:07:55.000' as DATETIME),2),
(CAST('20150104 19:11:56.000' as DATETIME),2),
(CAST('20150104 19:14:34.000' as DATETIME),2),
(CAST('20150104 19:16:59.000' as DATETIME),1),
(CAST('20150104 19:19:02.000' as DATETIME),2),
(CAST('20150104 19:21:24.000' as DATETIME),2),
(CAST('20150104 19:23:21.000' as DATETIME),2),
(CAST('20150104 19:26:57.000' as DATETIME),1),
(CAST('20150104 19:29:14.000' as DATETIME),2),
(CAST('20150104 19:32:06.000' as DATETIME),1),
(CAST('20150104 19:35:39.000' as DATETIME),1),
(CAST('20150104 19:38:49.000' as DATETIME),2),
(CAST('20150104 19:41:09.000' as DATETIME),1),
(CAST('20150104 19:43:56.000' as DATETIME),2),
(CAST('20150104 19:45:39.000' as DATETIME),1),
(CAST('20150104 19:48:36.000' as DATETIME),3),
(CAST('20150104 19:50:59.000' as DATETIME),1),
(CAST('20150104 19:52:46.000' as DATETIME),2),
(CAST('20150104 19:54:24.000' as DATETIME),2),
(CAST('20150104 19:56:44.000' as DATETIME),2),
(CAST('20150104 19:59:08.000' as DATETIME),1),
(CAST('20150104 20:02:59.000' as DATETIME),2),
(CAST('20150104 20:07:32.000' as DATETIME),1),
(CAST('20150104 20:10:35.000' as DATETIME),2),
(CAST('20150104 20:13:35.000' as DATETIME),2),
(CAST('20150104 20:16:27.000' as DATETIME),1),
(CAST('20150104 20:19:39.000' as DATETIME),1),
(CAST('20150104 20:22:05.000' as DATETIME),2),
(CAST('20150104 20:25:01.000' as DATETIME),1),
(CAST('20150104 20:27:30.000' as DATETIME),1),
(CAST('20150104 20:29:53.000' as DATETIME),2),
(CAST('20150104 20:32:13.000' as DATETIME),2),
(CAST('20150104 20:34:28.000' as DATETIME),1),
(CAST('20150104 20:37:07.000' as DATETIME),2),
(CAST('20150104 20:39:09.000' as DATETIME),2),
(CAST('20150104 20:40:46.000' as DATETIME),2),
(CAST('20150104 20:42:32.000' as DATETIME),2),
(CAST('20150104 20:44:08.000' as DATETIME),2),
(CAST('20150104 20:45:24.000' as DATETIME),1),
(CAST('20150104 20:46:26.000' as DATETIME),1),
(CAST('20150104 20:47:32.000' as DATETIME),2),
(CAST('20150104 20:48:55.000' as DATETIME),1),
(CAST('20150104 20:49:41.000' as DATETIME),2),
(CAST('20150104 20:50:17.000' as DATETIME),2),
(CAST('20150104 20:52:04.000' as DATETIME),2),
(CAST('20150104 20:53:29.000' as DATETIME),1),
(CAST('20150104 20:54:42.000' as DATETIME),2),
(CAST('20150104 20:55:55.000' as DATETIME),1),
(CAST('20150104 20:57:23.000' as DATETIME),2),
(CAST('20150104 20:59:12.000' as DATETIME),2),
(CAST('20150104 21:00:03.000' as DATETIME),2),
(CAST('20150104 21:01:05.000' as DATETIME),1),
(CAST('20150104 21:08:24.000' as DATETIME),2),
(CAST('20150104 21:09:08.000' as DATETIME),2),
(CAST('20150104 21:10:07.000' as DATETIME),1),
(CAST('20150104 21:11:02.000' as DATETIME),1),
(CAST('20150104 21:12:11.000' as DATETIME),2),
(CAST('20150104 21:13:11.000' as DATETIME),2),
(CAST('20150104 21:14:16.000' as DATETIME),1),
(CAST('20150104 21:15:20.000' as DATETIME),2),
(CAST('20150104 21:16:35.000' as DATETIME),1),
(CAST('20150104 21:18:58.000' as DATETIME),2),
(CAST('20150104 21:22:27.000' as DATETIME),1),
(CAST('20150104 21:25:04.000' as DATETIME),1),
(CAST('20150104 21:27:34.000' as DATETIME),2),
(CAST('20150104 21:30:38.000' as DATETIME),2),
(CAST('20150104 21:34:11.000' as DATETIME),1),
(CAST('20150104 21:36:45.000' as DATETIME),1),
(CAST('20150104 21:39:26.000' as DATETIME),1),
(CAST('20150104 21:41:56.000' as DATETIME),1),
(CAST('20150104 21:44:21.000' as DATETIME),2),
(CAST('20150104 21:46:59.000' as DATETIME),1),
(CAST('20150104 21:48:47.000' as DATETIME),2),
(CAST('20150104 21:50:19.000' as DATETIME),2),
(CAST('20150104 21:52:02.000' as DATETIME),1),
(CAST('20150104 21:53:34.000' as DATETIME),1),
(CAST('20150104 21:55:39.000' as DATETIME),2),
(CAST('20150104 21:57:11.000' as DATETIME),2),
(CAST('20150104 21:58:21.000' as DATETIME),1),
(CAST('20150104 21:59:41.000' as DATETIME),1),
(CAST('20150104 22:01:29.000' as DATETIME),2),
(CAST('20150104 22:02:56.000' as DATETIME),2),
(CAST('20150104 22:04:09.000' as DATETIME),1),
(CAST('20150104 22:05:13.000' as DATETIME),2),
(CAST('20150104 22:06:37.000' as DATETIME),2),
(CAST('20150104 22:08:43.000' as DATETIME),1),
(CAST('20150104 22:09:35.000' as DATETIME),2),
(CAST('20150104 22:10:55.000' as DATETIME),2),
(CAST('20150104 22:11:45.000' as DATETIME),2),
(CAST('20150104 22:12:27.000' as DATETIME),2),
(CAST('20150104 22:13:13.000' as DATETIME),1),
(CAST('20150104 22:14:03.000' as DATETIME),2),
(CAST('20150104 22:14:43.000' as DATETIME),2),
(CAST('20150104 22:15:32.000' as DATETIME),2),
(CAST('20150104 22:16:22.000' as DATETIME),2),
(CAST('20150104 22:17:15.000' as DATETIME),3),
(CAST('20150104 22:18:10.000' as DATETIME),2),
(CAST('20150104 22:18:57.000' as DATETIME),1),
(CAST('20150104 22:19:55.000' as DATETIME),1),
(CAST('20150104 22:20:43.000' as DATETIME),2),
(CAST('20150104 22:26:04.000' as DATETIME),1),
(CAST('20150104 22:28:08.000' as DATETIME),2),
(CAST('20150104 22:30:15.000' as DATETIME),1),
(CAST('20150104 22:31:49.000' as DATETIME),2),
(CAST('20150104 22:33:50.000' as DATETIME),1),
(CAST('20150104 22:35:52.000' as DATETIME),1),
(CAST('20150104 22:37:13.000' as DATETIME),2),
(CAST('20150104 22:38:38.000' as DATETIME),1),
(CAST('20150104 22:40:35.000' as DATETIME),2),
(CAST('20150104 22:42:15.000' as DATETIME),2),
(CAST('20150104 22:43:24.000' as DATETIME),1),
(CAST('20150104 22:44:28.000' as DATETIME),1),
(CAST('20150104 22:45:18.000' as DATETIME),2),
(CAST('20150104 22:46:07.000' as DATETIME),1),
(CAST('20150104 22:46:55.000' as DATETIME),1),
(CAST('20150104 22:47:41.000' as DATETIME),1),
(CAST('20150104 22:48:58.000' as DATETIME),1),
(CAST('20150104 22:49:48.000' as DATETIME),1),
(CAST('20150104 22:50:30.000' as DATETIME),1),
(CAST('20150104 22:51:19.000' as DATETIME),1),
(CAST('20150104 22:52:13.000' as DATETIME),2),
(CAST('20150104 22:53:00.000' as DATETIME),1),
(CAST('20150104 22:53:49.000' as DATETIME),2),
(CAST('20150104 22:54:32.000' as DATETIME),1),
(CAST('20150104 22:55:28.000' as DATETIME),2),
(CAST('20150104 22:56:14.000' as DATETIME),1),
(CAST('20150104 22:57:01.000' as DATETIME),1),
(CAST('20150104 22:57:46.000' as DATETIME),2),
(CAST('20150104 22:58:29.000' as DATETIME),1),
(CAST('20150104 22:59:14.000' as DATETIME),1),
(CAST('20150104 23:00:18.000' as DATETIME),1),
(CAST('20150104 23:01:37.000' as DATETIME),1),
(CAST('20150104 23:02:23.000' as DATETIME),2),
(CAST('20150104 23:03:15.000' as DATETIME),1),
(CAST('20150104 23:04:14.000' as DATETIME),1),
(CAST('20150104 23:05:10.000' as DATETIME),2),
(CAST('20150104 23:06:22.000' as DATETIME),1),
(CAST('20150104 23:07:18.000' as DATETIME),2),
(CAST('20150104 23:08:17.000' as DATETIME),1),
(CAST('20150104 23:09:27.000' as DATETIME),1),
(CAST('20150104 23:10:27.000' as DATETIME),1),
(CAST('20150104 23:11:36.000' as DATETIME),1),
(CAST('20150104 23:12:40.000' as DATETIME),1),
(CAST('20150104 23:13:47.000' as DATETIME),1),
(CAST('20150104 23:14:48.000' as DATETIME),1),
(CAST('20150104 23:15:47.000' as DATETIME),2),
(CAST('20150104 23:17:11.000' as DATETIME),1),
(CAST('20150104 23:18:35.000' as DATETIME),2),
(CAST('20150104 23:20:22.000' as DATETIME),2),
(CAST('20150104 23:22:18.000' as DATETIME),1),
(CAST('20150104 23:23:40.000' as DATETIME),1),
(CAST('20150104 23:25:12.000' as DATETIME),2),
(CAST('20150104 23:27:25.000' as DATETIME),2),
(CAST('20150104 23:30:33.000' as DATETIME),2),
(CAST('20150104 23:32:22.000' as DATETIME),1),
(CAST('20150104 23:33:50.000' as DATETIME),1),
(CAST('20150104 23:36:20.000' as DATETIME),1),
(CAST('20150104 23:37:58.000' as DATETIME),1),
(CAST('20150104 23:39:06.000' as DATETIME),3),
(CAST('20150104 23:40:09.000' as DATETIME),2),
(CAST('20150104 23:40:53.000' as DATETIME),2),
(CAST('20150104 23:41:33.000' as DATETIME),2),
(CAST('20150104 23:42:21.000' as DATETIME),2),
(CAST('20150104 23:43:13.000' as DATETIME),1),
(CAST('20150104 23:43:55.000' as DATETIME),2),
(CAST('20150104 23:44:42.000' as DATETIME),2),
(CAST('20150104 23:45:30.000' as DATETIME),1),
(CAST('20150104 23:46:22.000' as DATETIME),1),
(CAST('20150104 23:48:15.000' as DATETIME),1),
(CAST('20150104 23:52:19.000' as DATETIME),1),
(CAST('20150104 23:53:18.000' as DATETIME),1),
(CAST('20150104 23:54:18.000' as DATETIME),1),
(CAST('20150104 23:55:43.000' as DATETIME),1),
(CAST('20150104 23:57:29.000' as DATETIME),1),
(CAST('20150104 23:58:24.000' as DATETIME),2),
(CAST('20150105 00:00:05.000' as DATETIME),2),
(CAST('20150105 00:01:16.000' as DATETIME),2),
(CAST('20150105 00:02:39.000' as DATETIME),2),
(CAST('20150105 00:04:08.000' as DATETIME),2),
(CAST('20150105 00:05:07.000' as DATETIME),2),
(CAST('20150105 00:06:50.000' as DATETIME),2),
(CAST('20150105 00:09:10.000' as DATETIME),2),
(CAST('20150105 00:10:27.000' as DATETIME),2),
(CAST('20150105 00:11:52.000' as DATETIME),2),
(CAST('20150105 00:13:26.000' as DATETIME),2),
(CAST('20150105 00:15:33.000' as DATETIME),1),
(CAST('20150105 00:17:24.000' as DATETIME),2),
(CAST('20150105 00:18:57.000' as DATETIME),2),
(CAST('20150105 00:20:09.000' as DATETIME),3),
(CAST('20150105 00:21:53.000' as DATETIME),2),
(CAST('20150105 00:24:11.000' as DATETIME),1),
(CAST('20150105 00:27:20.000' as DATETIME),1),
(CAST('20150105 00:28:52.000' as DATETIME),2),
(CAST('20150105 00:30:27.000' as DATETIME),3),
(CAST('20150105 00:31:47.000' as DATETIME),2),
(CAST('20150105 00:33:05.000' as DATETIME),3),
(CAST('20150105 00:34:36.000' as DATETIME),2),
(CAST('20150105 00:35:53.000' as DATETIME),2),
(CAST('20150105 00:36:48.000' as DATETIME),1),
(CAST('20150105 00:37:36.000' as DATETIME),2),
(CAST('20150105 00:38:36.000' as DATETIME),2),
(CAST('20150105 00:39:29.000' as DATETIME),1),
(CAST('20150105 00:40:19.000' as DATETIME),1),
(CAST('20150105 00:41:06.000' as DATETIME),2),
(CAST('20150105 00:41:42.000' as DATETIME),2),
(CAST('20150105 00:42:26.000' as DATETIME),2),
(CAST('20150105 00:43:10.000' as DATETIME),2),
(CAST('20150105 00:43:55.000' as DATETIME),1),
(CAST('20150105 00:44:41.000' as DATETIME),1),
(CAST('20150105 00:45:33.000' as DATETIME),1),
(CAST('20150105 00:46:24.000' as DATETIME),1),
(CAST('20150105 00:47:09.000' as DATETIME),2),
(CAST('20150105 00:47:58.000' as DATETIME),2),
(CAST('20150105 00:48:58.000' as DATETIME),2),
(CAST('20150105 00:50:02.000' as DATETIME),2),
(CAST('20150105 00:50:46.000' as DATETIME),2),
(CAST('20150105 00:51:37.000' as DATETIME),2),
(CAST('20150105 00:52:22.000' as DATETIME),2),
(CAST('20150105 00:53:13.000' as DATETIME),1),
(CAST('20150105 00:54:17.000' as DATETIME),1),
(CAST('20150105 00:55:01.000' as DATETIME),2),
(CAST('20150105 00:55:41.000' as DATETIME),2),
(CAST('20150105 00:56:28.000' as DATETIME),2))
select * from #tab1 t;
September 16, 2015 at 10:13 am
Poor performance :angry:
But....
WITH
cte1 (Dt,Item,RowNo) AS (
SELECT Dt,Item,ROW_NUMBER() OVER (ORDER BY Dt ASC)
FROM #tab1
),
cte2 (RowNo) AS (
--SELECT 1
--UNION ALL
SELECT a.RowNo
FROM cte1 a
JOIN cte1 b ON b.RowNo = a.RowNo+1 AND b.Item <> a.Item
),
cte3 (RowNo,Grp) AS (
SELECT RowNo,ROW_NUMBER() OVER (ORDER BY RowNo ASC)
FROM cte2
)
SELECT cte1.Dt,cte1.Item,a.Grp
FROM cte3 a
LEFT JOIN cte3 b ON b.Grp = a.Grp-1
JOIN cte1 ON cte1.RowNo BETWEEN ISNULL(b.RowNo,1) AND a.RowNo
WHERE a.RowNo - ISNULL(b.RowNo,1) + 1 >= 5
ORDER BY a.Grp,cte1.Dt ASC;
Note that I tested this on SQL2008R2, using LEAD/LAG in SQL2012 may improve this
Far away is close at hand in the images of elsewhere.
Anon.
September 18, 2015 at 1:59 am
Hello David,
thanks for the script but the performance is truly poor...
I decided do create a dynamic SQL Script :pinch: with, as you mentioned, LEAD() and LAG().
This performs very well.
If anyone's interested I can post the finished script...
Thanks
September 18, 2015 at 3:37 am
Please post that script here, alongside some sample data pls. I'm at wits end to understand what you're looking for given the sample data you provided earlier.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply