Incremental Update of Data with Update Field

jwbadger3 · ‎2015-07-28

Hey guys, (sorry for the duplicate entry. I realized I created the wrong type of entry when I couldn't add the attachment.)

I'm wondering who here does incremental updates from Clarity/Cogito - especially for tables in Epic that don't have modified date/time stamps to go off of (EX: PATIENT). Have folks found a way to do incremental updates to data without this or are you just completing full reloads of those tables?

Here's a quick example of the QVD incremental load I'm doing with tables that have an update field. (Turns out PATIENT did have this)

Gethyn · ‎2015-09-10

I have been tending to write stored procedures in the Clarity database and then calling the stored procedure from QlikView.

A simple example would be to show the susceptibility values for antibiotic tests on organisms detected in specimens.

USE [CLARITY];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*
=====================================
Author:      Gethyn Owen
Create date: 28/08/2015
Description: Susceptibility Tests
              For CoSurv Report
=====================================
*/
-- Parameters can be entered as absolute values eg. 31/08/2015 or relative values eg. T-1, WB-1 etc.
ALTER PROCEDURE [dbo].[SP_GO_CoSurve_SUSCEPTIBILITY] (@Start VARCHAR(50),  @End VARCHAR(50))
AS
DECLARE
      @StartDate   DATETIME,
      @EndDate    DATETIME
BEGIN
  -- convert values entered for parameters into real dates.
  SET @StartDate = EPIC_UTIL.EFN_DIN (@Start)
  SET @endDate = EPIC_UTIL.EFN_DIN (@End)
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

SELECT DISTINCT
     (((RES_MICRO_SUSC.SPECIMEN_ID + '|') + (CASE
                                                WHEN CLARITY_ORGANISM.ORGANISM_ID IS NULL
                                                THEN
                                                    'S' + CAST (CLARITY_COMPONENT.COMPONENT_ID AS VARCHAR (18))
                                                ELSE CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (18))
                                             END)) + '|')/* + (CASE WHEN RES_MICRO_SUSC.RESULT_ID IS NULL THEN '' ELSE RES_MICRO_SUSC.RESULT_ID END)*/
                                                AS SORT,
     CLARITY_COMPONENT.ABBREVIATION AS [Antibiotic Code],
     CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) AS LastVerifiedDate,
     CASE
        WHEN ZC_SUSCEPT.ABBR IS NOT NULL
        THEN
            ZC_SUSCEPT.ABBR
        ELSE
            CASE
                WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
                THEN
                    'U'
                WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
                THEN
                    ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
                WHEN ZC_SUSCEPT.ABBR IS NULL
                THEN
                    ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
                ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
            END
     END
        AS RES_MICRO_SUSC_group
FROM CLARITY.dbo.RES_MICRO_CULTURE RES_MICRO_CULTURE
     LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM
        ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)
     LEFT OUTER JOIN CLARITY.dbo.RES_DB_MAIN RES_DB_MAIN
        ON (RES_DB_MAIN.RESULT_ID = RES_MICRO_CULTURE.RESULT_ID)
     RIGHT OUTER JOIN CLARITY.dbo.RES_MICRO_SUSC RES_MICRO_SUSC
        ON (RES_MICRO_SUSC.CULTURE_ID = RES_MICRO_CULTURE.RESULT_ID)
        AND (RES_MICRO_SUSC.UNIQUE_ORGANISM_ID = RES_MICRO_CULTURE.LINE)
     LEFT OUTER JOIN CLARITY.dbo.V_LAB_RESULTS V_LAB_RESULTS
        ON (RES_DB_MAIN.RESULT_ID = V_LAB_RESULTS.RESULT_ID)
     LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT
        ON (RES_MICRO_SUSC.SUSC_ANTIBIOTIC_ID = CLARITY_COMPONENT.COMPONENT_ID)
     LEFT OUTER JOIN CLARITY.dbo.METHOD_INFO METHOD_INFO
        ON (RES_MICRO_SUSC.SUSC_TEST_METH_ID = METHOD_INFO.METHOD_ID)
     RIGHT OUTER JOIN CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN
        ON (RES_MICRO_SUSC.SPECIMEN_ID = SPEC_DB_MAIN.SPECIMEN_ID)
     LEFT OUTER JOIN CLARITY.dbo.TEST_MSTR_DB_MAIN TEST_MSTR_DB_MAIN
        ON (RES_MICRO_SUSC.SUSC_TEST_ID = TEST_MSTR_DB_MAIN.TEST_ID)
     LEFT OUTER JOIN CLARITY.dbo.ZC_SUSCEPT ZC_SUSCEPT
        ON (RES_MICRO_SUSC.SUSC_INTERPRETATN_C = ZC_SUSCEPT.SUSCEPT_C)
     LEFT OUTER JOIN CLARITY.dbo.RTYPE_DB_MAIN RTYPE_DB_MAIN
        ON (RES_MICRO_SUSC.RESULT_TYPE_ID = RTYPE_DB_MAIN.RESULT_TYPE_ID)
WHERE (RES_DB_MAIN.RES_VAL_STATUS_C = 9
      AND RES_MICRO_SUSC.CULTURE_ID IS NOT NULL
      and CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate
      AND RES_MICRO_SUSC.RESULT_ID Is NOT NULL)
      AND CLARITY_COMPONENT.ABBREVIATION <> 'ADNR'
      AND CASE
              WHEN ZC_SUSCEPT.ABBR IS NOT NULL
              THEN
                  ZC_SUSCEPT.ABBR
              ELSE
                  CASE
                      WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
                      THEN
                          'U'
                      WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
                      THEN
                          ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
                      WHEN ZC_SUSCEPT.ABBR IS NULL
                      THEN
                          ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
                      ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
                  END
          END is not null
END
GO

In my QlikView script I would then have the following:

Susc:
LOAD *
FROM
[\\QlikView1\QVDocumentsE\Labs\Live\Susc.qvd]
(qvd);
Store Susc into \\QlikView1\QVDocumentsE\Labs\Live\Susc_Backup.qvd;

Susc:
SQL exec CLARITY.dbo.SP_GO_CoSurv_SUSCEPTIBILITY 'T-1', 'T-1';
Store Susc into \\QlikView1\QVDocumentsE\Labs\Live\Susc.qvd;

The first time I refresh the report I would comment out the first part of the QlikView script, lines 01 to 06 above and just run the part whick executes the stored procedure with the parameters set to a date range which covers from as far back as you need up to date, i.e. YB-1 to T-1.

