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/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
--/*
,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'
) 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.