KinnickGable
12/21/2017 - 4:05 AM
Patient Sanity Query with Variables
Sanity Patients Query
--Updated 13 March 2013 - TW - Added a variety score to the select to easily see patients with a variety of act types.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
use dbmVCDRData
-- use dbmVCDRDataHistory -- uncomment for querying the history CDR
go
declare @startTime as datetime,
@patientidroot as varchar(255),
@numberofrecords as int;
set @startTime='2016-12-15 00:00:00';
/* Define Assigning Authority */
set @patientidroot ='1.3.6.1.4.1.22812.3.7753501.0';
/* Define # of records */
set @numberofrecords = 15;
IF OBJECT_ID('tempdb..#EncountersCount') IS NOT NULL DROP TABLE #EncountersCount
IF OBJECT_ID('tempdb..#DiagnosesCount') IS NOT NULL DROP TABLE #DiagnosesCount
IF OBJECT_ID('tempdb..#AllergiesCount') IS NOT NULL DROP TABLE #AllergiesCount
IF OBJECT_ID('tempdb..#ProblemsCount') IS NOT NULL DROP TABLE #ProblemsCount
IF OBJECT_ID('tempdb..#ImmunizationsCount') IS NOT NULL DROP TABLE #ImmunizationsCount
IF OBJECT_ID('tempdb..#MedicationsCount') IS NOT NULL DROP TABLE #MedicationsCount
IF OBJECT_ID('tempdb..#LabEventsCount') IS NOT NULL DROP TABLE #LabEventsCount
IF OBJECT_ID('tempdb..#ImagingCount') IS NOT NULL DROP TABLE #ImagingCount
IF OBJECT_ID('tempdb..#ProceduresCount') IS NOT NULL DROP TABLE #ProceduresCount
IF OBJECT_ID('tempdb..#DocumentsCount') IS NOT NULL DROP TABLE #DocumentsCount
IF OBJECT_ID('tempdb..#ActsCount') IS NOT NULL DROP TABLE #ActsCount
IF OBJECT_ID('tempdb..#PatientForDemoRepositiory_Raw') IS NOT NULL DROP TABLE #PatientForDemoRepositiory_Raw
CREATE TABLE #EncountersCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #DiagnosesCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #AllergiesCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #ProblemsCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #ImmunizationsCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #MedicationsCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #LabEventsCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #ImagingCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #ProceduresCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #DocumentsCount (PatientRecordID bigint,RecNumber bigint)
CREATE TABLE #ActsCount
(PatientRecordID bigint
,EncountersCount bigint,DiagnosesCount bigint, AllergiesCount bigint
,ProblemsCount bigint,ImmunizationsCount bigint, MedicationsCount bigint
,LabEventsCount bigint,ImagingCount bigint, ProceduresCount bigint, DocumentsCount bigint
)
INSERT INTO #EncountersCount (PatientRecordID,RecNumber)
SELECT
PatientRecordID,COUNT(main.EncounterID)
FROM dbmVCDRData.PatientAdministration.Encounter main
where main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #DiagnosesCount (PatientRecordID,RecNumber)
SELECT
PatientRecordID,COUNT(main.ConditionID)
FROM dbmVCDRData.Condition.Condition main
Where main.dbmClassCodeID=65536
and main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #AllergiesCount (PatientRecordID,RecNumber)
SELECT
PatientRecordID,COUNT(main.AllergyIntoleranceID)
FROM dbmVCDRData.Allergy.AllergyIntolerance main
--Where main.dbmClassCodeID=65536
where main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #ProblemsCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.ConditionID)
FROM dbmVCDRData.Condition.Condition main
Where main.dbmClassCodeID=16384
Group by PatientRecordID
INSERT INTO #ImmunizationsCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.SubstanceAdministrationID)
FROM dbmVCDRData.Medication.SubstanceAdministration main
Where main.dbmClassCodeID=131072
and main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #MedicationsCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.SubstanceAdministrationID)
FROM dbmVCDRData.Medication.SubstanceAdministration main
Where main.dbmClassCodeID=128
and main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #LabEventsCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.LaboratoryEventID)
FROM dbmVCDRData.Laboratory.LaboratoryEvent main
Where main.dbmClassCodeID=8192
and main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #ProceduresCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.ProcedureID)
FROM dbmVCDRData.[Procedure].[Procedure] main
--Where main.dbmClassCodeID=8192
where main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #ImagingCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.ImagingStudyID)
FROM dbmVCDRData.Imaging.ImagingStudy main
--Where main.dbmClassCodeID=8192
where main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #DocumentsCount (PatientRecordID,RecNumber)
Select
PatientRecordID,COUNT(main.ClinicalDocumentID)
FROM dbmVCDRData.ClinicalDocument.ClinicalDocument main
Where main.ClinicalDocumentID not in -- check the the clinical document is not already related to other act which is not encnouter, e.g. imaging
(select actrel.TargetActID
from dbmVCDRData.Common.ActRelationship actrel
where actrel.TargetActClassID=4096 and actrel.SourceActClassID!=1
)
and main.dbmAvailabilityTime>@startTime
Group by PatientRecordID
INSERT INTO #ActsCount
(PatientRecordID
,EncountersCount,DiagnosesCount, AllergiesCount
,ProblemsCount,ImmunizationsCount, MedicationsCount
,LabEventsCount,ImagingCount, ProceduresCount, DocumentsCount)
Select
pr.PatientRecordID
,#EncountersCount.RecNumber,#DiagnosesCount.RecNumber,#AllergiesCount.RecNumber
,#ProblemsCount.RecNumber,#ImmunizationsCount.RecNumber,#MedicationsCount.RecNumber
,#LabEventsCount.RecNumber,#ImagingCount.RecNumber,#ProceduresCount.RecNumber
,#DocumentsCount.RecNumber
FROM dbmVCDRData.PatientAdministration.PatientRecord pr
left outer join #EncountersCount on
pr.PatientRecordID=#EncountersCount.PatientRecordID
left outer join #DiagnosesCount on
pr.PatientRecordID=#DiagnosesCount.PatientRecordID
left outer join #AllergiesCount on
pr.PatientRecordID=#AllergiesCount.PatientRecordID
left outer join #ProblemsCount on
pr.PatientRecordID=#ProblemsCount.PatientRecordID
left outer join #ImmunizationsCount on
pr.PatientRecordID=#ImmunizationsCount.PatientRecordID
left outer join #MedicationsCount on
pr.PatientRecordID=#MedicationsCount.PatientRecordID
left outer join #LabEventsCount on
pr.PatientRecordID=#LabEventsCount.PatientRecordID
left outer join #ImagingCount on
pr.PatientRecordID=#ImagingCount.PatientRecordID
left outer join #ProceduresCount on
pr.PatientRecordID=#ProceduresCount.PatientRecordID
left outer join #DocumentsCount on
pr.PatientRecordID=#DocumentsCount.PatientRecordID
--select top 100 from #ActsCount
select top (@numberofrecords)
PtID.AssigningAuthorityName as AssigningAuthority,
PtId.Extension as PatientID, --Patient ID info
#ActsCount.EncountersCount,
#ActsCount.DiagnosesCount,
#ActsCount.AllergiesCount,
#ActsCount.ProblemsCount,
#ActsCount.ImmunizationsCount,
#ActsCount.MedicationsCount,
#ActsCount.LabEventsCount,
#ActsCount.ImagingCount,
#ActsCount.ProceduresCount,
#ActsCount.DocumentsCount, --Act Count Columns
ISNULL(#ActsCount.EncountersCount, 0) +
ISNULL(#ActsCount.DiagnosesCount, 0) +
ISNULL(#ActsCount.AllergiesCount, 0) +
ISNULL(#ActsCount.ProblemsCount, 0) +
ISNULL(#ActsCount.ImmunizationsCount, 0) +
ISNULL(#ActsCount.MedicationsCount, 0) +
ISNULL(#ActsCount.LabEventsCount, 0) +
ISNULL(#ActsCount.ImagingCount, 0) +
ISNULL(#ActsCount.ProceduresCount, 0) +
ISNULL(#ActsCount.DocumentsCount, 0) as TotalActCount, --Total Act Count across the patient
ISNUMERIC(#ActsCount.EncountersCount) +
ISNUMERIC(#ActsCount.DiagnosesCount) +
ISNUMERIC(#ActsCount.AllergiesCount) +
ISNUMERIC(#ActsCount.ProblemsCount) +
ISNUMERIC(#ActsCount.ImmunizationsCount) +
ISNUMERIC(#ActsCount.MedicationsCount) +
ISNUMERIC(#ActsCount.LabEventsCount) +
ISNUMERIC(#ActsCount.ImagingCount) +
ISNUMERIC(#ActsCount.ProceduresCount) +
ISNUMERIC(#ActsCount.DocumentsCount) as VarietyScore --"variety score" of different acts assoc with patient. High score means lots of types of acts.
from #ActsCount
join PatientAdministration.PatientIdentifier PtId on PtId.PatientRecordID = #ActsCount.PatientRecordID
where PtId.IsPrimary = 1
AND ptid.Root = @patientidroot
order by VarietyScore desc, TotalActCount desc, EncountersCount desc;
--Select 'PatinetRecord Table'
--Select
-- pr.PatientRecordId,prid.Extension as PrimaryIdExtension,prid.Root as PrimaryIdRoot,prid.AssigningAuthorityName as PrimaryIdAssigningAuthority
-- ,pr.BirthTime,GendCSC.ConceptCode as AdministrativeGenderCode
-- ,pnpGiv.value as FirstName,pnpFam.value as LastName
---- ,pt.Value as Phone
-- ,apSAL.value as Street,apCTY.value as City, apValCodeSTA.ConceptCode as 'State',apZIP.value as ZIP
-- --,pridSSN.Extension as SSN
Create table #PatientForDemoRepositiory_Raw
(PatientRecordID varchar(255),
PrimaryIDExtension varchar(255),
PrimaryIDRoot varchar(255),
PrimaryIdAssigningAuthority varchar(255),
BirthTime datetime,
AdministrativeGenderCode varchar(255),
FirstName varchar(255),
LastName varchar(255),
Phone varchar(255),
Street varchar(255),
City varchar(255),
"State" varchar(255),
Zip varchar(255),
SSN varchar(255),
InterchangeId varchar(255)
)
Insert into #PatientForDemoRepositiory_Raw(
PatientRecordID
,PrimaryIDExtension
,PrimaryIDRoot
,PrimaryIdAssigningAuthority
,BirthTime
,AdministrativeGenderCode
,FirstName
,LastName
,Phone
,Street
,City
,"State"
,Zip
,SSN
,InterchangeId
)
Select
pr.PatientRecordId
,prid.Extension,prid.Root
,prid.AssigningAuthorityName
,pr.BirthTime
,GendCSC.ConceptCode
,pnpGiv.value
,pnpFam.value
--, Null --Phone
,PAAS.Address
,PAAS.CITY
,PAAS.State
,PAAS.Country
,PAAS.Zip
,pridSSN.Extension as SSN
,msg.InterchangeID
From PatientAdministration.PatientRecord pr with (nolock)
left outer join PatientAdministration.PatientIdentifier prid with (nolock)
on pr.PatientRecordId=prid.PatientRecordId and prid.IsPrimary=1
left outer join PatientAdministration.PatientIdentifier pridSSN with (nolock)
on pr.PatientRecordId=pridSSN.PatientRecordId
left outer join dbmVCDRData.vocabulary.CodeSystemConcept SSNCSC with (nolock)
on pridSSN.IdentifierTypeId=SSNCSC.cscid and SSNCSC.conceptcode='SSN'
left outer join dbmVCDRData.vocabulary.CodeSystemConcept GendCSC with (nolock)
on pr.AdministrativeGenderCodeID=GendCSC.CSCID
left outer join dbmVCDRData.vocabulary.CodeSystem GendCS with (nolock)
on GendCSC.CodeSystemId=GendCS.CodeSystemId
left outer join dbmVCDRData.vocabulary.DomainConcepts GendDC with (nolock)
on pr.AdministrativeGenderCodeID=GendDC.cscid
left outer join dbmVCDRData.vocabulary.Domain GendDM with (nolock)
on GendDC.DomainId=GendDM.DomainId
left outer join dbmVCDRData.vocabulary.CodeSystemConcept MariCSC with (nolock)
on pr.MaritalStatusCodeID=MariCSC.CSCID
left outer join dbmVCDRData.vocabulary.CodeSystemConcept EthnCSC with (nolock)
on pr.EthnicGroupCodeID=EthnCSC.CSCID
left outer join dbmVCDRData.vocabulary.CodeSystemConcept ReliCSC with (nolock)
on pr.ReligiousAffiliationCodeID=ReliCSC.CSCID
left outer join dbmVCDRData.vocabulary.CodeSystemConcept RaceCSC with (nolock)
on pr.RaceCodeID=RaceCSC.CSCID
left outer join dbmVCDRData.vocabulary.ConceptDesignation GendCD with (nolock)
on pr.AdministrativeGenderCodeID=GendCD.CSCID
left outer join dbmVCDRData.vocabulary.ConceptDesignation MariCD with (nolock)
on pr.MaritalStatusCodeID=MariCD.CSCID
left outer join dbmVCDRData.vocabulary.ConceptDesignation EthnCD with (nolock)
on pr.EthnicGroupCodeID=EthnCD.CSCID
left outer join dbmVCDRData.vocabulary.ConceptDesignation ReliCD with (nolock)
on pr.ReligiousAffiliationCodeID=ReliCD.CSCID
left outer join dbmVCDRData.vocabulary.ConceptDesignation RaceCD with (nolock)
on pr.RaceCodeID=RaceCD.CSCID
LEFT outer join dbmVCDRData.MessageWrapper.ControlAct ca with (nolock)
on Pr.ControlActId=ca.ControlActId
LEFT outer join dbmVCDRData.MessageWrapper.Message msg with (nolock)
on msg.MessageId=ca.MessageId
LEFT outer join patientadministration.patientname pnGiv with (nolock)
on pr.PatientRecordId=pnGiv.PatientRecordId
join patientadministration.patientnamepart pnpGiv with (nolock)
on pnGiv.PatientNameID=pnpGiv.PatientNameID
join dbmVCDRData.vocabulary.CodeSystemConcept ptcscGiv with (nolock)
on pnpGiv.PartTypeID=ptcscGiv.cscid and ptcscGiv.ConceptCode='GIV'
LEFT outer join patientadministration.patientname pnFAM with (nolock)
on pr.PatientRecordId=pnFAM.PatientRecordId
join patientadministration.patientnamepart pnpFam with (nolock)
on pnFAM.PatientNameID=pnpFAM.PatientNameID
join dbmVCDRData.vocabulary.CodeSystemConcept ptcscFAM with (nolock)
on pnpFAM.PartTypeID=ptcscFAM.cscid and ptcscFam.ConceptCode='Fam'
LEFT outer join
(select PAa.PatientRecordID
,paa.PostalAddressID
,PA1.Address
,PA2.City
,PA3.State,PA4.Country
,PA5.Zip
from dbmvcdrData.PatientAdministration.PatientRecord pa
LEFT outer join dbmVCDRData.PatientAdministration.PostalAddress paa
on pa.PatientRecordID=paa.PatientRecordID
left outer join (Select
ap.PostalAddressID
,isnull(ap.value,pacd2.Designation) Address
from patientadministration.AddressPart ap
left join dbmVCDRData.vocabulary.CodeSystemConcept pacsc
on ap.PartTypeID=pacsc.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd
on ap.PartTypeID=pacd.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd2
on ap.valueCodeID=pacd2.CSCID
where pacsc.conceptcode='SAL')PA1
on paa.PostalAddressID=PA1.PostalAddressID
Left outer join (Select
ap.PostalAddressID
,isnull(ap.value,pacd2.Designation) CITY
from patientadministration.AddressPart ap
left join dbmVCDRData.vocabulary.CodeSystemConcept pacsc
on ap.PartTypeID=pacsc.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd
on ap.PartTypeID=pacd.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd2
on ap.valueCodeID=pacd2.CSCID
where pacsc.conceptcode='CTY') PA2
on paa.PostalAddressID=PA2.PostalAddressID
Left outer join (Select
ap.PostalAddressID
,isnull(ap.value,pacd2.Designation) State
from patientadministration.AddressPart ap
left join dbmVCDRData.vocabulary.CodeSystemConcept pacsc
on ap.PartTypeID=pacsc.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd
on ap.PartTypeID=pacd.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd2
on ap.valueCodeID=pacd2.CSCID
where pacsc.conceptcode='STA') PA3
on paa.PostalAddressID=PA3.PostalAddressID
LEFT outer Join
(Select
ap.PostalAddressID
,isnull(ap.value,pacd2.Designation) Country
from patientadministration.AddressPart ap
left join dbmVCDRData.vocabulary.CodeSystemConcept pacsc
on ap.PartTypeID=pacsc.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd
on ap.PartTypeID=pacd.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd2
on ap.valueCodeID=pacd2.CSCID
where pacsc.conceptcode='CNT' ) PA4
On paa.PostalAddressID=pa4.PostalAddressID
LEFT outer Join
(Select
ap.PostalAddressID
,isnull(ap.value,pacd2.Designation) ZIP
from patientadministration.AddressPart ap
left join dbmVCDRData.vocabulary.CodeSystemConcept pacsc
on ap.PartTypeID=pacsc.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd
on ap.PartTypeID=pacd.cscid
left join dbmVCDRData.vocabulary.ConceptDesignation pacd2
on ap.valueCodeID=pacd2.CSCID
where pacsc.conceptcode='ZIP' ) PA5
On paa.PostalAddressID=pa5.PostalAddressID
) PAAS
on pr.PatientRecordID=PAAS.PatientRecordID
left outer join
patientadministration.PatientTelecom pt
on pr.PatientRecordId=pt.PatientRecordId
left outer join
dbmVCDRData.vocabulary.CodeSystemConcept ptUseCode
on pt.UseId=ptUseCode.cscid
--where
-- and (ptUseCode.conceptcode='H' or ptUseCode.conceptCode is null)
--Select * from #PatientForDemoRepositiory_Raw for xml raw, ROOT('Patients')
--select * from #ActsCount
--Select *
--from
-- #PatientForDemoRepositiory_Raw pat
-- left join
-- #ActsCount on pat.PatientRecordID=#ActsCount.PatientRecordId
-- left join
-- dbmDILMessagesArchive.dbo.ArchMessage arc
-- on arc.BTSInterchangeID=pat.InterchangeID
-- where arc.ArchTime>@startTime
--and arc.MessageType='ADT' and arc.MessageTriggerEvent!='A60'
--and pat.PrimaryIDExtension='105707782'
-- order by arc.ArchTime desc
/*
where
-- #ActsCount.EncountersCount>0
-- and #ActsCount.AllergiesCount>5
-- and #ActsCount.DocumentsCount>0
--and #ActsCount.LabEventsCount>0
-- and #ActsCount.ImagingCount>0
-- and #ActsCount.DiagnosesCount>0
--and #ActsCount.ProblemsCount>0
--and #ActsCount.ImmunizationsCount>0
--and #ActsCount.MedicationsCount>0
--and #ActsCount.ProceduresCount>0
pat.PatientRecordID in
(
select c.PatientRecordID from dbmVCDRData.Condition.condition c
join dbmVCDRData.PatientAdministration.PatientIdentifier pi
on c.PatientRecordID=pi.PatientRecordID
where pi.Root like '%.200.%'
group by c.PatientRecordID
)
*/
go