After the report has refreshed I would uncomment lines 01 to 06 and change the parameters to T-1 and T-1 and set the QlikView server to refresh the report daily. The line which creates Susc_Backup.qvd is just to make it easier to fix the date if the load ever goes wrong one day.

If you wanted to, you could then have the script drop the table and then reload it using the Max LastVerifiedDate field grouped by the Sort and Antibiotic Code fields to make sure that you only show the latest values.

For this particular report I would also have a second stored procedure ( shown below) to load the patient samples with detected organisms which are reportable to the public health authority. This can then be loaded into the same Qlikview app and the resulting table joined to the first by the Sort column.

USE [CLARITY];

GO

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

/*

=====================================

Author: Gethyn Owen

Create date: 25/08/2015

Description: Patient Samples & Tests

For CoSurv Report

=====================================

*/

CREATE PROCEDURE [dbo].[SP_GO_CoSurve_PATIENT_SAMPLES] (@Start VARCHAR(50), @End VARCHAR(50))

AS

DECLARE

@StartDate DATETIME,

@EndDate DATETIME

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET @StartDate = EPIC_UTIL.EFN_DIN (@Start)

SET @endDate = EPIC_UTIL.EFN_DIN (@End)

/* ░▒▓ Check for existing temp tables ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

IF OBJECT_ID('TEMPDB..#Samples') IS NOT NULL

DROP TABLE #Samples;

IF OBJECT_ID('TEMPDB..#VLABRES') IS NOT NULL

DROP TABLE #VLABRES;

IF OBJECT_ID('TEMPDB..#ORDERPROC') IS NOT NULL

DROP TABLE #ORDERPROC;

IF OBJECT_ID('TEMPDB..#RESCOMP') IS NOT NULL

DROP TABLE #RESCOMP;

IF OBJECT_ID('TEMPDB..#RESMICROCULTURE') IS NOT NULL

DROP TABLE #RESMICROCULTURE;

IF OBJECT_ID('TEMPDB..#REQDBMAIN') IS NOT NULL

DROP TABLE #REQDBMAIN;

IF OBJECT_ID('TEMPDB..#RESDBMAIN') IS NOT NULL

DROP TABLE #RESDBMAIN;

IF OBJECT_ID('TEMPDB..#CDiff') IS NOT NULL

DROP TABLE #CDiff;

IF OBJECT_ID('TEMPDB..#CCG') IS NOT NULL

DROP TABLE #CCG;

IF OBJECT_ID('TEMPDB..#CCGResidence') IS NOT NULL

DROP TABLE #CCGResidence;

IF OBJECT_ID('TEMPDB..#Current_CCG') IS NOT NULL

DROP TABLE #Current_CCG;

/* ░▒▓ Create temp tables ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

SET NOCOUNT ON

SELECT * INTO #VLABRES

FROM CLARITY.dbo.V_LAB_RESULTS V_LAB_RESULTS

WHERE -- V_LAB_RESULTS.RESULT_STATUS_C = 9

--AND V_LAB_RESULTS.LAST_VERIFIED_DATETIME >= '01/06/2015 00:00:00'

-- CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.ORDERED_DATETIME, 112)) Between @Start and @End

CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate

AND V_LAB_RESULTS.ORDER_ID is not null

-- AND V_LAB_RESULTS.EPT_PATIENT_ID IS NOT NULL

-- AND V_LAB_RESULTS.SUBMITTER_ID IS NULL

SELECT CLARITY.dbo.ORDER_PROC.* INTO #ORDERPROC

FROM CLARITY.dbo.ORDER_PROC INNER JOIN #VLABRES ON #VLABRES.ORDER_ID = CLARITY.dbo.ORDER_PROC.ORDER_PROC_ID

SET NOCOUNT ON

SELECT CLARITY.dbo.ORDER_PROC_3.* INTO #ORDERPROC3

FROM CLARITY.dbo.ORDER_PROC_3 INNER JOIN #VLABRES ON #VLABRES.ORDER_ID = CLARITY.dbo.ORDER_PROC_3.ORDER_ID

SET NOCOUNT ON

select REQ_DB_MAIN.* INTO #REQDBMAIN

from CLARITY.dbo.REQ_DB_MAIN REQ_DB_MAIN

INNER JOIN #VLABRES ON #VLABRES.REQUISITION_ID = REQ_DB_MAIN.REQUISITION_ID

where (REQ_DB_MAIN.REQ_SUBMITTER_ID not in (2367, 374, 1040, 1077, 2404, 1391, 762, 2366, 1039, 2405, 1078, 2314, 263, 3085, 3171, 1257, 1261, 2355, 362, 1028)

or REQ_DB_MAIN.REQ_SUBMITTER_ID is null)

SET NOCOUNT ON

select RES_DB_MAIN.* INTO #RESDBMAIN

from CLARITY.dbo.RES_DB_MAIN RES_DB_MAIN

INNER JOIN #VLABRES ON #VLABRES.RESULT_ID = RES_DB_MAIN.RESULT_ID

where RES_DB_MAIN.RES_VAL_STATUS_C = 9

SET NOCOUNT ON

SELECT --DISTINCT

SPEC_DB_MAIN.SPECIMEN_ID,

ORDER_PROC.SPECIMEN_TYPE_C AS SPECIMEN_TYPE_C_S1810885,

ZC_SPECIMEN_TYPE.[NAME] as SpecimenType_S1810885,

CASE

WHEN CLARITY_ORGANISM.ORGANISM_ID IS NULL

THEN

'S' + CAST(CLARITY_COMPONENT.COMPONENT_ID AS VarChar (18))

ELSE

CAST(CLARITY_ORGANISM.ORGANISM_ID AS VarChar (18))

END

as ORGANISM_ID_S1810885,

CLARITY_ORGANISM.ORGANISM_ID AS OrigOrgID_S1810885,

RES_COMPONENTS.RESULT_ID AS RESULT_ID_S1810885,

RES_COMPONENTS.COMPONENT_ID AS COMPONENT_ID_S1810885,

RES_COMPONENTS.COMPONENT_VALUE AS COMPONENT_VALUE_S1810885,

RES_COMPONENTS.RES_SPECIMEN_ID AS RES_SPECIMEN_ID_S1810885,

SPEC_TECH_FLAGS.SPEC_FLAGS_C AS SPEC_FLAGS_C_S1810885,

SPEC_DB_MAIN.SPEC_NUMBER_LN1 AS SPEC_NUMBER_LN1_S1810885

INTO #CDiff

FROM CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN

LEFT OUTER JOIN RES_MICRO_CULTURE

ON (SPEC_DB_MAIN.SPECIMEN_ID = RES_MICRO_CULTURE.SPECIMEN_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM

ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)

LEFT OUTER JOIN RES_COMPONENTS

ON (SPEC_DB_MAIN.SPECIMEN_ID = RES_COMPONENTS.RES_SPECIMEN_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT

ON (RES_COMPONENTS.COMPONENT_ID = CLARITY_COMPONENT.COMPONENT_ID)

LEFT OUTER JOIN CLARITY.dbo.SPEC_TECH_FLAGS SPEC_TECH_FLAGS

ON (SPEC_DB_MAIN.SPECIMEN_ID = SPEC_TECH_FLAGS.SPECIMEN_ID)

LEFT OUTER JOIN #VLABRES V_LAB_RESULTS

ON (SPEC_DB_MAIN.SPECIMEN_ID = V_LAB_RESULTS.SPECIMEN_ID)

LEFT OUTER JOIN #ORDERPROC ORDER_PROC

on (V_LAB_RESULTS.ORDER_ID = ORDER_PROC.ORDER_PROC_ID)

RIGHT OUTER JOIN CLARITY.dbo.ORDER_PROC_3 ORDER_PROC_3

ON (ORDER_PROC_3.ORDER_ID = ORDER_PROC.ORDER_PROC_ID)

RIGHT OUTER JOIN CLARITY.dbo.SPEC_TEST_REL SPEC_TEST_REL

ON (SPEC_TEST_REL.SPEC_TST_ORDER_ID = ORDER_PROC_3.ORDER_ID)

LEFT OUTER JOIN CLARITY.dbo.ZC_SPECIMEN_TYPE ZC_SPECIMEN_TYPE

ON (ORDER_PROC.SPECIMEN_TYPE_C = ZC_SPECIMEN_TYPE.SPECIMEN_TYPE_C)

LEFT OUTER JOIN #RESDBMAIN RES_DB_MAIN

ON (RES_DB_MAIN.RES_SPECIMEN_ID = SPEC_TEST_REL.SPECIMEN_ID)

AND (RES_DB_MAIN.TEST_LINE = SPEC_TEST_REL.LINE)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_DEP CLARITY_DEP

ON (SPEC_TEST_REL.ACC_FROM_DEP_ID = CLARITY_DEP.DEPARTMENT_ID)

WHERE --SPEC_TECH_FLAGS.SPEC_FLAGS_C = 29 -- CDR Flag

(RES_COMPONENTS.COMPONENT_ID = '1810885' AND RES_COMPONENTS.COMPONENT_VALUE = '1')--C difficile Toxin -- Positive

--or ((RES_COMPONENTS.COMPONENT_ID = '1810885')--C difficile Toxin

-- and SPEC_TECH_FLAGS.SPEC_FLAGS_C = 29)

-- AND CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate

AND CLARITY_DEP.SERV_AREA_ID = 20 -- CUH

-- AND RES_DB_MAIN.RES_VAL_STATUS_C = 9 -- Verified

SET NOCOUNT ON

SELECT RES_COMPONENTS.* INTO #RESCOMP

FROM CLARITY.dbo.RES_COMPONENTS RES_COMPONENTS

LEFT OUTER JOIN #CDiff CDiff

ON (RES_COMPONENTS.RES_SPECIMEN_ID = CDiff.SPECIMEN_ID

AND RES_COMPONENTS.COMPONENT_ID = '2740')

WHERE ((RES_COMPONENTS.COMPONENT_ID = '5343' AND RES_COMPONENTS.COMPONENT_VALUE = '1') -- CHLAMYDIA TRACHOMATIS RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '3578' AND RES_COMPONENTS.COMPONENT_VALUE = '42') -- HEPATITIS A IGM -- Reactive

OR (RES_COMPONENTS.COMPONENT_ID = '3580' AND RES_COMPONENTS.COMPONENT_VALUE = '46') -- HEPATITIS B POLYCLONAL -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '3581' AND RES_COMPONENTS.COMPONENT_VALUE = '46') -- HEPATITIS C Ag/Ab -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '3575' AND RES_COMPONENTS.COMPONENT_VALUE = '46') -- HEPATITIS C Ab VIDAS -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '3585' AND RES_COMPONENTS.COMPONENT_VALUE = '46') -- HEPATITIS E IGM -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '3593' AND RES_COMPONENTS.COMPONENT_VALUE = '43') -- MEASLES IGM -- Positive

OR (RES_COMPONENTS.COMPONENT_ID = '6397' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- MEASLES PCR -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1510340' AND RES_COMPONENTS.COMPONENT_VALUE = '46')-- CMV IgM VIDAS -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1740009' AND RES_COMPONENTS.COMPONENT_VALUE = '46')-- EBV VCA IgM -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '2737' AND RES_COMPONENTS.COMPONENT_VALUE = '51') -- Cryptosporidium Oocysts -- Seen

OR (RES_COMPONENTS.COMPONENT_ID = '3589' AND RES_COMPONENTS.COMPONENT_VALUE in ('1',-- HIV IMMUNOCOMB -- HIV 1 REACTIVE

'2', -- HIV IMMUNOCOMB -- HIV 2 REACTIVE

'3'))-- HIV IMMUNOCOMB -- HIV 1 & 2 REACTIVE

OR (RES_COMPONENTS.COMPONENT_ID = '3591' AND RES_COMPONENTS.COMPONENT_VALUE = '46') -- HTLV -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1628750' AND RES_COMPONENTS.COMPONENT_VALUE = '46')-- PARVO B19 IgM -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '524' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- JC PCR -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '461' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- BK PCR -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '515' AND RES_COMPONENTS.COMPONENT_VALUE = '6') -- HSV DNA CONFIRMATION -- Confirmed

OR (RES_COMPONENTS.COMPONENT_ID = '3583' AND RES_COMPONENTS.COMPONENT_VALUE = '43') -- HEPATITIS D (DELTA) ANTIBODY -- Positive

OR (RES_COMPONENTS.COMPONENT_ID = '5685' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- INFLUENZA A GENERIC -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '539' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- INFLUENZA B RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1628696' AND RES_COMPONENTS.COMPONENT_VALUE = '43')-- MUMPS IGM -- Positive

OR (RES_COMPONENTS.COMPONENT_ID = '532' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- NOROVIRUS RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1613304' AND RES_COMPONENTS.COMPONENT_VALUE = '42')-- RUBELLA IGM (LIAISON), QUANTITATIVE --Reactive

OR (RES_COMPONENTS.COMPONENT_ID = '3616' AND RES_COMPONENTS.COMPONENT_VALUE = '43') -- VARICELLA ZOSTER VIRUS IGM -- Positive

OR ((RES_COMPONENTS.COMPONENT_ID = '2740' AND RES_COMPONENTS.COMPONENT_VALUE = '1') -- GDH -- Positive

AND (CDiff.COMPONENT_ID_S1810885 = '1810885' AND CDiff.COMPONENT_VALUE_S1810885 = '1'))--C difficile Toxin -- Positive

OR (RES_COMPONENTS.COMPONENT_ID = '315' AND RES_COMPONENTS.COMPONENT_VALUE = '1001') -- MP PH 7.2 FILM REVIEW -- Parasites seen

OR (RES_COMPONENTS.COMPONENT_ID = '318' AND RES_COMPONENTS.COMPONENT_VALUE = '1001') -- MP THICK FILM PH 7.2 -- Parasites seen

OR (RES_COMPONENTS.COMPONENT_ID = '541' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- PARAINFLUENZA VIRUS RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '543' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- RHINOVIRUS RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '540' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- RSV RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '475' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- ENTEROVIRUS RNA -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '542' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- ADENOVIRUS DNA, RESPIRATORY -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1511242' AND RES_COMPONENTS.COMPONENT_VALUE = '4')-- ADENOVIRUS / ROTAVIRUS -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '1510071' AND RES_COMPONENTS.COMPONENT_VALUE = '4')-- ADENOVIRUS / ROTAVIRUS -- Detected

OR (RES_COMPONENTS.COMPONENT_ID = '538' AND RES_COMPONENTS.COMPONENT_VALUE = '4') -- PNEUMOCYSTIS PCR -- Detected

and (RES_COMPONENTS.COMPONENT_RESULT IS NOT NULL

and RES_COMPONENTS.COMPONENT_RESULT <> 'Negative'

and RES_COMPONENTS.COMPONENT_RESULT <> 'Not Detected'

and RES_COMPONENTS.COMPONENT_RESULT <> 'No significant growth'

and RES_COMPONENTS.COMPONENT_RESULT <> 'Organisms NOT seen'))

SET NOCOUNT ON

SELECT RES_MICRO_CULTURE.* INTO #RESMICROCULTURE

FROM CLARITY.dbo.RES_MICRO_CULTURE RES_MICRO_CULTURE

LEFT OUTER JOIN SPEC_TEST_REL ON SPEC_TEST_REL.SPECIMEN_ID = RES_MICRO_CULTURE.SPECIMEN_ID

LEFT OUTER JOIN #ORDERPROC ORDER_PROC ON ORDER_PROC.ORDER_PROC_ID = SPEC_TEST_REL.SPEC_TST_ORDER_ID

WHERE (RES_MICRO_CULTURE.CULT_ORG_ID IN (--Bacteria:

'656', -- Bacillus anthracis

'689', -- Bordetella pertussis

'127', -- Burkholderia mallei

'128', -- Burkholderia pseudomallei

'755', -- Clostridium botulinum

'777', -- Clostridium tetani

'794', -- Corynebacterium diphtheriae

'805', -- Corynebacterium ulcerans

'215', -- Francisella tularenis

'280', -- Listeria monocytogenes

'984', -- Mycobacterium tuberculosis

'311', -- Neisseria meningitidis

'309', -- Neisseria gonorrhoeae

-- '202', -- Verocytotoxigenic Escherichia coli

'414', '1329',-- Vibrio cholerae

'424', -- Yersinia pestis

-- Protozoa:

'440', '591', -- Acanthamobae

'839', '840', -- Entamoeba histolytica

'501', '888', -- Giardia lamblia

-- Helminths:

'1342',-- Hookworm

'567', '1289',-- Strongyloides

-- Fungi:

'494', '864') -- Exophalia spp

OR (RES_MICRO_CULTURE.CULT_ORG_ID in ('202',-- Escherichia coli spp

'857')-- Escherichia coli O157

AND ORDER_PROC.SPECIMEN_TYPE_C = 54) -- Stool

OR (RES_MICRO_CULTURE.CULT_ORG_ID in ('238', -- Haemophilus influenzae

'4', -- Staphylococcus aureus

'36') -- Streptococcus pneumoniae

AND ORDER_PROC.SPECIMEN_TYPE_C in (307,-- Broncho-alveolar lavage

30))-- Sputum

OR RES_MICRO_CULTURE.CULTURE_GENUS_C in (-- Bacteria:

1004, -- Actinomyces spp

1013, -- Brucella spp

1017, -- Campylobacter spp

1034, -- Erysipelothrix spp

1171, -- Legionella spp

1057, -- Listeria spp

1174, -- Mycobacterium spp

1188, -- Nocardia spp

1083, -- Salmonella spp

1086, -- Shigella spp

1092, -- Vibrio spp

1094, -- Yersinia spp

-- Protozoa:

1136, -- Cyclospora

1133, -- Cryptosporidium

1172, -- Leishmania

1185, -- Naegleria

1233, -- Trichomonas

-- Helminths:

1129, -- Clonorchis

1143, -- Diphyllobothrium

1167, -- Hymenolepsis

1144, -- Echinococcus

1157, -- Fasciola

1222, -- Schistosoma

1230, -- Taenia

1232, -- Toxocara

1237, -- Trichuris

-- Fungi:

1100, -- Absidia

1102, -- Acremonium

1131, -- Coccidiodes

1132, -- Cryptococcus

1151, -- Epidermophyton

1160, -- Fusarium

1166, -- Histoplasma

1180, -- Microsporum

1204, -- Phialophora

1215, -- Rhizopus

1217, -- Rhodotolorula

1220, -- Saccharomyces

1221, -- Scedosporium

1223, -- Scopulatiopsis

1226, -- Sporothrix

1234, -- Trichophyton

1235))-- Trichosporon

OR ((RES_MICRO_CULTURE.CULT_ORG_ID IN (--Bacteria:

'659', -- Bacillus cereus

'768', -- Clostridium perfringens

'202', -- Escherichia coli spp

'238', -- Haemophilus influenzae

'4', -- Staphylococcus aureus

'405', -- Stenotrophomonas maltophilia

'36', -- Streptococcus pneumoniae

'63', '1283')-- Streptococcus pyogenes

OR RES_MICRO_CULTURE.CULTURE_GENUS_C in (-- Bacteria:

1002, -- Acinetobacter

1006, -- Aeromonas

1024, -- Citrobacter

1130, -- Clostridium

1032, -- Enterobacter

1033, -- Enterococcus

1051, -- Klebsiella

1140, -- Morganella

1070, -- Pasteurella

1073, -- Plesiomonas

1074, -- Proteus

1075, -- Providencia

1076, -- Pseudomonas

1084, -- Serratia

2, -- Streptococcus

-- Fungi:

1114, -- Aspegillus

1121))-- Candida

AND (ORDER_PROC.SPECIMEN_TYPE_C IN (1, -- Amniotic Fluid

3, -- Blood

4, -- Arterial Blood

9, -- Cerebrospinal Fluid

19, -- Tissue

22, -- Body Fluid

29, -- Bone Marrow

93, -- Synovial Fluid

95, -- Bone Marrow Aspirate

301, -- Aqueous humor

302, -- Aspirate

322, -- Fluid

363, -- Tissue - Biopsy

377, -- Vitreous humor

399, -- Lymph node

403)))

SET NOCOUNT ON

SELECT --DISTINCT

RES_MICRO_SUSC.SPECIMEN_ID as SpecID,

V_LAB_RESULTS.TYPE_OF_DATA_C,

V_LAB_RESULTS.RESULT_ID,

CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) as CALENDAR_DT,

(((V_LAB_RESULTS.SPECIMEN_ID + '|')

+ (CASE

WHEN CLARITY_ORGANISM.ORGANISM_ID IS NULL

THEN

'S'

+ CAST (CLARITY_COMPONENT.COMPONENT_ID AS VARCHAR (18))

ELSE CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (18))

END))

+ '|')/* + (CASE WHEN RES_MICRO_SUSC.RESULT_ID IS NULL THEN '' ELSE RES_MICRO_SUSC.RESULT_ID END)*/

