SQL server 按白夜班查询报警数据

SQL server 按白夜班查询报警数据

SQL实例代码

1.按照时间段、班次查询数据

--按时间段、班次查询数据
select * from (select Line,Machine,Adress,Adress_num, UpStart_time,UpEnd_time,CONVERT (CHAR(10), UpStart_time, 120)as DATATIME,BC=case when datename(hour,UpStart_time)<8 or datename(hour,UpStart_time)>=20 then '夜班' else '白班' endfrom B10_ALM_Data_Record_History )A where A.BC='白班' and A.DATATIME between '2022-10-31' and '2022-10-31'
union all
select * from (select Line,Machine,Adress,Adress_num, UpStart_time,UpEnd_time,CONVERT (CHAR(10), UpStart_time, 120)as DATATIME,BC=case when datename(hour,UpStart_time)<8 or datename(hour,UpStart_time)>=20 then '夜班' else '白班' endfrom B10_ALM_Data_Record )A where A.BC='白班' and A.DATATIME between '2022-10-31' and '2022-10-31'

查询结果

2.按年、班次查询数据

--按年、班次查询数据select * from (select Line,Machine,Adress,Adress_num, UpStart_time,UpEnd_time,CONVERT (CHAR(10), UpStart_time, 120)as DATATIME,BC=case when datename(hour,UpStart_time)<8 or datename(hour,UpStart_time)>=20 then '夜班' else '白班' endfrom B10_ALM_Data_Record_History )A where A.BC='夜班' and year(A.DATATIME)='2022'

3.按月、班次查询数据

--按月、班次查询数据
select * from (select Line,Machine,Adress,Adress_num, UpStart_time,UpEnd_time,CONVERT (CHAR(10), UpStart_time, 120)as DATATIME,BC=case when datename(hour,UpStart_time)<8 or datename(hour,UpStart_time)>=20 then '夜班' else '白班' endfrom B10_ALM_Data_Record_History )A where A.BC='夜班' and month(A.DATATIME)='11'

4.按照日、班次查询

--按照日、班次查询
select * from (select Line,Machine,Adress,Adress_num, UpStart_time,UpEnd_time,CONVERT (CHAR(10), UpStart_time, 120)as DATATIME,BC=case when datename(hour,UpStart_time)<8 or datename(hour,UpStart_time)>=20 then '夜班' else '白班' endfrom B10_ALM_Data_Record_History )A where A.BC='夜班' and A.DATATIME='2022-11-05'

说明：按照周的暂时未写