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