⚠️SQL查询失控项目

SQL语句

SELECT
	LabLotTest.LabID,
	Instrument.Name,
	LabLotTest.LotID,
	Analyte.Name,
	Analyte.AnalyteID,
	Unit.Name,
	Method.Name,
	Reagent.Name,
	Temperature.Name,
	PointData.EnteredDate,
	PointData.Operator,
	PointData.Level1Value,
	PointData.Level1Violation,
	PointData.Level1Status,
	PointData.Level2Value,
	PointData.Level2Violation,
	PointData.Level2Status,
	PointData.Level3Value,
	PointData.Level3Violation,
	PointData.Level3Status,
	PointData.Level4Value,
	PointData.Level4Violation,
	PointData.ActionLog,
	PointData.Comment,
	PointData.RuleApplied,
	PointData.FloatMeanSD,
	PointData.FixedMeanSD 
FROM
	PointData,
	LabLotTest,
	Test,
	Analyte,
	Instrument,
	Method,
	Reagent,
	Temperature,
	Unit 
WHERE
	( PointData.LabLotTestID = LabLotTest.LabLotTestID ) 
	AND ( Test.TestID = LabLotTest.TestID ) 
	AND ( Test.AnalyteID = Analyte.AnalyteID ) 
	AND ( Test.InstrumentID = Instrument.InstrumentID ) 
	AND ( Test.MethodID = Method.MethodID ) 
	AND ( Test.ReagentID = Reagent.ReagentID ) 
	AND ( Test.TemperatureID = Temperature.TemperatureID ) 
	AND ( Test.UnitID = Unit.UnitID ) 
	
	AND (
	   	   ( PointData.Level1Violation <> ''  and PointData.Level1Status = 0) 
		OR ( PointData.Level2Violation <> ''  and PointData.Level2Status = 0) 
		OR ( PointData.Level3Violation <> ''  and PointData.Level3Status = 0) 
		OR ( PointData.Level4Violation <> ''  and PointData.Level4Status = 0)  
	) 


	AND PointData.EnteredDate > = '2021-07-19' 
	AND PointData.EnteredDate < = '2021-07-19 23:59:59'
	and LabLotTest.LabID in (232017)
	

说明

PointData.EnteredDate 为查询的时间段

LabLotTest.LabID 为查询某个实验室号是否有失控

如需精确到批号，则添加 LabLotTest.LotID 条件，如：

LabLotTest.LabID in ('88100')

此外，还可以根据 测试组 的条件查询：

LabLotTest.LabLotTestID  in 
( select LabLotTestID from Panel_All_VW where Panel_Name  like N'生化特检' )

其中 生化待检 为测试组的名称

查询的结果

LabID	Name	LotID	Name	AnalyteID	Name	Name	Name	Name	EnteredDate	Operator	Level1Value	Level1Violation	Level1Status	Level2Value	Level2Violation	Level2Status
232017	Roche cobas 8000	88100	Protein, Total, Urine/Spinal Fluid	444	mg/L	Benzethonium chloride	Dedicated Reagent	No Temperature	2021-07-29 15:16:00.000	sa	100	1-3S|R-4S|	0	527	R-4S|	0