时间戳

首页 > 技术文章 > 预约业务等待时长计算查询语句

预约业务等待时长计算查询语句

AmatVictorialCuram 2021-06-23 17:33 原文

预约业务等待时长计算查询语句：

DECLARE @StartTime DATETIME = '2020-08-01 00:00:00' ,
      @EndTime DATETIME = '2020-08-31 23:59:59',
      @超声 INT =0,
      @CT平扫 INT =0,
      @CT增强 INT =0,
      @MRI平扫 INT =0,
      @MRI增强 INT =0,
      @消化内镜 INT =0

SELECT @超声= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ii.Classify='0200030011'

SELECT @超声=@超声/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3 
AND ii.Classify='0200030011')


SELECT @CT平扫= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ii.Classify='0200030001' AND ii.Name LIKE'%平扫%'

SELECT @CT平扫=@CT平扫/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3 
AND ii.Classify='0200030001' AND ii.Name LIKE'%平扫%')

SELECT @CT增强= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ((ii.Classify='0200030001' AND ii.Name LIKE'%增强%') OR (ii.Name LIKE'%CTV%' OR ii.Name LIKE'%CTU%'))

SELECT @CT增强=@CT增强/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3  
AND ((ii.Classify='0200030001' AND ii.Name LIKE'%增强%') OR (ii.Name LIKE'%CTV%' OR ii.Name LIKE'%CTU%')))


SELECT @MRI平扫= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ii.Classify='0200030003' AND ii.Name LIKE'%平扫%' 

SELECT @MRI平扫=@MRI平扫/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3 
 AND ii.Classify='0200030003' AND ii.Name LIKE'%平扫%' )

SELECT @MRI增强= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ii.Classify='0200030003' AND ii.Name LIKE'%增强%'

SELECT @MRI增强=@MRI增强/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3  
AND ii.Classify='0200030003' AND ii.Name LIKE'%增强%')



SELECT @消化内镜= SUM(DATEDIFF(minute,ai.CreateDate,ai.CheckTime)) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3
AND ii.Classify='0200030019' 

SELECT @消化内镜=@消化内镜/(SELECT COUNT(1) FROM dbo.MQ_AppointmentInfo ai INNER JOIN dbo.MQ_InspectionItem ii
ON ai.InspectionCode=ii.Code WHERE ai.StartTime>=@StartTime AND ai.EndTime<=@EndTime AND ai.BillStatus !='1' AND ai.BillStatus!=3  AND ii.Classify='0200030019' )





SELECT @超声 AS 超声, @CT增强 AS CT增强,@CT平扫 AS CT平扫,@MRI平扫 AS MRI平扫,@MRI增强 AS MRI增强,@消化内镜 AS 消化内镜

 

推荐阅读