RE: Multiple joins to the same table

SSC Guru

Points: 183698

July 29, 2014 at 9:08 am

That's a lengthy code. Instead of including a whole set of columns in a group by, I used a correlated subquery with CROSS APPLY. You can do the same with the procedures table. try to understand the changes I did and ask any questions that you have. There's a nice article about cross tabs that you could read. http://www.sqlservercentral.com/articles/T-SQL/63681/

select

AD.VisitID

,AD.PtStatus --inpatient or outpatient

,AD.UnitNumber --local patient ID

,AD.UniquePublicIdentifier --NHS Number

,ADMPUK.NhsTraceStatus

,AD.AccountNumber --spell number or attendance identifier

,AD.AbstractID --DR Abstract Module Linking Field

,AD.AdmitDateTime

,AD.DischargeDateTime

,AD.[Status]--DS Added 29/11/13Coding finished

,AD.AbstractStatusDateTime--DS Added 29/11/13

,CE.StartDateTime --EPISODE ST

,CE.EndDateTime --EPISODE ET

,DATC.AbsTapeCode --National Treatment Function Code

,***.AbsServiceID --Treatment Function Local Code

,***.ServiceName --Treatment Function Local Description

,***.ServiceSeqID --Episode Number in AbsServices

,***.ProviderType --ASK IAN IS IT CONSULTANT LED?

,***.ProviderTypeName --As above but the description

,***.ProviderNumber --Consultant National Code

,***.ProviderID --Consultant Local Code

,***.ProviderName --Consultant Local Description

,APUK.AdmissionMethodID

,APUK.AdmissionMethodName

,Isnull(ac.AbsTapeCode,'Blank') as AdmissionMethodDhCode

,APUK.DischargeDestinationID

,APUK.DischargeDestinationName

,DDDH.AbsTapeCode as DischargeDestinationDhCode

,APUK.ManagementIntentID

,APUK.ManagementIntentDescription

,APUK.ReferringGpID

,APUK.OutcomeID

,APUK.OutcomeName

,APUK.VisitType

,case

when VisitType = 'N' Then 'New Appointment'

when VisitType = 'F' Then 'FollowUp Appointment'

when VisitType is null Then 'Blank'

else 'Other'

end as AppointmentType

,APUK.ReferralSourceID

,APUK.ReferralSourceDescription

,CE.CommissionerID

,***.Days --Episode Length

,AD.AdmitPriorityID

,AD.AdmitPriorityName

,AD.AdmitSourceID

,AD.AdmitSourceName

,AD.DischargeDispositionID

,AD.DischargeDispositionName

,AD.DischargeAbsServiceID --Local Code of specialty discharging

,AD.DischargeServiceName --Local Specialty Description

,ADMPUK.SubSpecialty --14/05/20 DS Added Sub Specialty

