纪念一下，我又开始使用数据库了，拿出数据结账

1.拿出已处理故障单、未处理故障单、满足两小时未到达及4小时未响应

2.分成九组。

3.注意null不能进行算数运算，要用isnull筛除

/*
demand for one of them except applied extensions for faultTicket
  (1.arrivetime more than two hours 2.closetime more than four hours)
*/

/*  1.tackled ticket*/
drop table z01
select FaultBillNo,xqmc,Remark,Type,Address,AddressCode,CreateTime,ArriveTime,CloseTime 
into z01
from T_FaultBill a where (a.CloseTime-a.CreateTime>4 or a.ArriveTime-a.CreateTime>2 or a.RespondTime-a.CreateTime>0.30 ) 
and a.CreateTime between '20190301' and '20190401' 
and a.Delay3Time is null and a.Delay3ingTime is null 
and a.Delay7Time is null and a.Delay7ingTime is null
and a.ArriveTime is not null and a.CloseTime is not null

select a.whgs, b.* from T_Address2 a, z01 b
where a.xqbh=b.AddressCode

/*  2.untackled ticket*/
drop table z002
select FaultBillNo,xqmc,Remark,Type,Address,AddressCode,CreateTime,ArriveTime,CloseTime into z002
from T_FaultBill a where a.CloseTime is null  
and (a.ArriveTime-a.CreateTime>2 or a.RespondTime-a.CreateTime>0.30)
and a.CreateTime between '20190301' and '20190401' 
and a.Delay3Time is null and a.Delay3ingTime is null 
and a.Delay7Time is null and a.Delay7ingTime is null

select * from z002

/*
Z0 was splited into nine groups
*/
select a.whgs, b.* from T_Address2 a, z002 b
where a.xqbh=b.AddressCode

 

 

• 超过三小时的故障

 

drop table z01

select  FaultBillNo,xqmc,Remark,Type,Address,AddressCode,CreateTime,RespondTime,ArriveTime,CloseTime  into z01

from T_FaultBill a where a.CreateTime between '20190301' and '20190401' 

and  ((datediff(s,a.FaultTime,CloseTime) > 10800 and status = 'close')

or (datediff(s,a.FaultTime,GETDATE()) > 10800 and status <> 'close'))

 

/*

Z01 was splited into nine groups

*/

 

select a.whgs, b.* from T_Address2 a, z01 b where a.xqbh = b.AddressCode