, Guest>> || || |
/ Microsoft SQL Server
  ms sql2008  [new]
XLiMeR
Member

:
: 11
. .
:
addresstime
. 21 2011-06-21 01:11:08.000
. 21 2011-06-21 01:14:50.000
. 21 2011-06-21 01:27:40.000
. 21 2011-06-21 04:02:36.000
. 21 2011-06-21 04:21:44.000
. 2 2011-06-21 07:43:34.000
. 2 2011-06-21 08:00:10.000
. 60 2011-06-21 12:22:24.000
. 60 2011-06-21 12:23:32.000
. 60 2011-06-21 12:31:22.000
. 15 2011-06-21 12:38:02.000

:
addresstime_starttime_end raznost
. 21 2011-06-21 01:11:08.000 2011-06-21 01:27:40.000 00:16:32.000
. 21 2011-06-21 04:02:36.000 2011-06-21 04:21:44.000 00:19:08.000
. 2 2011-06-21 07:43:34.000 2011-06-21 08:00:10.000 00:16:36.000
. 60 2011-06-21 12:22:24.000 2011-06-21 12:31:22.000 00:08:58.000
. 15 2011-06-21 12:38:02.000 2011-06-21 12:38:02.000 00:00:00.000


.. ,
1) 20, . , , , " . 21"
2) . time_start - , time_end - , raznost - 20 .

. . ., , 20 . .
5 11, 17:26[10926857]      |
 Re: ms sql2008  [new]
iiyama
Member

:
: 642
,
1) - DATEDIFF
2) LEFT/RIGHT JOIN
5 11, 17:50[10927052]      |
 Re: ms sql2008  [new]

Guest
select adr, tim tim1, tim20 tim2, tim20-tim int12
from (
   select adr, tim
      , count(*)         over(partition by adr order by tim      range interval '20' minute preceding) cnt
      , first_value(tim) over(partition by adr order by tim desc range interval '20' minute preceding) tim20
   from t
)
where cnt = 1;

ADR                      TIM1                      TIM2                      INT12       
------------------------ ------------------------- ------------------------- ----------- 
 .  15    21.06.2011 12:38:02,00000 21.06.2011 12:38:02,00000 0 0:0:0.0   
 .  21      21.06.2011 01:11:08,00000 21.06.2011 01:27:40,00000 0 0:16:32.0 
 .  21      21.06.2011 04:02:36,00000 21.06.2011 04:21:44,00000 0 0:19:8.0  
  .  2 21.06.2011 07:43:34,00000 21.06.2011 08:00:10,00000 0 0:16:36.0 
 .  60  21.06.2011 12:22:24,00000 21.06.2011 12:31:22,00000 0 0:8:58.0  
5 11, 20:28[10927882]      |
 Re: ms sql2008  [new]
pkarklin
Member

: ()
: 74930
,

...
5 11, 21:32[10928035]      |
 Re: ms sql2008  [new]
XLiMeR
Member

:
: 11
select adr, tim tim1, tim20 tim2, tim20-tim int12
from (
   select adr, tim
      , count(*)         over(partition by adr order by tim      range interval '20' minute preceding) cnt
      , first_value(tim) over(partition by adr order by tim desc range interval '20' minute preceding) tim20
   from t
)
where cnt = 1;
  

, "Incorrect syntax near 'Order'."

over(partition by adr order by tim range interval '20' minute preceding)
7 11, 14:24[10939092]      |
 Re: ms sql2008  [new]
Glory
Member

:
: 104751
XLiMeR

over(partition by adr order by tim range interval '20' minute preceding)

PL/SQL Oracle
7 11, 14:29[10939150]      |
 Re: ms sql2008  [new]
Anddros
Member

:
: 1077
declare @t table (address varchar(100), time datetime, primary key(address,time)) 
insert @t values
(' .  21','2011-06-21 01:11:08.000'), 
(' .  21','2011-06-21 01:14:50.000'), 
(' .  21','2011-06-21 01:27:40.000'),
(' .  21','2011-06-21 04:02:36.000'), 
(' .  21','2011-06-21 04:21:44.000'), 
('  .  2','2011-06-21 07:43:34.000'), 
('  .  2','2011-06-21 08:00:10.000'), 
(' .  60','2011-06-21 12:22:24.000'), 
(' .  60','2011-06-21 12:23:32.000'), 
(' .  60','2011-06-21 12:31:22.000'), 
(' .  15','2011-06-21 12:38:02.000') 

;with q as (select address, time, row_number()over(partition by address order by time)rn from @t),
p as (select address a, time time_start, time time_end, rn r from q where rn=1
union all
select a, case when datediff(ss,time_end,time)<=1200 then time_start else time end, time, rn
from p 
inner join q on a=address and r+1=rn
)
select a,time_start,max(time_end)time_end,convert(varchar(20),dateadd(ms,datediff(ms,time_start,max(time_end)),0),114) 
from p
group by a,time_start
7 11, 15:26[10939702]      |
 Re: ms sql2008  [new]