--/*

,dc.PrimaryDiagnosis

,dc.SecDiag01

,dc.SecDiag02

,dc.SecDiag03

,dc.SecDiag04

,dc.SecDiag05

,dc.SecDiag06

,dc.SecDiag07

,dc.SecDiag08

,dc.SecDiag09

,dc.SecDiag10

,dc.SecDiag11

,dc.SecDiag12

,dc.Exceeds12ScondaryDiagnosisInEpisode

,isnull(dc.PrimaryDiagnosis,'')+';'+isnull(dc.SecDiag01,'')+';'+

isnull(dc.SecDiag02,'')+';'+isnull(dc.SecDiag03,'')+';'+

isnull(dc.SecDiag04,'')+';'+isnull(dc.SecDiag05,'')+';'+

isnull(dc.SecDiag06,'')+';'+isnull(dc.SecDiag07,'')+';'+

isnull(dc.SecDiag08,'')+';'+isnull(dc.SecDiag09,'')+';'+

isnull(dc.SecDiag10,'')+';'+isnull(dc.SecDiag11,'')+';'+

isnull(dc.SecDiag12,'')+';' as DiagnosisConcatenated

----------------------------------------------

--OPCS4 PROCEDURE FIELDS

----------------------------------------------

,PRIMPROC.ProcedureCode as PrimProcSnomedCode

,PRIMPROC.OpcsProcedureCode as PrimProcOpcsCode

,PRIMPROC.ProcedureDateTime as PrimProcDateTime

,SECPROC1.ProcedureCode as SecProcSnomedCode01

,SECPROC1.OpcsProcedureCode as SecProcOpcsCode01

,SECPROC1.ProcedureDateTime as SecProcDateTime01

,SECPROC2.ProcedureCode as SecProcSnomedCode02

,SECPROC2.OpcsProcedureCode as SecProcOpcsCode02

,SECPROC2.ProcedureDateTime as SecProcDateTime02

,SECPROC3.ProcedureCode as SecProcSnomedCode03

,SECPROC3.OpcsProcedureCode as SecProcOpcsCode03

,SECPROC3.ProcedureDateTime as SecProcDateTime03

,SECPROC4.ProcedureCode as SecProcSnomedCode04

,SECPROC4.OpcsProcedureCode as SecProcOpcsCode04

,SECPROC4.ProcedureDateTime as SecProcDateTime04

,SECPROC5.ProcedureCode as SecProcSnomedCode05

,SECPROC5.OpcsProcedureCode as SecProcOpcsCode05

,SECPROC5.ProcedureDateTime as SecProcDateTime05

,SECPROC6.ProcedureCode as SecProcSnomedCode06

,SECPROC6.OpcsProcedureCode as SecProcOpcsCode06

,SECPROC6.ProcedureDateTime as SecProcDateTime06

,SECPROC7.ProcedureCode as SecProcSnomedCode07

,SECPROC7.OpcsProcedureCode as SecProcOpcsCode07

,SECPROC7.ProcedureDateTime as SecProcDateTime07

,SECPROC8.ProcedureCode as SecProcSnomedCode08

,SECPROC8.OpcsProcedureCode as SecProcOpcsCode08

,SECPROC8.ProcedureDateTime as SecProcDateTime08

,SECPROC9.ProcedureCode as SecProcSnomedCode09

,SECPROC9.OpcsProcedureCode as SecProcOpcsCode09

,SECPROC9.ProcedureDateTime as SecProcDateTime09

,SECPROC10.ProcedureCode as SecProcSnomedCode10

,SECPROC10.OpcsProcedureCode as SecProcOpcsCode10

,SECPROC10.ProcedureDateTime as SecProcDateTime10

,SECPROC11.ProcedureCode as SecProcSnomedCode11

,SECPROC11.OpcsProcedureCode as SecProcOpcsCode11

,SECPROC11.ProcedureDateTime as SecProcDateTime11

,SECPROC12.ProcedureCode as SecProcSnomedCode12

,SECPROC12.OpcsProcedureCode as SecProcOpcsCode12

,SECPROC12.ProcedureDateTime as SecProcDateTime12

---------------------------------------------------------------

,isnull(PRIMPROC.ProcedureCode,'')+';'+isnull(SECPROC1.ProcedureCode,'')+';'+

isnull(SECPROC2.ProcedureCode,'')+';'+isnull(SECPROC3.ProcedureCode,'')+';'+

isnull(SECPROC4.ProcedureCode,'')+';'+isnull(SECPROC5.ProcedureCode,'')+';'+

isnull(SECPROC6.ProcedureCode,'')+';'+isnull(SECPROC7.ProcedureCode,'')+';'+

isnull(SECPROC8.ProcedureCode,'')+';'+isnull(SECPROC9.ProcedureCode,'')+';'+

isnull(SECPROC10.ProcedureCode,'')+';'+isnull(SECPROC11.ProcedureCode,'')+';'+

isnull(SECPROC12.ProcedureCode,'')+';' as SnomedProcCodeConcatenated

--------------------------------------------------------------------

,isnull(PRIMPROC.OpcsProcedureCode,'')+';'+isnull(SECPROC1.OpcsProcedureCode,'')+';'+

isnull(SECPROC2.OpcsProcedureCode,'')+';'+isnull(SECPROC3.OpcsProcedureCode,'')+';'+

isnull(SECPROC4.OpcsProcedureCode,'')+';'+isnull(SECPROC5.OpcsProcedureCode,'')+';'+

isnull(SECPROC6.OpcsProcedureCode,'')+';'+isnull(SECPROC7.OpcsProcedureCode ,'')+';'+

isnull(SECPROC8.OpcsProcedureCode,'')+';'+isnull(SECPROC9.OpcsProcedureCode,'')+';'+

isnull(SECPROC10.OpcsProcedureCode,'')+';'+isnull(SECPROC11.OpcsProcedureCode,'')+';'+

isnull(SECPROC12.OpcsProcedureCode,'')+';' as OpcsProcCodeConcatenated

--*/