AS SORT,

REQ_DB_MAIN.REQ_SUBMITTER_ID AS [Submitter Location],

SPEC_TEST_REL.SPEC_TST_LAB_ID AS [Reporting Laboratory],

SPEC_TEST_REL.LAB_ID AS [Source Laboratory],

PATIENT.PAT_MRN_ID AS [Hospital Patient Number],

PATIENT.PAT_LAST_NAME AS [Patient Surname],

PATIENT.PAT_FIRST_NAME AS [Patient Forename],

ISNULL (

CASE

WHEN LEN (REPLACE (REPLACE (PATIENT.SSN, ' ', ''), '-', '')) <>

10

THEN

NULL

ELSE

REPLACE (REPLACE (PATIENT.SSN, ' ', ''), '-', '')

END, '')

AS [Patient NHS Number],

IDENTITY_ID.IDENTITY_ID AS TQuestNHS,

IDENTITY_ID.IDENTITY_TYPE_ID,

PATIENT.BIRTH_DATE AS [Patient Date of Birth],

PATIENT.ADD_LINE_1 AS [Patient Address],

PATIENT.ZIP AS [Patient Postcode],

PATIENT_3.OCCUPATION AS [Patient Occupation],

PATIENT.PAT_STATUS_C,

CONVERT (

DATETIME,

CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112))

