Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple joins to the same table


Multiple joins to the same table

Author
Message
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
Hi all

I've got the following stored procedure (this is part of the from clause) and it looks horrendous. Here's the code:-

-------------------------------------------------------
--PRIMARY DIAGNOSIS
-------------------------------------------------------
   left outer join Diag_Codes PRIMDIAG
      on ***.AbstractID = PRIMDIAG.AbstractID
      and ***.ServiceSeqID = PRIMDIAG.EpisodeNumber
      and PRIMDIAG.OrderOfDiagnosisInEpisode = 1
-------------------------------------------------------
--SECONDARY DIAGNOSIS 1 (2)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG01
      on ***.AbstractID = SECDIAG01.AbstractID
      and ***.ServiceSeqID = SECDIAG01.EpisodeNumber
      and SECDIAG01.OrderOfDiagnosisInEpisode = 2
-------------------------------------------------------
--SECONDARY DIAGNOSIS 2 (3)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG02
      on ***.AbstractID = SECDIAG02.AbstractID
      and ***.ServiceSeqID = SECDIAG02.EpisodeNumber
      and SECDIAG02.OrderOfDiagnosisInEpisode = 3
-------------------------------------------------------
--SECONDARY DIAGNOSIS 3 (4)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG03
      on ***.AbstractID = SECDIAG03.AbstractID
      and ***.ServiceSeqID = SECDIAG03.EpisodeNumber
      and SECDIAG03.OrderOfDiagnosisInEpisode = 4
-------------------------------------------------------
--SECONDARY DIAGNOSIS 4 (5)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG04
      on ***.AbstractID = SECDIAG04.AbstractID
      and ***.ServiceSeqID = SECDIAG04.EpisodeNumber
      and SECDIAG04.OrderOfDiagnosisInEpisode = 5
-------------------------------------------------------
--SECONDARY DIAGNOSIS 5 (6)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG05
      on ***.AbstractID = SECDIAG05.AbstractID
      and ***.ServiceSeqID = SECDIAG05.EpisodeNumber
      and SECDIAG05.OrderOfDiagnosisInEpisode = 6
-------------------------------------------------------
--SECONDARY DIAGNOSIS 6 (7)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG06
      on ***.AbstractID = SECDIAG06.AbstractID
      and ***.ServiceSeqID = SECDIAG06.EpisodeNumber
      and SECDIAG06.OrderOfDiagnosisInEpisode = 7
-------------------------------------------------------
--SECONDARY DIAGNOSIS 7 (8)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG07
      on ***.AbstractID = SECDIAG07.AbstractID
      and ***.ServiceSeqID = SECDIAG07.EpisodeNumber
      and SECDIAG07.OrderOfDiagnosisInEpisode = 8
-------------------------------------------------------
--SECONDARY DIAGNOSIS 8 (9)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG08
      on ***.AbstractID = SECDIAG08.AbstractID
      and ***.ServiceSeqID = SECDIAG08.EpisodeNumber
      and SECDIAG08.OrderOfDiagnosisInEpisode = 9
-------------------------------------------------------
--SECONDARY DIAGNOSIS 9 (10)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG09
      on ***.AbstractID = SECDIAG09.AbstractID
      and ***.ServiceSeqID = SECDIAG09.EpisodeNumber
      and SECDIAG09.OrderOfDiagnosisInEpisode = 10
-------------------------------------------------------
--SECONDARY DIAGNOSIS 10 (11)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG10
      on ***.AbstractID = SECDIAG10.AbstractID
      and ***.ServiceSeqID = SECDIAG10.EpisodeNumber
      and SECDIAG10.OrderOfDiagnosisInEpisode = 11
   -------------------------------------------------------
   --SECONDARY DIAGNOSIS 11 (12)
   -------------------------------------------------------
   left outer join Diag_Codes SECDIAG11
      on ***.AbstractID = SECDIAG11.AbstractID
      and ***.ServiceSeqID = SECDIAG11.EpisodeNumber
      and SECDIAG11.OrderOfDiagnosisInEpisode = 12
   -------------------------------------------------------
   --SECONDARY DIAGNOSIS 12 (13)
   -------------------------------------------------------
   left outer join Diag_Codes SECDIAG12
      on ***.AbstractID = SECDIAG12.AbstractID
      and ***.ServiceSeqID = SECDIAG12.EpisodeNumber
      and SECDIAG12.OrderOfDiagnosisInEpisode = 13
   ------------------------------------------------------------------
   --SECONDARY DIAGNOSIS 13 (14) EXCEEDS 12 SECONDARY DIAGNOSIS CHECK
   ------------------------------------------------------------------
   left outer join Diag_Codes SECDIAG13
      on ***.AbstractID = SECDIAG13.AbstractID
      and ***.ServiceSeqID = SECDIAG13.EpisodeNumber
      and SECDIAG13.OrderOfDiagnosisInEpisode = 14



