Hi guys, probably a really simple thing to do below is some code I use to identify where a patient has attended the ED department whilst also admitted as an Inpatient. This report works fine. However while most of the results are recording issues to be corrected some of them are real and as so can be excluded from the report.

I can simply hardcode these into the code each time but this to me is messy. Is there a way I can build in an exclusion table which would include:

  

SELECT
	 IP_ADMISSION.HeyNo AS HEYNo
      ,IP_ADMISSION.NHSNo2 AS NHSNo
      ,IP_ADMISSION.Forename AS Forename
      ,IP_ADMISSION.Surname AS Surname
      ,IP_ADMISSION.SourceAdmNatCode AS SourceAdm
      ,IP_ADMISSION.AdmMethGrpDesc AS MethAdm
      ,AdmitDateTime AS IPAdmitDate
      ,DischDateTime AS IPDischDate
      ,IP_ADMISSION.DischWardName AS IPDischWard
      ,ED_ATTENDANCE.EDAttendanceID AS AENo
      ,ED_ATTENDANCE.AttendDateTime AS EDAttendDate
      ,ED_ATTENDANCE.DepartDateTime AS EDDeptDate
      ,DATEDIFF(minute, IP_ADMISSION.AdmitDateTime, ED_ATTENDANCE.AttendDateTime) AS EDMinsAfterIP
      ,ED_ATTENDANCE.AttendDate
      ,IP_ADMISSION.AdmitDate
      ,IP_ADMISSION.DischDate
      ,CASE WHEN ED_ATTENDANCE.AttendDate = IP_ADMISSION.AdmitDate OR ED_ATTENDANCE.AttendDate = IP_ADMISSION.DischDate THEN 'Data Quality Challenge - Standard Review' ELSE 'Commissioner Challenge  Priority Review' end as [Review Status]
FROM IP_ADMISSION
LEFT OUTER JOIN ED_ATTENDANCE
ON IP_ADMISSION.MFPatientID = ED_ATTENDANCE.MFPatientID
WHERE
   IP_ADMISSION.DischDateTime >= @StartDate
AND
   IP_ADMISSION.AdmitDateTime <= @EndDate
AND
   (ED_ATTENDANCE.AttendDateTime >= IP_ADMISSION.AdmitDateTime
    AND
    ED_ATTENDANCE.AttendDateTime <= IP_ADMISSION.DischDateTime)
ORDER BY [Review Status] asc, IP_ADMISSION.DischDateTime DESC

First of all: WAT? I'm not sure what you mean? A NOT EXISTS/NOT IN?

 SELECT *
 FROM   yourTables O
 WHERE  NOT EXISTS ( SELECT *
                     FROM   exclusionTable I
                     WHERE  I.columnNames = O.columnNames );

btw, you can use an INNER JOIN instead of your LEFT JOIN. Cause the test

ED_ATTENDANCE.AttendDateTime >= IP_ADMISSION.AdmitDateTime  AND ED_ATTENDANCE.AttendDateTime <= IP_ADMISSION.DischDateTime

will reduce it anyway to rows where ED_ATTENDANCE returns a value (not being NULL).

Thanks for that I was hoping there was a better alternative to adding this onto the end of the WHERE clause as in time it could get quite lengthy.

---- THIS IS THE BIT I WOULD LIKE TO EXCLUDE WITHOUT HAVING TO HARDCODE FOR EVERY INSTANCE
and (IP_ADMISSION.HEYNo <> 'HEY0103406' AND IP_ADMISSION.AdmitDate <> '14/06/2015') -- THIS PATIENT PATHWAY IS CORRECT AND VERIFIED BY JS ON 11/09/15 
and (IP_ADMISSION.HEYNo <> 'HEY0760615' AND IP_ADMISSION.AdmitDate <> '23/04/2015') -- THIS PATIENT PATHWAY IS CORRECT AND VERIFIED BY JS ON 11/09/15 
and (IP_ADMISSION.HEYNo <> 'HEY1167718' AND IP_ADMISSION.AdmitDate <> '23/04/2015') -- THIS PATIENT PATHWAY IS CORRECT AND VERIFIED BY JS ON 11/09/15 
and (IP_ADMISSION.HEYNo <> 'HEY0826571' AND IP_ADMISSION.AdmitDate <> '17/06/2015') -- THIS PATIENT PATHWAY IS CORRECT AND VERIFIED BY JS ON 11/09/15 

What's better than a table?

DECLARE @Exclude TABLE
    (
      HeyNo NVARCHAR(255) ,
      AdmitDate DATE
    );

INSERT  INTO @Exclude
VALUES  ( 'HEY0103406', '14/06/2015' ) ,
        ( 'HEY0760615', '23/04/2015' ) ,
        ( 'HEY1167718', '23/04/2015' ) ,
        ( 'HEY0826571', '17/06/2015' ); 

SELECT  *
FROM    IP_ADMISSION IPA
        INNER JOIN ED_ATTENDANCE EDA ON IPA.MFPatientID = EDA.MFPatientID
WHERE   IPA.DischDateTime >= @StartDate
        AND IPA.AdmitDateTime <= @EndDate
        AND EDA.AttendDateTime >= IPA.AdmitDateTime
        AND EDA.AttendDateTime <= IPA.DischDateTime
        AND NOT EXISTS ( SELECT *
                         FROM   @Exclude E
                         WHERE  E.HeyNo = IPA.HeyNo
                                AND E.AdmitDate = IPA.AdmitDate )
ORDER BY [Review Status] ASC ,
        IPA.DischDateTime DESC;

-- or

SELECT  *
FROM    IP_ADMISSION IPA
        INNER JOIN ED_ATTENDANCE EDA ON IPA.MFPatientID = EDA.MFPatientID
        LEFT JOIN @Exclude E ON E.HeyNo = IPA.HeyNo
                                AND E.AdmitDate = IPA.AdmitDate
WHERE   IPA.DischDateTime >= @StartDate
        AND IPA.AdmitDateTime <= @EndDate
        AND EDA.AttendDateTime >= IPA.AdmitDateTime
        AND EDA.AttendDateTime <= IPA.DischDateTime
        AND E.HeyNo IS NULL
ORDER BY [Review Status] ASC ,
        IPA.DischDateTime DESC;

• Edited by Stefan HoffmannMVP 16 hours 55 minutes ago
• Marked as answer by ProfessorFudger 16 hours 27 minutes ago

Thank you kindly, very similar but i prefer your method