AS LastVerifiedDate,

CASE

WHEN RES_MICRO_CULTURE.CULT_ORG_ID IS NULL

THEN

'*Unspecified ' + 'Organism'

WHEN CLARITY_ORGANISM.ORGANISM_ID IS NULL

THEN

((('*Unknown ' + 'Organism') + ' [')

+ CAST (RES_MICRO_CULTURE.CULT_ORG_ID AS VARCHAR (255)))

+ ']'

WHEN CLARITY_ORGANISM.[NAME] IS NULL

THEN

((('*Unnamed ' + 'Organism') + ' [')

+ CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (255)))

+ ']'

ELSE

((CAST (CLARITY_ORGANISM.[NAME] AS VARCHAR (255)) + ' [')

+ CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (255)))

+ ']'

END

AS org_group,

CASE

WHEN CLARITY_ORGANISM.ORGANISM_ID IS NULL

THEN

'S' + CAST(CLARITY_COMPONENT.COMPONENT_ID AS VarChar (18))

ELSE

CAST(CLARITY_ORGANISM.ORGANISM_ID AS VarChar (18))

END

as ORGANISM_ID,

V_LAB_RESULTS.SPECIMEN_ID,

RES_DB_MAIN.RES_VAL_STATUS_C,

V_LAB_RESULTS.RQG_PATIENT_ID,

