预约业务等待时长计算查询语句:
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 消化内镜