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

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

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 消化内镜

 

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

上一篇:Nacos配置中心


下一篇:vscode中使用git合并分支