V_LAB_RESULTS.EPT_PATIENT_ID,

RQG_DB_MAIN.RQG_GROUPER_NAME,

SPEC_DB_MAIN.SPEC_NUMBER_LN1 AS SpecimenNumber,

SPEC_DB_MAIN.REQ_ID,

SPEC_TECH_FLAGS.SPEC_FLAGS_C,

ORDER_PROC.SPECIMEN_TYPE_C,

ORDER_PROC.SPECIMEN_SOURCE_C,

Case

when ORDER_PROC.SPECIMEN_TYPE_C is null

then

''

ELSE

Convert(VarChar(18), ORDER_PROC.SPECIMEN_TYPE_C)

END

+ '$' +

Case

when ORDER_PROC.SPECIMEN_SOURCE_C is null

then

''

ELSE

Convert(VarChar(18), ORDER_PROC.SPECIMEN_SOURCE_C)

END

AS SpecimenTypeSource,

ZC_SPECIMEN_TYPE.[NAME] AS SpecimenType,

ZC_SPECIMEN_SOURCE.[NAME],

CASE

WHEN RES_DB_MAIN.RESULTING_LAB_ID IS NULL

THEN

'*Unspecified ' + 'Laboratory'

WHEN LAB_PROFILE.LAB_ID IS NULL

THEN

((('*Unknown ' + 'Laboratory') + ' [') +

CAST (RES_DB_MAIN.RESULTING_LAB_ID AS VARCHAR (255))) + ']'

WHEN LAB_PROFILE.LAB_NAME IS NULL

THEN

((('*Unnamed ' + 'Laboratory') + ' [') +

CAST (LAB_PROFILE.LAB_ID AS VARCHAR (255))) + ']'

ELSE

((CAST (LAB_PROFILE.LAB_NAME AS VARCHAR (255)) + ' [') +

CAST (LAB_PROFILE.LAB_ID AS VARCHAR (255))) + ']'

END

AS lab_group,

CASE

WHEN SPEC_DB_MAIN.REQ_SMT_ID IS NULL

THEN

'*Unspecified ' + 'Submitter'

WHEN LAB_SMT_NOADD.RECORD_ID IS NULL

THEN

((('*Unknown ' + 'Submitter') + ' [') +

CAST (SPEC_DB_MAIN.REQ_SMT_ID AS VARCHAR (255))) + ']'

WHEN LAB_SMT_NOADD.RECORD_NAME IS NULL

THEN

((('*Unnamed ' + 'Submitter') + ' [') +

CAST (LAB_SMT_NOADD.RECORD_ID AS VARCHAR (255))) + ']'

