火灾疏散实时信息系统（演习）

1.用户输入火灾开始时间FireBeginTime，火灾完成时间FireEndTime，逃生统计间隔时间TimeInterval （以秒为单位）

2.report显示累计时间逃生人数（例如：30秒内，60秒内，90秒内....）

3.FireEndTime 若没填，以数据中最后一位逃生时间为准

存储过程：

if exists(select * from sys.objects where name='fire_drill_report')
drop procedure fire_drill_report
GO
create procedure fire_drill_report
 @BeginDate varchar(30),
 @EndDate varchar(30),
 @TimeInterval int
as
while(1=1)
begin
with system_log_1 as
(
 select 
   APPLICATION_ID
  ,SITE
  ,DATE
 FROM SYSTEM_LOG WHERE application_id='VERAPP0005' and Date>=@BeginDate and Date<=@EndDate
),
 group_date as (
select T.groupid,count(T.application_id) as number from
(
 select 
  o.application_id
 ,o.Date
,((datename(hh,o.Date)*60*60+datename(mi,o.Date)*60+datename(ss,o.Date))-(datename(hh,@BeginDate)*60*60+datename(mi,@BeginDate)*60+datename(ss,@BeginDate)))/@TimeInterval as groupid 
  from system_log_1 as o 
) as T
group by T.groupid
--order by groupid
)
select t1.groupid,t1.number,sum(t2.number) as sumNumber
from group_date t1
inner join group_date t2 on t1.groupid>=t2.groupid
group by t1.groupid,t1.number
order by t1.groupid
break
end

---测试

exec fire_drill_report @BeginDate='2015-03-02 10:48:45.000',@EndDate='2015-03-02 23:59:59.000',@TimeInterval=30