I'm thinking there must be a better way to interact with the same table multiple times but I can't think of anything.
The table Diag_Codes has the following fields:-
AbstractID
EpisodeNumber
OrderOfDiagnosisInEpisode
RowUpdateDateTime
DiagnosisICD

Am I right in thinking that joining to the same table multiple times is going to be the slow way of doing things?

I've already speeded it up by using semi-temprorary tables for the diagnosis and procedure data and these semi-temporary tables are indexed using the recommendations from the Database Tuning Advisor.

The proc in its entirety runs in about 20 minutes but I'm thinking it could be quicker if I could get this section (and there's another one like it for procedures) a bit tidier/slicker.

Anyone any pointers please?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8476 Visits: 18082
I'm just guessing here, but maybe a cross tabs approach is what you need to read the table just once.
Example:

SELECT x.Somecolumns,
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 1 THEN OrderOfDiagnosisInEpisode END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 1 THEN DiagnosisICD END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 2 THEN OrderOfDiagnosisInEpisode END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 2 THEN DiagnosisICD END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 3 THEN OrderOfDiagnosisInEpisode END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 3 THEN DiagnosisICD END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 4 THEN OrderOfDiagnosisInEpisode END),
MAX( CASE WHEN OrderOfDiagnosisInEpisode = 4 THEN DiagnosisICD END)
FROM SomeTable x
LEFT OUTER JOIN Diag_Codes PRIMDIAG
   on x.AbstractID = PRIMDIAG.AbstractID
   and x.ServiceSeqID = PRIMDIAG.EpisodeNumber
GROUP BY x.Somecolumns




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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
Yeah, would have to see the main SELECT to know how to adjust the code properly.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 7660
This appears to be a reporting pivot of timeline rows for a report of some kind regarding the diagnosis applied to a patient.

Look to the report. Can this be sub-reported intelligently instead of trying to force the pivot? If not, I agree with the above, either crosstab it or PIVOT it. Either will most likely get you performance improvements.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
Hi all

I like the idea of the cross-tab/pivot but I'm not sure how they would work?
Any idiot-proof info available?