ELSE

((CAST (LAB_SMT_NOADD.RECORD_NAME AS VARCHAR (255)) + ' [') +

CAST (LAB_SMT_NOADD.RECORD_ID AS VARCHAR (255))) + ']'

END

AS smt_group,

CASE

WHEN SPEC_DB_MAIN.SPEC_COLL_DEPT_ID IS NOT NULL

THEN

CASE

WHEN SPEC_DB_MAIN.SPEC_COLL_DEPT_ID IS NULL

THEN

'*Unspecified ' + 'Department'

WHEN CLARITY_DEP_coll.DEPARTMENT_ID IS NULL

THEN

((('*Unknown ' + 'Department') + ' [') +

CAST (SPEC_DB_MAIN.SPEC_COLL_DEPT_ID AS VARCHAR (255))) + ']'

WHEN CLARITY_DEP_coll.DEPARTMENT_NAME IS NULL

THEN

((('*Unnamed ' + 'Department') + ' [') +

CAST (CLARITY_DEP_coll.DEPARTMENT_ID AS VARCHAR (255))) + ']'

ELSE

((CAST (CLARITY_DEP_coll.DEPARTMENT_NAME AS VARCHAR (255)) + ' [') +

CAST (CLARITY_DEP_coll.DEPARTMENT_ID AS VARCHAR (255))) + ']'

END

ELSE

CASE

WHEN SPEC_TEST_REL.ACC_FROM_DEP_ID IS NULL

THEN

'*Unspecified ' + 'Department'

WHEN CLARITY_DEP.DEPARTMENT_ID IS NULL

THEN

((('*Unknown ' + 'Department') + ' [') +

CAST (SPEC_TEST_REL.ACC_FROM_DEP_ID AS VARCHAR (255))) + ']'

WHEN CLARITY_DEP.DEPARTMENT_NAME IS NULL

THEN

((('*Unnamed ' + 'Department') + ' [') +

CAST (CLARITY_DEP.DEPARTMENT_ID AS VARCHAR (255))) + ']'

ELSE

((CAST (CLARITY_DEP.DEPARTMENT_NAME AS VARCHAR (255)) + ' [') +

CAST (CLARITY_DEP.DEPARTMENT_ID AS VARCHAR (255))) + ']'

END

AS CLARITY_DEP_group,

CLARITY_DEP.DEPARTMENT_ID AS CLARITY_DEPid,

CLARITY_DEP.EXTERNAL_NAME AS CLARITY_DEP_extname,

CLARITY_DEP_coll.DEPARTMENT_ID AS CLARITY_DEPid_coll,

CLARITY_DEP_coll.EXTERNAL_NAME AS CLARITY_DEP_coll_extname,

CLARITY_DEP_1.SPECIALTY,

ORDER_PROC.PAT_ENC_CSN_ID AS csn,

ORDER_PROC.ORDER_PROC_ID,

ORDER_PROC_3.ORDERING_MODE_C,

CLARITY_DEP_1.DEPARTMENT_NAME,

SPEC_TEST_REL.SPEC_TST_ID,

HNO_NOTE_TEXT_req.NOTE_ID,

HNO_NOTE_TEXT_req.NOTE_TEXT AS RequisitionComment,

HNO_NOTE_TEXT_int.NOTE_TEXT AS InternalComment,

case

when RES_COMPONENTS.COMPONENT_ID in ('315', '318')

then

'H'+V_LAB_RESULTS.TEST_METHOD_ID

else V_LAB_RESULTS.TEST_METHOD_ID

end as TEST_METHOD_ID,

V_LAB_RESULTS.SPECIMEN_NUMBER,

SPEC_DB_MAIN.SPEC_DTM_COLLECTED,

SPEC_DB_MAIN.SPEC_DTM_RECEIVED,

LAB_SMT_NOADD.RECORD_NAME,

REQ_DB_MAIN.ORDERING_PROV_ID,

REQ_DB_MAIN.REQUISITION_NAME,

INTERFACE_TBL_VALS_ethnicity.EXT_VALUE AS Ethnicity,

INTERFACE_TBL_VALS_sex.EXT_VALUE AS Sex,

RES_DB_MAIN.RESULTING_LAB_ID,

V_LAB_RESULTS.RESULT_STATUS_C,

CLARITY_ORGANISM.ORGANISM_ID AS OrigOrgID

-- Orgs.ORGANISM_ID,

-- CDiff.ORGANISM_ID_S1810885,

-- Susc.[Antibiotic Code],

-- Susc.RES_MICRO_SUSC_group,

-- Tests.Specimen,

-- Tests.SPECIMEN_NUMBER,

-- Tests.TEST_METHOD_ID,

-- Tests.SUSC_TEST_METH_ID

-- CLARITY_COMPONENT.COMPONENT_ID

INTO #Samples

FROM #VLABRES V_LAB_RESULTS

left OUTER JOIN #RESCOMP RES_COMPONENTS

ON (V_LAB_RESULTS.RESULT_ID = RES_COMPONENTS.RESULT_ID)

LEFT OUTER JOIN #ORDERPROC ORDER_PROC

ON (V_LAB_RESULTS.ORDER_ID = ORDER_PROC.ORDER_PROC_ID)

LEFT OUTER JOIN CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN

ON (V_LAB_RESULTS.SPECIMEN_ID = SPEC_DB_MAIN.SPECIMEN_ID)

LEFT OUTER JOIN #ORDERPROC3 ORDER_PROC_3

ON (ORDER_PROC.ORDER_PROC_ID = ORDER_PROC_3.ORDER_ID)

LEFT OUTER JOIN CLARITY.dbo.PAT_ENC_HSP PAT_ENC_HSP

ON (V_LAB_RESULTS.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID)

left OUTER JOIN CLARITY.dbo.SPEC_TEST_REL SPEC_TEST_REL

ON (ORDER_PROC.ORDER_PROC_ID = SPEC_TEST_REL.SPEC_TST_ORDER_ID)

LEFT OUTER JOIN CLARITY.dbo.PATIENT PATIENT

ON (V_LAB_RESULTS.EPT_PATIENT_ID = PATIENT.PAT_ID)

LEFT OUTER JOIN CLARITY.dbo.PATIENT_3 PATIENT_3

ON (V_LAB_RESULTS.EPT_PATIENT_ID = PATIENT_3.PAT_ID)

LEFT OUTER JOIN CLARITY.dbo.IDENTITY_ID IDENTITY_ID

