Getting Viral Load Data - APHL-Global-Health/OpenLDR-Dashboard GitHub Wiki

To improve on performance, instead of querying directly from the OpenLDR requests and labresults tables, a new HIVVL table is created (that can be refreshed at determined intervals) that holds some test level pre-calculated information. The query for creating the table is as follows. Note that this is only done once. Also, to avoid making changes to OpenLDR databases, a new database called OpenLDRReporting is used for creating any new database objects

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

select *
into dbo.HIVVL
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.AgeInYears=0 then '<01'
	when r.AgeInYears between 1 and 4 then '01-04'
	when r.AgeInYears between 5 and 9 then '05-09'
	when r.AgeInYears between 10 and 14 then '10-14'
	when r.AgeInYears between 15 and 19 then '15-19'
	when r.AgeInYears between 20 and 24 then '20-24'
	when r.AgeInYears between 25 and 29 then '25-29'
	when r.AgeInYears between 30 and 34 then '30-34'
	when r.AgeInYears between 35 and 39 then '35-39'
	when r.AgeInYears between 40 and 44 then '40-44'
	when r.AgeInYears between 45 and 49 then '45-49'
	when r.AgeInYears >=50 then '50+'
	else NULL
end AgeGroup
,r.AuthorisedDateTime TestedDate
,CASE WHEN (
				(
					(LimsRptResult in 
						('<20 copies/mL','TAR','Target Not Detected','TARG','< 20','RNA not detected','<30 copies/ml','<30 copies/mL','NDET')
					) OR 
					(isnumeric(LimsRptResult)=1 and cast(LimsRptResult as float)<1000)
				)) 
					THEN 1
			 else 0
	end
Suppressed,
case when LIMSRptResult in ('TAR','Target Not Detected','TARG','RNA not detected','NDET') then 1 else 0 end UU,
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='HIVVL'
and LIMSObservationCode in ('HIVVL','HIVVC')
and HL7ResultStatusCode='F'
and CASE WHEN (LimsRptResult IN 
				('<20 copies/mL','TAR','Target Not Detected','TARG','< 20','RNA not detected','<30 copies/ml','<30 copies/mL','NDET')) OR (isnumeric(LimsRptResult)=1) 
					THEN 1
			 WHEN LimsRptResult in ('INVAL','Please repeat','Insufficient serum received','Insufficient for further tests','Indeterminate','Haemolysed','FAIL','Absent','Haemolysed ++','Icteric ++++','INV','This result has been checked','to confirm patient identity.','Positive','Negative','No') then 0
	END=1
) s

After this table is created, a separate procedure is created that either updates the stored information, or adds new information to make it more up to date. The procedure is defined as follows

