AS
BEGIN
SET NOCOUNT ON;
DECLARE
--name of previous month
@MonthName VARCHAR(MAX) = DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),
--first day of previous month
@BeginDt DATETIME = DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) ,
--end of last day of last month
@EndDt DATETIME = DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))
INSERT INTO PhaLabInrGlucose (Facility,MonthTitle,DataElement,Number)
select 'ABC' as Organization,
@MonthName,
--DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 3, 0)) as MonthTitle,
'BSGLU_Numerator' as DataElement,
(ResultRW + ResultRW1) As Number from
(select
COUNT(LST.ResultRW) as ResultRW
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
ISNUMERIC(LST.ResultRW)=1 and
CAST(LST.ResultRW As Integer) < '40' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt ) A
cross join (select
COUNT(LST.ResultRW) as ResultRW1
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
--ISNUMERIC(LST.ResultRW)=1 and
LST.ResultRW = '< 20' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt) B
INSERT INTO PhaLabInrGlucose (Facility,MonthTitle,DataElement,Number)
select 'ABC' as Organization,
@MonthName,
--DATENAME(month, DATEADD(month, DATEDIFF(month, -1, getdate()) - 3, 0)) as MonthTitle,
'BSGLU_Denominator' as DataElement,
COUNT(LST.ResultRW) as Number
FROM livedb.dbo.LabSpecimens LS
INNER JOIN livedb.dbo.LabSpecimenTests LST ON LS.SourceID = LST.SourceID AND LS.SpecimenID = LST.SpecimenID
INNER JOIN livedb.dbo.DLabTest t ON LST.SourceID = t.SourceID AND LST.TestPrintNumberID = t.PrintNumberID
INNER JOIN AbstractData a ON a.VisitID=LS.VisitID
where t.Mnemonic='GLUBS' and
--a.LocationID NOT IN('ER','EDOVINP','NSRY', 'NSYOP',) and
a.PatientClass = 'IN' and
a.LocationID NOT IN ('NSRY','EDOVINP') and
LST.ResultRW is not null and
--ISNUMERIC(LST.ResultRW)=1 and
--CAST(LST.ResultRW As Integer) < '40' and
LS.CollectionDateTime BETWEEN @BeginDt AND @EndDt
select Facility ,MonthTitle ,DataElement ,Number ,ID , LoadDate from PhaLabInrGlucose where MonthTitle = @MonthName
SET NOCOUNT OFF
END