As per an earlier request, here's the entire select statement:-

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/13   Coding 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
--/*
   ,PRIMDIAG.DiagnosisIcd as PrimaryDiagnosis
   ,SECDIAG01.DiagnosisIcd as SecDiag01
   ,SECDIAG02.DiagnosisIcd as SecDiag02
   ,SECDIAG03.DiagnosisIcd as SecDiag03
   ,SECDIAG04.DiagnosisIcd as SecDiag04
   ,SECDIAG05.DiagnosisIcd as SecDiag05
   ,SECDIAG06.DiagnosisIcd as SecDiag06
   ,SECDIAG07.DiagnosisIcd as SecDiag07
   ,SECDIAG08.DiagnosisIcd as SecDiag08
   ,SECDIAG09.DiagnosisIcd as SecDiag09
   ,SECDIAG10.DiagnosisIcd as SecDiag10
   ,SECDIAG11.DiagnosisIcd as SecDiag11
   ,SECDIAG12.DiagnosisIcd as SecDiag12
   ,CASE
      WHEN SECDIAG13.DiagnosisIcd IS NOT NULL THEN 'Yes'
      Else 'No'
   END as Exceeds12ScondaryDiagnosisInEpisode   
   ,isnull(PRIMDIAG.DiagnosisIcd,'')+';'+isnull(SECDIAG01.DiagnosisIcd,'')+';'+
   isnull(SECDIAG02.DiagnosisIcd,'')+';'+isnull(SECDIAG03.DiagnosisIcd,'')+';'+
   isnull(SECDIAG04.DiagnosisIcd,'')+';'+isnull(SECDIAG05.DiagnosisIcd,'')+';'+
   isnull(SECDIAG06.DiagnosisIcd,'')+';'+isnull(SECDIAG07.DiagnosisIcd,'')+';'+
   isnull(SECDIAG08.DiagnosisIcd,'')+';'+isnull(SECDIAG09.DiagnosisIcd,'')+';'+
   isnull(SECDIAG10.DiagnosisIcd,'')+';'+isnull(SECDIAG11.DiagnosisIcd,'')+';'+
   isnull(SECDIAG12.DiagnosisIcd,'')+';' 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
--/*
   ,PRIMDIAG.RowUpdateDateTime as PRIMDIAG_RowUpdateDateTime
   ,SECDIAG01.RowUpdateDateTime as SECDIAG01_RowUpdateDateTime
   ,SECDIAG02.RowUpdateDateTime as SECDIAG02_RowUpdateDateTime
   ,SECDIAG03.RowUpdateDateTime as SECDIAG03_RowUpdateDateTime
   ,SECDIAG04.RowUpdateDateTime as SECDIAG04_RowUpdateDateTime
   ,SECDIAG05.RowUpdateDateTime as SECDIAG05_RowUpdateDateTime
   ,SECDIAG06.RowUpdateDateTime as SECDIAG06_RowUpdateDateTime
   ,SECDIAG07.RowUpdateDateTime as SECDIAG07_RowUpdateDateTime
   ,SECDIAG08.RowUpdateDateTime as SECDIAG08_RowUpdateDateTime
   ,SECDIAG09.RowUpdateDateTime as SECDIAG09_RowUpdateDateTime
   ,SECDIAG10.RowUpdateDateTime as SECDIAG10_RowUpdateDateTime
   ,SECDIAG11.RowUpdateDateTime as SECDIAG11_RowUpdateDateTime
   ,SECDIAG12.RowUpdateDateTime as 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'
--/*
-------------------------------------------------------
--PRIMARY DIAGNOSIS
-------------------------------------------------------
   left outer join Diag_Codes PRIMDIAG
      on ***.AbstractID = PRIMDIAG.AbstractID
      and ***.ServiceSeqID = PRIMDIAG.EpisodeNumber
      and PRIMDIAG.OrderOfDiagnosisInEpisode = 1
-------------------------------------------------------
--SECONDARY DIAGNOSIS 1 (2)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG01
      on ***.AbstractID = SECDIAG01.AbstractID
      and ***.ServiceSeqID = SECDIAG01.EpisodeNumber
      and SECDIAG01.OrderOfDiagnosisInEpisode = 2
-------------------------------------------------------
--SECONDARY DIAGNOSIS 2 (3)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG02
      on ***.AbstractID = SECDIAG02.AbstractID
      and ***.ServiceSeqID = SECDIAG02.EpisodeNumber
      and SECDIAG02.OrderOfDiagnosisInEpisode = 3
-------------------------------------------------------
--SECONDARY DIAGNOSIS 3 (4)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG03
      on ***.AbstractID = SECDIAG03.AbstractID
      and ***.ServiceSeqID = SECDIAG03.EpisodeNumber
      and SECDIAG03.OrderOfDiagnosisInEpisode = 4
-------------------------------------------------------
--SECONDARY DIAGNOSIS 4 (5)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG04
      on ***.AbstractID = SECDIAG04.AbstractID
      and ***.ServiceSeqID = SECDIAG04.EpisodeNumber
      and SECDIAG04.OrderOfDiagnosisInEpisode = 5
-------------------------------------------------------
--SECONDARY DIAGNOSIS 5 (6)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG05
      on ***.AbstractID = SECDIAG05.AbstractID
      and ***.ServiceSeqID = SECDIAG05.EpisodeNumber
      and SECDIAG05.OrderOfDiagnosisInEpisode = 6
-------------------------------------------------------
--SECONDARY DIAGNOSIS 6 (7)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG06
      on ***.AbstractID = SECDIAG06.AbstractID
      and ***.ServiceSeqID = SECDIAG06.EpisodeNumber
      and SECDIAG06.OrderOfDiagnosisInEpisode = 7
-------------------------------------------------------
--SECONDARY DIAGNOSIS 7 (8)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG07
      on ***.AbstractID = SECDIAG07.AbstractID
      and ***.ServiceSeqID = SECDIAG07.EpisodeNumber
      and SECDIAG07.OrderOfDiagnosisInEpisode = 8
-------------------------------------------------------
--SECONDARY DIAGNOSIS 8 (9)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG08
      on ***.AbstractID = SECDIAG08.AbstractID
      and ***.ServiceSeqID = SECDIAG08.EpisodeNumber
      and SECDIAG08.OrderOfDiagnosisInEpisode = 9
-------------------------------------------------------
--SECONDARY DIAGNOSIS 9 (10)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG09
      on ***.AbstractID = SECDIAG09.AbstractID
      and ***.ServiceSeqID = SECDIAG09.EpisodeNumber
      and SECDIAG09.OrderOfDiagnosisInEpisode = 10
-------------------------------------------------------
--SECONDARY DIAGNOSIS 10 (11)
-------------------------------------------------------
   left outer join Diag_Codes SECDIAG10
      on ***.AbstractID = SECDIAG10.AbstractID
      and ***.ServiceSeqID = SECDIAG10.EpisodeNumber
      and SECDIAG10.OrderOfDiagnosisInEpisode = 11
   -------------------------------------------------------
   --SECONDARY DIAGNOSIS 11 (12)
   -------------------------------------------------------
   left outer join Diag_Codes SECDIAG11
      on ***.AbstractID = SECDIAG11.AbstractID
      and ***.ServiceSeqID = SECDIAG11.EpisodeNumber
      and SECDIAG11.OrderOfDiagnosisInEpisode = 12
   -------------------------------------------------------
   --SECONDARY DIAGNOSIS 12 (13)
   -------------------------------------------------------
   left outer join Diag_Codes SECDIAG12
      on ***.AbstractID = SECDIAG12.AbstractID
      and ***.ServiceSeqID = SECDIAG12.EpisodeNumber
      and SECDIAG12.OrderOfDiagnosisInEpisode = 13
   ------------------------------------------------------------------
   --SECONDARY DIAGNOSIS 13 (14) EXCEEDS 12 SECONDARY DIAGNOSIS CHECK
   ------------------------------------------------------------------
   left outer join Diag_Codes SECDIAG13
      on ***.AbstractID = SECDIAG13.AbstractID
      and ***.ServiceSeqID = SECDIAG13.EpisodeNumber
      and SECDIAG13.OrderOfDiagnosisInEpisode = 14
   ------------------------------------------------------------------
   --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'
               Wink 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



Apologies for the length of it (and I've got another one to sort out as well!).

Any pointers gratefully received.

::edit::
Just checked the post and the forum has very kindly replaced A S S (without the spaces) with ***. A S S is a table alias.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8476 Visits: 18082
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/13   Coding 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'
               Wink 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
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
Thanks Luis

Firstly, that's a lot shorter than my code and (I assume) will run a lot quicker.
Just a couple of questions, I've googled cross apply several times but I always get lost somewhere along the lines. Can you explain how it works in idiot-proof terms please?

With the cross apply and cross-tab approach, does this mean the data will be read one instead of (in this case) 14 times?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21055 Visits: 18258
richardmgreen1 (7/29/2014)
Thanks Luis

Firstly, that's a lot shorter than my code and (I assume) will run a lot quicker.
Just a couple of questions, I've googled cross apply several times but I always get lost somewhere along the lines. Can you explain how it works in idiot-proof terms please?

With the cross apply and cross-tab approach, does this mean the data will be read one instead of (in this case) 14 times?


This series of articles should help you with Cross apply.

http://www.sqlservercentral.com/articles/APPLY/69953/



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8476 Visits: 18082
I often think of APPLY as a JOIN on steroids. :-D
Apply will allow you to use correlated subqueries as tables/views or columns from other tables as parameters of functions. I could go in depth but maybe you could read Paul White's articles to understand better: Understanding and Using APPLY (Part 1) & (Part 2)

I can't promise that the query will run faster because I can't test it, but it should because all those repeated joins are not helping with the performance. You need to test it. Can you get rid of the test patients? Or use a table or column to remove the non SARGable filters?


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
richardmgreen1
richardmgreen1
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 892
Hi Luis

Thanks for the links, I'll have a look when I get chance (hopefully today).
As for the test patients, they're part of our raw data which is why I have to exclude them from the load (this SELECT is part of an INSERT INTO statement).

Another quick (I hope) question......
What's SARG?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search