原题见:http://bbs.51cto.com/viewthread.php?tid=810424&page=1
超时的定义:连接行驶4个小时休息时间少于20分钟算超时驾驶(这个20分钟是在4个小时中停车时间的累加,还有个条件是停车时间小于5分钟也算他在行驶不算休息)
现在要得到的结果是超时的次数,以及车牌,开始时间,结束时间,连续行驶时间
create table TB_VDRDATA
(
  vehicleid varchar(16),--------车牌
  carspeed int,---------车速
  date datetime,----------时间
  bcms float------------保持秒数
)
由于提供的测试数据有问题,重新提供如下:

  1. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   50  ,   '2011-01-13 11:19:59.000'   ,   3   )  
  2. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   63  ,   '2011-01-13 11:20:02.000'   ,   5   )  
  3. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   60  ,   '2011-01-13 11:20:07.000'   ,   7   )  
  4. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   61  ,   '2011-01-13 11:20:14.000'   ,   5   )  
  5. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   62  ,   '2011-01-13 11:20:19.000'   ,   9   )  
  6. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   63  ,   '2011-01-13 11:20:28.000'   ,   5   )  
  7. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:20:33.000'   ,   100 )  
  8. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:22:13.000'   ,   100 )  
  9. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:23:53.000'   ,   80  )  
  10. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   20  ,   '2011-01-13 11:25:13.000'   ,   7   )  
  11. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   30  ,   '2011-01-13 11:25:20.000'   ,   5   )  
  12. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   40  ,   '2011-01-13 11:25:25.000'   ,   5   )  
  13. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:25:30.000'   ,   100 )  
  14. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:27:10.000'   ,   100 )  
  15. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:28:50.000'   ,   100 )  
  16. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   0   ,   '2011-01-13 11:30:30.000'   ,   100 )  
  17. insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values'陕ADF191'   ,   9   ,   '2011-01-13 11:32:10.000'   ,   5   ) 

问题处理思路
1.将所有连续休息的开始时间求出:时间都是连续记录的,任何一个开始时间前面要不没有记录,要不就是速度>0的记录
2.将所有连续休息的结束时间求出:这个和上面类似,任何一个结束时间后面要不没有记录,要不就是速度>0的记录
3.合并1,2生成的表,形成一个完成的开始时间到结束时间表,这里可以用日期差来得到休息时间。
4,将时间差小于5分钟的记录找出来,用来标示原来表中的这部分记录(最好加个字段)
5.类似123步骤,可以得到驾驶时间表(开始时间,结束时间),注意将第四步产生的记录也视为驾驶时间,这样得到的记录时间差大于4个小时的就是超时驾驶了。

具体的SQL:

  1. --本计算主要是表示一个计算过程,没有对查询做过多的优化。  
  2. --连续休息时间计算  
  3. with  b1 as 
  4. (  
  5.     select vehicleid,carspeed,date as bdate,bcms   
  6.     from TB_VDRDATA f  
  7.     where  carspeed=0 and date not in (select DATEADD(ss,bcms,datefrom TB_VDRDATA where carspeed=0)  
  8. ),e1 as   
  9. (  
  10.     select vehicleid,carspeed,DATEADD(ss,bcms,dateas edate,bcms   
  11.     from TB_VDRDATA f  
  12.     where  carspeed=0 and DATEADD(ss,bcms,datenot in (select date from TB_VDRDATA where carspeed=0)  
  13. ),c1 as--连续休息时间 单位秒 剔除小于5分钟300秒  
  14. (  
  15.     select vehicleid,carspeed,bdate,bcms,  
  16.     (select MIN(edate) from e1 where edate>b1.bdate) edate,DATEDIFF(ss,bdate,isnull((select MIN(edate) from e1 where edate>b1.bdate),bdate)) as ss  
  17.     from b1   
  18.     where DATEDIFF(ss,bdate,isnull((select MIN(edate) from e1 where edate>b1.bdate),bdate))<300  
  19. ),c2 as --连续休息小于20分钟(1200秒)(已经剔除300秒内)  
  20. (   
  21.     select * from c1 where ss<1200  
  22. ),  
  23. --连续驾驶时间计算  
  24. --计算过程中对于休息时间在20分钟内的也算作一次连续驾驶  
  25. as 
  26. (  
  27.     select f.vehicleid,f.carspeed,f.date as bdate,f.bcms   
  28.     from TB_VDRDATA f  
  29.     where  f.carspeed>0 and f.date not in   
  30.         (select DATEADD(ss,tb.bcms,datefrom TB_VDRDATA tb,c2 c1   
  31.         where tb.vehicleid=c1.vehicleid and (tb.carspeed>0   
  32.         or (tb.date >=c1.bdate and tb.date<=c1.edate)))  
  33. ),e as   
  34. (  
  35.     select f.vehicleid,f.carspeed,DATEADD(ss,f.bcms,f.dateas edate,f.bcms  
  36.     from TB_VDRDATA f  
  37.     where  f.carspeed>0 and DATEADD(ss,f.bcms,f.datenot in   
  38.         (select tb.date from TB_VDRDATA tb,c2 c1   
  39.         where tb.vehicleid=c1.vehicleid and (tb.carspeed>0   
  40.         or (tb.date >=c1.bdate and tb.date<=c1.edate)))  
  41. ),c as--连续驾驶时间  
  42. (  
  43.     select vehicleid,carspeed,bdate,bcms,  
  44.     isnull((select MIN(edate) from e where edate>b.bdate),bdate) as edate,DATEDIFF(ss,bdate,isnull((select MIN(edate) from e where edate>b.bdate),bdate)) as ss  
  45.     from b  
  46. )  
  47. --连续驾驶时间大于4小时(60*60*4=14400秒),并且休息时间少于20分钟的记录  
  48. --即连续行驶时间大于4小时中包含休息时间多余5分钟而小于20分钟的记录  
  49. --考虑到测试数据没有这么多的时间跨度,这个阀值我们假定为300秒  
  50. select c.vehicleid,c.bdate,c.edate,c.ss   
  51. from c,c2   
  52. where c.vehicleid=c2.vehicleid and c.ss>300 and c.bdate<c2.bdate and c.edate>c2.edate