-------------------

--Patient Detail Feilds

-------------------

,AD.Name --PatientName

,AD.BirthDateTime

,Datediff(YY,AD.BirthDateTime,AD.AdmitDateTime) -

CASE

WHEN Dateadd(YY,Datediff (YY,AD.BirthDateTime,AD.AdmitDateTime),AD.BirthDateTime)> AD.AdmitDateTime THEN 1

ELSE 0

END as AgeAtAdmission

,AD.RaceID

,AD.RaceName

,Eth.AbsTapeCode as AbsTapeCodeEthnicity

,AD.Sex

,AD.MaritalStatusID

,AD.MaritalStatusName

,ADMPUK.Title

,AV.Address1

,AV.Address2

,AV.City

,AV.StateProvince

,AV.PostalCode

,AV.Status as AdmStatus

,AV.HomePhone

,AV.OtherPhone

--select * from livedb_daily.dbo.AbstractData

-------------------------------

--RowUpdateTimes

-------------------------------

,AD.RowUpdateDateTime as AD_RowUpdateDateTime

,APUK.RowUpdateDateTime as APUK_RowUpdateDateTime

,***.RowUpdateDateTime as ASS_RowUpdateDateTime

,CE.RowUpdateDateTime as CE_RowUpdateDateTime

,DATC.RowUpdateDateTime as DATC_RowUpdateDateTime

--/*

,dc.PRIMDIAG_RowUpdateDateTime

,dc.SECDIAG01_RowUpdateDateTime

,dc.SECDIAG02_RowUpdateDateTime

,dc.SECDIAG03_RowUpdateDateTime

,dc.SECDIAG04_RowUpdateDateTime

,dc.SECDIAG05_RowUpdateDateTime

,dc.SECDIAG06_RowUpdateDateTime

,dc.SECDIAG07_RowUpdateDateTime

,dc.SECDIAG08_RowUpdateDateTime

,dc.SECDIAG09_RowUpdateDateTime

,dc.SECDIAG10_RowUpdateDateTime

,dc.SECDIAG11_RowUpdateDateTime

,dc.SECDIAG12_RowUpdateDateTime

--*/

,AdmissionWard.RowUpdateDateTime as AdmissionWard_RowUpdateDateTime

,MaxWardStay.RowUpdateDateTime as MaxWardStay_RowUpdateDateTime

--------------------------------

--Ward Details

--------------------------------

,AdmissionWard.Ward as AdmissionWardIDForSpell

,AdmissionWard.WardLocation as AdmissionWardLocationForSpell

,MaxWardStay.Ward as LatestWardIDInSpell

,MaxWardStay.WardLocation as LatestWardLocationInSpell

,MaxWardStay.ServiceSeqID as LatestWardStayEpisode

,MaxWardStay.WardSeqID as LatestWardStaySequence

,SecondtoLastWardStay.Ward as SecondLatestWardIDInSpell

,PenultWardLocationInSpell = SecondtoLastWardStay.WardLocation

-----------------------------------------------------------------

--HRG MEDITECH CALCULATED ON EPISODE

-----------------------------------------------------------------

,CE.GrouperVersionID as HrgEpisodeMeditechCalculatedCode

,CE.GrouperVersionName as HrgEpisodeMeditechCalculatedDesc

------------------------------------------------------------------