ON (V_LAB_RESULTS.EPT_PATIENT_ID = IDENTITY_ID.PAT_ID

and IDENTITY_ID.IDENTITY_TYPE_ID = 138)

LEFT OUTER JOIN CLARITY.dbo.INTERFACE_TBL_VALS INTERFACE_TBL_VALS_sex

ON (PATIENT.SEX_C = INTERFACE_TBL_VALS_sex.INT_VALUE

AND INTERFACE_TBL_VALS_sex.TABLE_ID = 3100008)

LEFT OUTER JOIN CLARITY.dbo.INTERFACE_TBL_VALS INTERFACE_TBL_VALS_ethnicity

ON (PATIENT.ETHNIC_GROUP_C = INTERFACE_TBL_VALS_ethnicity.INT_VALUE

AND INTERFACE_TBL_VALS_ethnicity.TABLE_ID = 3100009)

LEFT OUTER JOIN CLARITY.dbo.RQG_DB_MAIN RQG_DB_MAIN

ON (V_LAB_RESULTS.RQG_PATIENT_ID = RQG_DB_MAIN.RQG_GROUPER_ID)

LEFT OUTER JOIN #RESDBMAIN RES_DB_MAIN

ON (RES_DB_MAIN.RES_SPECIMEN_ID = SPEC_TEST_REL.SPECIMEN_ID)

AND (RES_DB_MAIN.TEST_LINE = SPEC_TEST_REL.LINE)

LEFT OUTER JOIN #REQDBMAIN REQ_DB_MAIN

ON (SPEC_DB_MAIN.REQ_ID = REQ_DB_MAIN.REQUISITION_ID)

LEFT OUTER JOIN #RESMICROCULTURE RES_MICRO_CULTURE

ON (V_LAB_RESULTS.RESULT_ID = RES_MICRO_CULTURE.RESULT_ID)

LEFT OUTER JOIN CLARITY.dbo.RES_MICRO_SUSC RES_MICRO_SUSC

ON (RES_MICRO_CULTURE.RESULT_ID = RES_MICRO_SUSC.CULTURE_ID

AND RES_MICRO_CULTURE.LINE = RES_MICRO_SUSC.UNIQUE_ORGANISM_ID)

LEFT OUTER JOIN CLARITY.dbo.TEST_MSTR_DB_MAIN TEST_MSTR_DB_MAIN

ON (RES_MICRO_SUSC.SUSC_TEST_ID = TEST_MSTR_DB_MAIN.TEST_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM

ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)

LEFT OUTER JOIN CLARITY.dbo.SPEC_TECH_FLAGS SPEC_TECH_FLAGS

ON (SPEC_DB_MAIN.SPECIMEN_ID = SPEC_TECH_FLAGS.SPECIMEN_ID)

LEFT OUTER JOIN CLARITY.dbo.LAB_SMT_NOADD LAB_SMT_NOADD

ON (SPEC_DB_MAIN.REQ_SMT_ID = LAB_SMT_NOADD.RECORD_ID)

LEFT OUTER JOIN CLARITY.dbo.ZC_SPECIMEN_SOURCE ZC_SPECIMEN_SOURCE

ON (SPEC_DB_MAIN.SPEC_SOURCE_C = ZC_SPECIMEN_SOURCE.SPECIMEN_SOURCE_C)

LEFT OUTER JOIN CLARITY.dbo.ZC_SPECIMEN_TYPE ZC_SPECIMEN_TYPE

ON (ORDER_PROC.SPECIMEN_TYPE_C = ZC_SPECIMEN_TYPE.SPECIMEN_TYPE_C)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_DEP CLARITY_DEP_coll