USE [OpenLDRReporting]
GO
/****** Object:  StoredProcedure [dbo].[sp_update_HIVVLTable]    Script Date: 6/23/2020 9:09:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_update_HIVVLTable]
as

BEGIN
MERGE dbo.HIVVL vl 
    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.AgeInYears=0 then '<01'
	when r.AgeInYears between 1 and 4 then '01-04'
	when r.AgeInYears between 5 and 9 then '05-09'
	when r.AgeInYears between 10 and 14 then '10-14'
	when r.AgeInYears between 15 and 19 then '15-19'
	when r.AgeInYears between 20 and 24 then '20-24'
	when r.AgeInYears between 25 and 29 then '25-29'
	when r.AgeInYears between 30 and 34 then '30-34'
	when r.AgeInYears between 35 and 39 then '35-39'
	when r.AgeInYears between 40 and 44 then '40-44'
	when r.AgeInYears between 45 and 49 then '45-49'
	when r.AgeInYears >=50 then '50+'
	else NULL
end AgeGroup
,r.AuthorisedDateTime TestedDate
,CASE WHEN (
				(
					(LimsRptResult in 
						('<20 copies/mL','TAR','Target Not Detected','TARG','< 20','RNA not detected','<30 copies/ml','<30 copies/mL','NDET')
					) OR 
					(isnumeric(LimsRptResult)=1 and cast(LimsRptResult as float)<1000)
				)) 
					THEN 1
			 else 0
	end
Suppressed,
case when LIMSRptResult in ('TAR','Target Not Detected','TARG','RNA not detected','NDET') then 1 else 0 end UU,
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='HIVVL'
and LIMSObservationCode in ('HIVVL','HIVVC')
and HL7ResultStatusCode='F'
and CASE WHEN (LimsRptResult IN 
				('<20 copies/mL','TAR','Target Not Detected','TARG','< 20','RNA not detected','<30 copies/ml','<30 copies/mL','NDET')) OR (isnumeric(LimsRptResult)=1) 
					THEN 1
			 WHEN LimsRptResult in ('INVAL','Please repeat','Insufficient serum received','Insufficient for further tests','Indeterminate','Haemolysed','FAIL','Absent','Haemolysed ++','Icteric ++++','INV','This result has been checked','to confirm patient identity.','Positive','Negative','No') then 0
	END=1) vl_s
ON (vl_s.Province= vl.Province and vl_s.District=vl.District and vl_s.Facility=vl.Facility and vl_s.LabID=vl.LabID)
WHEN MATCHED
    THEN UPDATE SET 
        Province = vl_s.Province,
        District = vl_s.District,
		Facility = vl_s.Facility,
        LabID = vl_s.LabID,
		Gender = vl_s.Gender,
        AgeGroup = vl_s.AgeGroup,
		TestedDate = vl_s.TestedDate,
        Suppressed = vl_s.Suppressed,
		UU = vl_s.UU,
        TAT = vl_s.TAT
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (Province, District, Facility,LabID,Gender,AgeGroup,TestedDate,Suppressed,UU,TAT)
         VALUES (vl_s.Province, vl_s.District, vl_s.Facility,vl_s.LabID,vl_s.Gender,vl_s.AgeGroup,vl_s.TestedDate,vl_s.Suppressed,vl_s.UU,vl_s.TAT);
--WHEN NOT MATCHED BY SOURCE 
--    THEN DELETE;
END

Using sql server Agent this stored procedure can be scheduled to run at desired frequency. Note that if there are duplicated in your data, this query will not run. It is essential that duplicates are removed before this query is run.

This approach tremendously improves query performance for the stored procedure to get Viral load data. The query that previously run for 4 minutes 53 seconds on the zambia dataset nows runs just for a couple of seconds. The stored procedure to get Viral load data is defined as sholwn below;

USE [OpenLDRReporting]
GO
/****** Object:  StoredProcedure [dbo].[sp_dash_getHIVVL_v2]    Script Date: 6/23/2020 9:16:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_dash_getHIVVL_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(Suppressed) Suppressed
,count(LabID)-sum(suppressed) Unsuppressed
,sum(UU) Undetectable
,cast(sum(suppressed) as float)/count(LabID) SuppressionRate
,AVG(TAT) AverageTAT
,sum(case AgeGroup when '<01' then 1 else 0 end) [lt01]
,sum(case AgeGroup when '01-04' then 1 else 0 end) [1to4]
,sum(case AgeGroup when '05-09' then 1 else 0 end) [5to9]
,sum(case AgeGroup when '10-14' then 1 else 0 end) [10to14]
,sum(case AgeGroup when '15-19' then 1 else 0 end) [15to19]
,sum(case AgeGroup when '20-24' then 1 else 0 end) [20to24]
,sum(case AgeGroup when '25-29' then 1 else 0 end) [25to29]
,sum(case AgeGroup when '30-34' then 1 else 0 end) [30to34]
,sum(case AgeGroup when '35-39' then 1 else 0 end) [35to39]
,sum(case AgeGroup when '40-44' then 1 else 0 end) [40to44]
,sum(case AgeGroup when '45-49' then 1 else 0 end) [45to49]
,sum(case AgeGroup when '50+' then 1 else 0 end) [50plus]
,sum(case when AgeGroup is NULL then 1 else 0 end) [AgeMissing]
from dbo.HIVVL
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(Suppressed) Suppressed
,count(LabID)-sum(suppressed) Unsuppressed
,sum(UU) Undetectable
,cast(sum(suppressed) as float)/count(LabID) SuppressionRate
,AVG(TAT) AverageTAT
,sum(case AgeGroup when '<01' then 1 else 0 end) [lt01]
,sum(case AgeGroup when '01-04' then 1 else 0 end) [1to4]
,sum(case AgeGroup when '05-09' then 1 else 0 end) [5to9]
,sum(case AgeGroup when '10-14' then 1 else 0 end) [10to14]
,sum(case AgeGroup when '15-19' then 1 else 0 end) [15to19]
,sum(case AgeGroup when '20-24' then 1 else 0 end) [20to24]
,sum(case AgeGroup when '25-29' then 1 else 0 end) [25to29]
,sum(case AgeGroup when '30-34' then 1 else 0 end) [30to34]
,sum(case AgeGroup when '35-39' then 1 else 0 end) [35to39]
,sum(case AgeGroup when '40-44' then 1 else 0 end) [40to44]
,sum(case AgeGroup when '45-49' then 1 else 0 end) [45to49]
,sum(case AgeGroup when '50+' then 1 else 0 end) [50plus]
,sum(case when AgeGroup is NULL then 1 else 0 end) [AgeMissing]
from dbo.HIVVL
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(Suppressed) Suppressed
,count(LabID)-sum(suppressed) Unsuppressed
,sum(UU) Undetectable
,cast(sum(suppressed) as float)/count(LabID) SuppressionRate
,AVG(TAT) AverageTAT
,sum(case AgeGroup when '<01' then 1 else 0 end) [lt01]
,sum(case AgeGroup when '01-04' then 1 else 0 end) [1to4]
,sum(case AgeGroup when '05-09' then 1 else 0 end) [5to9]
,sum(case AgeGroup when '10-14' then 1 else 0 end) [10to14]
,sum(case AgeGroup when '15-19' then 1 else 0 end) [15to19]
,sum(case AgeGroup when '20-24' then 1 else 0 end) [20to24]
,sum(case AgeGroup when '25-29' then 1 else 0 end) [25to29]
,sum(case AgeGroup when '30-34' then 1 else 0 end) [30to34]
,sum(case AgeGroup when '35-39' then 1 else 0 end) [35to39]
,sum(case AgeGroup when '40-44' then 1 else 0 end) [40to44]
,sum(case AgeGroup when '45-49' then 1 else 0 end) [45to49]
,sum(case AgeGroup when '50+' then 1 else 0 end) [50plus]
,sum(case when AgeGroup is NULL then 1 else 0 end) [AgeMissing]
from dbo.HIVVL
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(Suppressed) Suppressed
,count(LabID)-sum(suppressed) Unsuppressed
,sum(UU) Undetectable
,cast(sum(suppressed) as float)/count(LabID) SuppressionRate
,AVG(TAT) AverageTAT
,sum(case AgeGroup when '<01' then 1 else 0 end) [lt01]
,sum(case AgeGroup when '01-04' then 1 else 0 end) [1to4]
,sum(case AgeGroup when '05-09' then 1 else 0 end) [5to9]
,sum(case AgeGroup when '10-14' then 1 else 0 end) [10to14]
,sum(case AgeGroup when '15-19' then 1 else 0 end) [15to19]
,sum(case AgeGroup when '20-24' then 1 else 0 end) [20to24]
,sum(case AgeGroup when '25-29' then 1 else 0 end) [25to29]
,sum(case AgeGroup when '30-34' then 1 else 0 end) [30to34]
,sum(case AgeGroup when '35-39' then 1 else 0 end) [35to39]
,sum(case AgeGroup when '40-44' then 1 else 0 end) [40to44]
,sum(case AgeGroup when '45-49' then 1 else 0 end) [45to49]
,sum(case AgeGroup when '50+' then 1 else 0 end) [50plus]
,sum(case when AgeGroup is NULL then 1 else 0 end) [AgeMissing]
from dbo.HIVVL
where cast(TestedDate as date)  between @StDate and @EDate
and Province=@Province and District=@District and Facility= @Facility
group by Province, District, Facility

END