--Status Added from AdmVisits (ASJS 20130305

------------------------------------------------------------------

,AV.Status as AdmPtStatus

-------------------------------------------------------------------

-- Registered Practice Added from AbsPatUkAbstractData (IS 20130731)

,Prac.RegisteredPracticeID

,Prac.RegisteredPracticeName

,case

when AD.DischargeDateTime IS NOT NULL then

CASE

when ***.ServiceSeqID=max(isnull(***.ServiceSeqID,0))over(partition by AD.AccountNumber)

then 1

else 2

end

ELSE 2

END as [Last_Episode_Indicator]

,DMETH.AbsTapeCode as Discharge_Method_DH_Code

,CASE

WHEN ac.AbsTapeCode IN ('11','12','13') AND APUK.ManagementIntentID LIKE 'DC'

AND DateDiff(d,DateAdd(d,0,(datediff(d,0,AD.AdmitDateTime))),DateAdd(d,0,(datediff(d,0,AD.DischargeDateTime))))= 0 then 2

WHEN AD.PtStatus LIKE 'IN' THEN 1

end as [Patient_Class_DH_Code]

,vte.Response

,AD.Newborn

,av.[ReasonForVisit]

-- Uncoded reason fields - added 17/07/2014 for Leilei

,mult.Response as [Uncoded Reason]

,left([Additional Notes].Comments,300) as [Additional Notes]

--------------------------------------------------------------------

--END OF FIELDS

-------------------------------------------------------------------

from

livedb_daily.dbo.AbstractData AD

left outer join livedb_daily.dbo.AbsServices ***

on AD.AbstractID = ***.AbstractID

--------------------------------------------------------

--Add Episodes To get Commissioner (thats it!)

--------------------------------------------------------

left outer join livedb_daily.dbo.AbsPatUkConsultantEpisodes CE

on ***.AbstractID = CE.AbstractID

and ***.ServiceSeqID = CE.ServiceSeqID

--------------------------------------------------------------------------------------------------------

--Add AbsPatUks for AdmissionMethod DischargeDestination Intended Management Referrer Source of referral

--------------------------------------------------------------------------------------------------------

left outer join livedb_daily.dbo.AbsPatUks APUK

on AD.AbstractID = APUK.AbstractID

------------------------------------

--DischargeDestination Dh Code

------------------------------------

left outer join livedb_daily.dbo.DMisUkDischargeDestCodes DDDH

on APUK.DischargeDestinationID = DDDH.DischargeDestinationID

and DDDH.AbsTapeID = 'NHS CDS'

--------------------------------------------------------

--TreatmentFunctionCode Dh Code Lookup

--------------------------------------------------------

left outer join livedb_daily.dbo.DMisAbsService DAS

on ***.AbsServiceID = DAS.AbsServiceID

left outer join livedb_daily.dbo.DMisAbsServiceTapeCode DATC

on DAS.AbsServiceID = DATC.AbsServiceID

and DAS.Active = 'Y'

and DATC.TapeID = 'NHS CDS'

-------------------------------------------------------

--Admission Method Dh Code Lookup

-------------------------------------------------------

left outer join livedb_daily.dbo.DMisUkAdmissionMethods am

on APUK.AdmissionMethodID = am.AdmissionMethodID

left outer join livedb_daily.dbo.DMisUkAdmissionMethodCodes ac

on am.AdmissionMethodID = ac.AdmissionMethodID

and am.Active = 'Y'

and ac.AbsTapeID = 'NHS CDS'

-------------------------------------------------------

--DIAGNOSIS

-------------------------------------------------------

cross apply( SELECT MAX( CASE WHEN OrderOfDiagnosisInEpisode = 1 THEN DiagnosisIcd END) PrimaryDiagnosis,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 1 THEN RowUpdateDateTime END) PRIMDIAG_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 2 THEN DiagnosisIcd END) SecDiag01,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 2 THEN RowUpdateDateTime END) SECDIAG01_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 3 THEN DiagnosisIcd END) SecDiag02,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 3 THEN RowUpdateDateTime END) SECDIAG02_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 4 THEN DiagnosisIcd END) SecDiag03,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 4 THEN RowUpdateDateTime END) SECDIAG03_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 5 THEN DiagnosisIcd END) SecDiag04,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 5 THEN RowUpdateDateTime END) SECDIAG04_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 6 THEN DiagnosisIcd END) SecDiag05,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 6 THEN RowUpdateDateTime END) SECDIAG05_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 7 THEN DiagnosisIcd END) SecDiag06,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 7 THEN RowUpdateDateTime END) SECDIAG06_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 8 THEN DiagnosisIcd END) SecDiag07,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 8 THEN RowUpdateDateTime END) SECDIAG07_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 9 THEN DiagnosisIcd END) SecDiag08,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 9 THEN RowUpdateDateTime END) SECDIAG08_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 10 THEN DiagnosisIcd END) SecDiag09,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 10 THEN RowUpdateDateTime END) SECDIAG09_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 11 THEN DiagnosisIcd END) SecDiag10,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 11 THEN RowUpdateDateTime END) SECDIAG10_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 12 THEN DiagnosisIcd END) SecDiag11,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 12 THEN RowUpdateDateTime END) SECDIAG11_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 13 THEN DiagnosisIcd END) SecDiag12,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 13 THEN RowUpdateDateTime END) SECDIAG12_RowUpdateDateTime,

