Getting EID Data

An approach similar to that for Geeting Viral Load data is used. A table for storing precalculated query information is created as follows

use OpenLDRReporting
go
 IF OBJECT_ID('dbo.EID', 'U') IS NOT NULL
 DROP TABLE dbo.EID
 go

select *
into dbo.EID
from
(Select a.HealthCareAreaDesc Province, b.HealthCareAreaDesc District, f.Description Facility
,r.requestid LabID
,case r.HL7SexCode
when 'F' then 'Female'
when 'M' then 'Male'
when 'I' then 'Indeterminate'
when 'U' then 'Unknown'
else NULL
end Gender
,case 
	when r.AgeInDays between 0 and 6 then 'At Birth'
	when r.AgeInDays between 7 and 48 then '1 - 6 Weeks'
	when r.AgeInDays between 49 and 209 then '7 weeks to 6 Months'
	when r.AgeInDays between 210 and 299 then '7 - 9 Months'
	when r.AgeInDays between 300 and 389 then '10 - 12 Months'
	when r.AgeInDays between 390 and 569 then '13 - 18 Months'
	when r.AgeInDays between 570 and 749 then '19 - 24 Months'
	when r.AgeInDays >=750 then '> 2 years'
	else NULL
end AgeGroup
,r.AuthorisedDateTime TestedDate
,CASE WHEN 
		LimsRptResult in 
			('Positive','Positive*','POS','Detected DBS')
	THEN 1
			 else 0
	end Positive,
datediff(dd,r.SpecimenDateTime,r.AuthorisedDateTime) TAT
 from

openldrdata.dbo.Requests r
inner join openldrdata.dbo.LabResults l
on r.RequestID=l.RequestID and r.OBRSetID=l.OBRSetID
left join openldrdict.dbo.Facilities f
on r.RequestingFacilityCode=f.FacilityCode
left join openldrdict.dbo.HealthcareAreas a
on concat(f.CountryCode,f.ProvinceCode)=a.HealthcareAreaCode
left join openldrdict.dbo.HealthcareAreas b
on concat(f.CountryCode,f.ProvinceCode,f.DistrictCode)=b.HealthcareAreaCode

where 

LIMSPanelCode='HIVPC'
and LIMSObservationCode = 'HIVPC'
and HL7ResultStatusCode='F'
and LimsRptResult not in ('Indeterminate')
	) s

A mechanism for updating this information using merge statement is created and scheduled to run periodically. The following query is used

USE OpenLDRReporting
GO
CREATE procedure sp_update_EIDTable
As
Begin
MERGE dbo.EID t 
    USING (
Select a.HealthCareAreaDesc Province, b.HealthCareAreaDesc District, f.Description Facility
,r.requestid LabID
,case r.HL7SexCode
when 'F' then 'Female'
when 'M' then 'Male'
when 'I' then 'Indeterminate'
when 'U' then 'Unknown'
else NULL
end Gender
,case 
	when r.AgeInDays between 0 and 6 then 'At Birth'
	when r.AgeInDays between 7 and 48 then '1 - 6 Weeks'
	when r.AgeInDays between 49 and 209 then '7 weeks to 6 Months'
	when r.AgeInDays between 210 and 299 then '7 - 9 Months'
	when r.AgeInDays between 300 and 389 then '10 - 12 Months'
	when r.AgeInDays between 390 and 569 then '13 - 18 Months'
	when r.AgeInDays between 570 and 749 then '19 - 24 Months'
	when r.AgeInDays >=750 then '> 2 years'
	else NULL
end AgeGroup
,r.AuthorisedDateTime TestedDate
,CASE WHEN 
		LimsRptResult in 
			('Positive','Positive*','POS','Detected DBS')
	THEN 1
			 else 0
	end Positive,
datediff(dd,r.SpecimenDateTime,r.AuthorisedDateTime) TAT
 from

openldrdata.dbo.Requests r
inner join openldrdata.dbo.LabResults l
on r.RequestID=l.RequestID and r.OBRSetID=l.OBRSetID
left join openldrdict.dbo.Facilities f
on r.RequestingFacilityCode=f.FacilityCode
left join openldrdict.dbo.HealthcareAreas a
on concat(f.CountryCode,f.ProvinceCode)=a.HealthcareAreaCode
left join openldrdict.dbo.HealthcareAreas b
on concat(f.CountryCode,f.ProvinceCode,f.DistrictCode)=b.HealthcareAreaCode

where 

LIMSPanelCode='HIVPC'
and LIMSObservationCode = 'HIVPC'
and HL7ResultStatusCode='F'
and LimsRptResult not in ('Indeterminate')) s
ON (s.Province= t.Province and s.District=t.District and s.Facility=t.Facility and s.LabID=t.LabID)
WHEN MATCHED
    THEN UPDATE SET 
        Province = s.Province,
        District = s.District,
		Facility = s.Facility,
        LabID = s.LabID,
		Gender = s.Gender,
        AgeGroup = s.AgeGroup,
		TestedDate = s.TestedDate,
        Positive = s.Positive,
        TAT = s.TAT
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (Province, District, Facility,LabID,Gender,AgeGroup,TestedDate,Positive,TAT)
         VALUES (s.Province, s.District, s.Facility,s.LabID,s.Gender,s.AgeGroup,s.TestedDate,s.Positive,s.TAT);
--WHEN NOT MATCHED BY SOURCE 
--    THEN DELETE;
end

Stored procedure for getting EID information is created as follows

USE [OpenLDRReporting]
GO
/****** Object:  StoredProcedure [dbo].[sp_dash_getEID]    Script Date: 6/23/2020 10:14:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_dash_getEID_V2](
@Province varchar(20)=NULL,
@District varchar(40)=NULL,
@Facility varchar(40)=NULL,
@StDate date = null,
@EDate date=null
)
as
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
if @StDate is null 
set @StDate=DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) 
if @EDate is null 
set @EDate=DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) 
if @Province is null and @District is null and @Facility is null

Select 'All' Province,'All' District,'All' Facility, count(LabID) Tests
,sum(Positive) [Positive]
,count(LabID)-sum(Positive) Negative
,cast(sum(Positive) as float)/count(LabID) PositivityRate
,AVG(TAT) AverageTAT
from dbo.EID
where cast(TestedDate as date) between @StDate and @EDate

Else if @District is null and @Facility is null and @Province is not null

Select  Province, 'All' District,'All' Facility,count(LabID) Tests
,sum(Positive) Positive
,count(LabID)-sum(Positive) Negative
,cast(sum(Positive) as float)/count(LabID) PositivityRate
,AVG(TAT) AverageTAT
from dbo.EID
where cast(TestedDate as date) between @StDate and @EDate
and Province=@Province
group by Province

else if @Province is not null and @District is not null and @Facility is null

Select Province, District,'All' Facility, count(LabID) Tests
,sum(Positive) Positive
,count(LabID)-sum(Positive) Negative
,cast(sum(Positive) as float)/count(LabID) PositivityRate
,AVG(TAT) AverageTAT
from dbo.EID
where cast(TestedDate as date) between @StDate and @EDate
and Province=@Province and District=@District
group by Province, District

else if @Province is not null and @District is not null and @Facility is not null

Select  Province, District, Facility,count(LabID) Tests
,sum(Positive) Positive
,count(LabID)-sum(Positive) Negative
,cast(sum(Positive) as float)/count(LabID) PositivityRate
,AVG(TAT) AverageTAT
from dbo.EID
where cast(TestedDate as date)  between @StDate and @EDate
and Province=@Province and District=@District and Facility= @Facility
group by Province, District, Facility

END