ON (SPEC_DB_MAIN.SPEC_COLL_DEPT_ID = CLARITY_DEP_coll.DEPARTMENT_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_DEP CLARITY_DEP

ON (SPEC_TEST_REL.ACC_FROM_DEP_ID = CLARITY_DEP.DEPARTMENT_ID)

LEFT OUTER JOIN CLARITY.dbo.HNO_NOTE_TEXT HNO_NOTE_TEXT_req

ON (REQ_DB_MAIN.REQ_COMMENT_ID = HNO_NOTE_TEXT_req.NOTE_ID)

LEFT OUTER JOIN CLARITY.dbo.HNO_NOTE_TEXT HNO_NOTE_TEXT_int

ON (REQ_DB_MAIN.INTERNAL_COMMENT_ID = HNO_NOTE_TEXT_int.NOTE_ID)

LEFT OUTER JOIN CLARITY.dbo.LAB_PROFILE LAB_PROFILE

ON (RES_DB_MAIN.RESULTING_LAB_ID = LAB_PROFILE.LAB_ID)

LEFT OUTER JOIN CLARITY.dbo.RTYPE_DB_MAIN RTYPE_DB_MAIN

ON (RES_DB_MAIN.RES_TYPE_ID = RTYPE_DB_MAIN.RESULT_TYPE_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT

ON (RES_COMPONENTS.COMPONENT_ID = CLARITY_COMPONENT.COMPONENT_ID)

LEFT OUTER JOIN CLARITY.dbo.CLARITY_DEP CLARITY_DEP_1

ON (PAT_ENC_HSP.DEPARTMENT_ID = CLARITY_DEP_1.DEPARTMENT_ID)

LEFT OUTER JOIN #CDiff CDiff

ON (SPEC_DB_MAIN.SPECIMEN_ID = CDiff.SPECIMEN_ID

AND CLARITY_COMPONENT.COMPONENT_ID = '2740')

WHERE (PATIENT_3.IS_TEST_PAT_YN <> 'Y' OR PATIENT_3.IS_TEST_PAT_YN IS NULL) AND (SPEC_TECH_FLAGS.SPEC_FLAGS_C = 29 -- CDR Flag

OR RES_COMPONENTS.COMPONENT_ID IS NOT NULL OR RES_MICRO_CULTURE.CULT_ORG_ID IS NOT NULL) -- Tissue - Fetal

--AND CLARITY_DEP.SERV_AREA_ID = 20 -- CUH

-- AND RES_DB_MAIN.RES_VAL_STATUS_C = 9 -- Verified

-- AND CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate

-- AND RES_MICRO_CULTURE.CULT_ORG_ID is not null

-- and (REQ_DB_MAIN.REQ_SUBMITTER_ID not in (2367, 374, 1040, 1077, 2404, 1391, 762, 2366, 1039, 2405, 1078, 2314, 263, 3085, 3171, 1257, 1261, 2355, 362, 1028)

-- or REQ_DB_MAIN.REQ_SUBMITTER_ID is null)

-- or (RES_COMPONENTS.COMPONENT_ID = '1810885' AND SPEC_TECH_FLAGS.SPEC_FLAGS_C = 29)

/* ░▒▓ Match Registered GP Practice and Resposible CCG to the event date. ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

SET NOCOUNT ON

SELECT distinct

ConData.REFERRAL_ID,

ConData.PAT_ENC_CSN_ID,

ConData.ENC_TYPE_C,

ConData.PAT_ID1,

ConData.CONTACT_DATE,

ConData.StartMonth,

ConData.EndMonth,

V_PMD_ODS_ORG_TO_PARENT.ChildCode AS [GP Practice Code],

V_PMD_ODS_ORG_TO_PARENT.ChildName,

V_PMD_ODS_ORG_TO_PARENT.ParentCode AS CCGRespCode,

V_PMD_ODS_ORG_TO_PARENT.ParentName AS CCGRespName

INTO #CCG

FROM

(SELECT distinct

PAT_ENC.REFERRAL_ID,

PAT_ENC.PAT_ENC_CSN_ID,

PAT_ENC.ENC_TYPE_C,

PAT_ENC.PAT_ID AS PAT_ID1,

PAT_ENC.CONTACT_DATE,

Month(@StartDate) AS StartMonth,

Month(@EndDate) AS EndMonth

FROM PAT_ENC INNER JOIN #Samples ON #Samples.EPT_PATIENT_ID = PAT_ENC.PAT_ID

WHERE

(PAT_ENC.CONTACT_DATE >= @StartDate) AND

(PAT_ENC.CONTACT_DATE <= @EndDate)) AS ConData

LEFT OUTER JOIN (select * from X_PAT_CARE_REG where dbo.X_PAT_CARE_REG.TYPE_C = 1) as X_PAT_CARE_REG

ON ConData.PAT_ID1 = X_PAT_CARE_REG.PAT_ID

LEFT OUTER JOIN V_PMD_ODS_ORG_TO_PARENT

ON X_PAT_CARE_REG.POS_ID = V_PMD_ODS_ORG_TO_PARENT.FACILITY_ID

WHERE IS_DELETED_YN IS NULL

AND ((X_PAT_CARE_REG.START_DATE < ConData.CONTACT_DATE)

AND (X_PAT_CARE_REG.END_DATE >= ConData.CONTACT_DATE)

OR (X_PAT_CARE_REG.START_DATE < ConData.CONTACT_DATE)

AND (X_PAT_CARE_REG.END_DATE IS NULL))

/* ░▒▓ Match Historic Patient address and CCG of Residence to historic event dates. ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

SET NOCOUNT ON

SELECT distinct

X_PAT_ADDR_HX.PAT_ID AS PAT_ID2,

AddrLines.Add1,

AddrLines.Add2,

X_PAT_ADDR_HX.ADDR_HX_CITY,

X_PAT_ADDR_HX.ADDR_HX_COUNTY_C,

X_PAT_ADDR_HX.ADDR_HX_ZIP,

X_PAT_ADDR_HX.ADDR_HX_COUNTRY_C,

X_PAT_ADDR_HX.ADDR_HX_PDS_IDNT,

X_PAT_ADDR_HX.ADDR_HX_START_DT,

X_PAT_ADDR_HX.ADDR_HX_END_DT

INTO #CCGResidence

FROM

CLARITY.dbo.X_PAT_ADDR_HX X_PAT_ADDR_HX

INNER JOIN #Samples ON #Samples.EPT_PATIENT_ID = X_PAT_ADDR_HX.PAT_ID

LEFT OUTER JOIN

(SELECT PAT_ID,GROUP_LINE,[1] AS Add1, [2] AS Add2

FROM CLARITY.dbo.X_PAT_ADDR_HX_RM PIVOT (max(ADDR_HX_STREET)

FOR [VALUE_LINE] IN ([1], [2])) AS P) AS AddrLines

ON X_PAT_ADDR_HX.LINE = AddrLines.GROUP_LINE AND X_PAT_ADDR_HX.PAT_ID = AddrLines.PAT_ID

WHERE X_PAT_ADDR_HX.ADDR_HX_TYPE_C = 1

/* ░▒▓ Match current Patient address and CCG of Residence to event dates. ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

SET NOCOUNT ON

SELECT distinct

X_NHS_NOADD.ADDR_START_DT,

X_NHS_NOADD.PAT_ID AS PAT_ID3,

PATIENT.ADD_LINE_1 AS [Address line 1],

PATIENT.ADD_LINE_2 AS [Address line 2],

PATIENT.CITY AS [Town/City],

PATIENT.ZIP AS Postcode,

V_PMD_CURRCCG.CCGofResCode

Into #Current_CCG

FROM

CLARITY.dbo.X_NHS_NOADD X_NHS_NOADD

INNER JOIN #Samples ON #Samples.EPT_PATIENT_ID = X_NHS_NOADD.PAT_ID

INNER JOIN

CLARITY.dbo.V_PMD_CURRCCG V_PMD_CURRCCG

ON (X_NHS_NOADD.PAT_ID = V_PMD_CURRCCG.PAT_ID)

LEFT OUTER JOIN CLARITY.dbo.PATIENT PATIENT

ON (X_NHS_NOADD.Pat_ID = PATIENT.PAT_ID)

/* ░▒▓ Load data from temp tables ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▒░ */

SELECT DISTINCT

#Samples.*,

--#DateDimension.*,

#CCGResidence.*,

#CCG.*,

#Current_CCG.*

-- #Submitter.*

FROM #Samples

LEFT JOIN #CCGResidence

ON (#Samples.EPT_PATIENT_ID = #CCGResidence.PAT_ID2

AND #Samples.LastVerifiedDate >= #CCGResidence.ADDR_HX_START_DT

AND #Samples.LastVerifiedDate <= #CCGResidence.ADDR_HX_END_DT)

LEFT JOIN #CCG

ON (#Samples.csn = #CCG.PAT_ENC_CSN_ID)

LEFT JOIN #Current_CCG

ON (#Samples.EPT_PATIENT_ID = #Current_CCG.PAT_ID3

AND #Samples.LastVerifiedDate >= #Current_CCG.ADDR_START_DT)

Where #Samples.RESULT_STATUS_C = 9 -- Verified

-- AND #Samples.RES_VAL_STATUS_C = 9

and #Samples.SORT is not null

END

GO

dclark0699 · ‎2015-09-11

We do incremental as often as possible. Our work around to the tables without an update_date has been to actually just add a custom update date field to the table through the KB/SQL process that generates the data in Clarity. It was a practice that was put in place for our Data Warehouse and we've continued using it for Qlikview.

Epic Developers