MAX( CASE WHEN OrderOfDiagnosisInEpisode = 14 THEN 'Yes' ELSE 'No' END) Exceeds12ScondaryDiagnosisInEpisode

FROM Diag_Codes d

WHERE ***.AbstractID = d.AbstractID

and ***.ServiceSeqID = d.EpisodeNumber

GROUP BY d.AbstractID,

d.EpisodeNumber) DC

------------------------------------------------------------------

--PROCEDURES OPCS ETC

------------------------------------------------------------------

--/*

--PRIMARY PROCEDURE (1)

left outer join Proc_Codes PRIMPROC

on ***.AbstractID = PRIMPROC.AbstractID

and ***.ServiceSeqID = PRIMPROC.EpisodeNumber

and PRIMPROC.OrderOfProcedureInEpisode = 1

--SECONDARY PROCEDURE 1 (2)

left outer join Proc_Codes SECPROC1

on ***.AbstractID = SECPROC1.AbstractID

and ***.ServiceSeqID = SECPROC1.EpisodeNumber

and SECPROC1.OrderOfProcedureInEpisode = 2

--SECONDARY PROCEDURE 2 (3)

left outer join Proc_Codes SECPROC2

on ***.AbstractID = SECPROC2.AbstractID

and ***.ServiceSeqID = SECPROC2.EpisodeNumber

and SECPROC2.OrderOfProcedureInEpisode = 3

--SECONDARY PROCEDURE 3 (4)

left outer join Proc_Codes SECPROC3

on ***.AbstractID = SECPROC3.AbstractID

and ***.ServiceSeqID = SECPROC3.EpisodeNumber

and SECPROC3.OrderOfProcedureInEpisode = 4

--SECONDARY PROCEDURE 4 (5)

left outer join Proc_Codes SECPROC4

on ***.AbstractID = SECPROC4.AbstractID

and ***.ServiceSeqID = SECPROC4.EpisodeNumber

and SECPROC4.OrderOfProcedureInEpisode = 5

--SECONDARY PROCEDURE 5 (6)

left outer join Proc_Codes SECPROC5

on ***.AbstractID = SECPROC5.AbstractID

and ***.ServiceSeqID = SECPROC5.EpisodeNumber

and SECPROC5.OrderOfProcedureInEpisode = 6

--SECONDARY PROCEDURE 6 (7)

left outer join Proc_Codes SECPROC6

on ***.AbstractID = SECPROC6.AbstractID

and ***.ServiceSeqID = SECPROC6.EpisodeNumber

and SECPROC6.OrderOfProcedureInEpisode = 7

--SECONDARY PROCEDURE 7 (8)

left outer join Proc_Codes SECPROC7

on ***.AbstractID = SECPROC7.AbstractID

and ***.ServiceSeqID = SECPROC7.EpisodeNumber

and SECPROC7.OrderOfProcedureInEpisode = 8

--SECONDARY PROCEDURE 8 (9)

left outer join Proc_Codes SECPROC8

on ***.AbstractID = SECPROC8.AbstractID

and ***.ServiceSeqID = SECPROC8.EpisodeNumber

and SECPROC8.OrderOfProcedureInEpisode = 9

--SECONDARY PROCEDURE 9 (10)

left outer join Proc_Codes SECPROC9

on ***.AbstractID = SECPROC9.AbstractID

and ***.ServiceSeqID = SECPROC9.EpisodeNumber

and SECPROC9.OrderOfProcedureInEpisode = 10

--SECONDARY PROCEDURE 10 (11)

left outer join Proc_Codes SECPROC10

on ***.AbstractID = SECPROC10.AbstractID

and ***.ServiceSeqID = SECPROC10.EpisodeNumber

and SECPROC10.OrderOfProcedureInEpisode = 11

--SECONDARY PROCEDURE 11 (12)

left outer join Proc_Codes SECPROC11

on ***.AbstractID = SECPROC11.AbstractID

and ***.ServiceSeqID = SECPROC11.EpisodeNumber

and SECPROC11.OrderOfProcedureInEpisode = 12

--SECONDARY PROCEDURE 12 (13)

left outer join Proc_Codes SECPROC12

on ***.AbstractID = SECPROC12.AbstractID

and ***.ServiceSeqID = SECPROC12.EpisodeNumber

and SECPROC12.OrderOfProcedureInEpisode = 13

--*/