Anddros
Member

:
: 1077
. , . .
declare @t table (address varchar(100), time datetime, primary key(address,time)) 
insert @t values
(' .  21','2011-06-21 01:11:08.000'), 
(' .  21','2011-06-21 01:14:50.000'), 
(' .  21','2011-06-21 01:27:40.000'),
(' .  21','2011-06-21 04:02:36.000'), 
(' .  21','2011-06-21 04:21:44.000'), 
('  .  2','2011-06-21 07:43:34.000'), 
('  .  2','2011-06-21 08:00:10.000'), 
(' .  60','2011-06-21 12:22:24.000'), 
(' .  60','2011-06-21 12:23:32.000'), 
(' .  60','2011-06-21 12:31:22.000'), 
(' .  15','2011-06-21 12:38:02.000') 

select address, min(t2),max(t2),convert(varchar(20),dateadd(ms,datediff(ms,min(t2),max(t2)),0),114)  
from (
select address, max(time)t2, rn+n r1, datediff(ss,min(time), max(time))d,
row_number()over(partition by address, 
case when datediff(ss,min(time), max(time)) between 1 and 1200 then 0 else 1 end order by rn+n)r2
from (select address, time, row_number()over(partition by address order by time)rn from @t)t
cross join (select 0n union all select 1)n
group by address,rn+n
having max(case when n=0 then time end)is not null 
)t
group by address,case when d between 1 and 1200 then r1-r2 else r2 end
7 11, 16:09[10940019]      |
 Re: ms sql2008  [new]
Anddros
Member

:
: 1077
:
declare @t table (address varchar(100), time datetime, primary key(address,time)) 
insert @t values
(' .  21','2011-06-21 01:11:08.000'), 
(' .  21','2011-06-21 01:14:50.000'), 
(' .  21','2011-06-21 01:27:40.000'),
(' .  21','2011-06-21 03:25:40.000'),
(' .  21','2011-06-21 03:27:40.000'),
(' .  21','2011-06-21 03:28:40.000'),
(' .  21','2011-06-21 04:02:36.000'), 
(' .  21','2011-06-21 04:21:44.000'), 
('  .  2','2011-06-21 07:43:34.000'), 
('  .  2','2011-06-21 08:00:10.000'), 
(' .  60','2011-06-21 12:22:24.000'), 
(' .  60','2011-06-21 12:23:32.000'), 
(' .  60','2011-06-21 12:31:22.000'), 
(' .  15','2011-06-21 12:38:02.000') 

select address, min(t1), t2, convert(varchar(20),dateadd(ms,datediff(ms,min(t1),t2),0),114)  
from (
select t1.address, t1.time t1, min(t2.time) t2
from @t t1
inner join @t t2 on t1.address = t2.address and t1.time<=t2.time
where not exists(select 1 from @t t3 where t2.address = t3.address and t2.time<t3.time and dateadd(mi,20,t2.time)>t3.time)
group by t1.address, t1.time
)t 
group by address,t2
7 11, 16:11[10940044]      |
 Re: ms sql2008  [new]
XLiMeR
Member

:
: 11
Anddros,

, , 2 , !
, NULL,
- " ".

, , , . , , ,, 307( ), 307 115, ..
, . 115. ..
:
:
eventidaddresstime
115 ., 26 2011-06-14 09:38:48.000
307 ., 26 2011-06-14 09:38:48.000
115 ., 26 2011-06-14 09:39:58.000
115 ., 26 2011-06-14 09:42:04.000
307 ., 26 2011-06-14 09:42:04.000
115 ., 26 2011-06-14 09:43:04.000
115 ., 26 2011-06-14 09:47:12.000
307 ., 26 2011-06-14 09:47:12.000
115 ., 26 2011-06-14 09:48:34.000
115 ., 262011-06-14 09:59:00.000
115 ., 26 2011-06-14 09:59:20.000
115 ., 822011-06-14 13:03:36.000
115 ., 822011-06-14 13:04:24.000
115 ., 822011-06-14 13:07:04.000
307 ., 822011-06-14 13:07:04.000
115 ., 822011-06-14 13:18:26.000
115 ., 822011-06-14 13:19:46.000

:
addresstime_starttime_endraznost
., 26 2011-06-14 09:38:48.000 2011-06-14 09:48:34.000 00:09:46.000
., 82 2011-06-14 13:07:04.000 2011-06-14 13:18:26.000 00:11:22.000

?
9 11, 15:51[10950328]      |
/ Microsoft SQL Server