-----------------------------------------------------------------

--Max Ward Stay in Spell

-----------------------------------------------------------------

left JOIN ward_Stay MaxWardStay

on AD.VisitID=MaxWardStay.VisitID

and MaxWardStay.[Index] = MaxWardStay.MaxIndex

-----------------------------------------------------------------

--2nd to last Ward Stay in Spell

-----------------------------------------------------------------

left JOIN ward_Stay SecondtoLastWardStay

on AD.VisitID=SecondtoLastWardStay.VisitID

and SecondtoLastWardStay.[Index] =

case

when SecondtoLastWardStay.MaxIndex>1 then SecondtoLastWardStay.MaxIndex-1

else 1

end

---------------------------------------------------------------

--Admission Ward

---------------------------------------------------------------

left JOIN ward_Stay AdmissionWard

on AD.VisitID=AdmissionWard.VisitID

and AdmissionWard.[Index] = 1

-------------------------------------------------------------

--Title and trace status got from ADM on the visit

-------------------------------------------------------------

left outer join livedb_daily.dbo.AdmPatUks ADMPUK

on AD.VisitID = ADMPUK.VisitID

-------------------------------------------------------------

--Link to ADM Visits to get lots of fields such as address

-------------------------------------------------------------

left outer join livedb_daily.dbo.AdmVisits AV

on AD.VisitID = AV.VisitID

-------------------------------------------------------------

--Ethnicity National code link

-------------------------------------------------------------

left outer join (select

*

from

livedb_daily.dbo.DMisRaceTapeCode

where

TapeID = 'NHS CDS'

) Eth

on AD.RaceID = Eth.RaceID

-------------------------------------------------------------------

-- Registered Practice Code

Left outer join livedb_daily.dbo.AbsPatUkAbstractData Prac

on AD.AbstractID=Prac.AbstractID

-----------------------------------------------------------------------

---Discharge_Method_dh_code

Left outer join livedb_daily.dbo.DMisDischargeDispTapeCode DMETH

on AD.DischargeDispositionID=DMETH.DispositionID

AND DMETH.TapeID='NHS CDS'

-------------------------------------------------------------------------

--- VTE Flag

Left outer Join livedb_daily.dbo.AbsProjectsQueriesCs vte

on AD.AbstractID = vte.AbstractID

and vte.QueryID LIKE 'CODER.VTE'

-------------------------------------------------------------------------

--- Uncoded Reason **** Extra Field for Leilei

left outer join [livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult

on ad.[VisitID]=mult.[VisitID]

and ad.AbstractID=mult.AbstractID

and mult.[Query]='Uncoded Reason'

--- Additional Notes **** Extra Field for Leilei

left outer join (

select

mult1.VisitID

,mult1.AbstractID

,replace(replace(replace(replace((

select

Response + ','

from

[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult2

where

mult2.VisitID=mult1.VisitID

and mult2.AbstractID=mult1.AbstractID

and mult2.[Query]='Additional Notes'

order by

mult2.VisitID

,mult2.AbstractID

for xml path ('')),'

',''),'&','&'),char(13),''),char(10),'') as Comments

from

[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1

where

mult1.[Query]='Additional Notes'

group by

mult1.VisitID

,mult1.AbstractID) [Additional Notes]

on

[Additional Notes].VisitID=ad.[VisitID]

and [Additional Notes].AbstractID=ad.AbstractID

WHERE

(AD.Name NOT LIKE '%DONOTUSE%'

OR AD.Name NOT LIKE '%YYTESTPATIENT%'

OR AD.Name NOT LIKE '%XXTESTPATIENT%'

OR AD.Name IS NULL)

and AD.VisitID is not null

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2