May 22, 2015 at 8:59 am
Hi all
I've got a piece of code that returns 53 records when using just the SELECT section.
When I change it to INSERT INTO ..... SELECT it only inserts 39 records into the receiving table.
There are no keys/contraints/indices or anything else on the receiving table (it's just a dumping ground for some data that will be processed later).
Anyone any ideas?
The code for creating the table is here:-
USE [CDSExtractInpatients6.2]
GO
/****** Object: Table [dbo].[CDS_Inpatients_CDS_Feeds_Import] Script Date: 22/05/2015 15:54:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CDS_Inpatients_CDS_Feeds_Import](
[CdsType] [varchar](3) NULL,
[CdsUniqueIdentifier] [varchar](35) NULL,
[Extract_DATE] [varchar](10) NULL,
[Report_Start_DATE] [varchar](10) NULL,
[Report_End_DATE] [varchar](10) NULL,
[Activity_Date] [varchar](10) NULL,
[Prime_Recipient] [varchar](5) NULL,
[Copy_1] [varchar](5) NULL,
[Copy_2] [varchar](5) NULL,
[Pathway_ID] [varchar](20) NULL,
[Pathway_Issuer] [varchar](5) NULL,
[RTTP_Status] [varchar](2) NULL,
[RTT_START_DATE] [varchar](10) NULL,
[RTT_END_DATE] [varchar](10) NULL,
[LocalPatientIdentifier] [varchar](10) NULL,
[LocalPatientIdentifierOrganisationCode] [varchar](5) NULL,
[NhsNumber] [varchar](10) NULL,
[NhsNumberStatusIndicator] [varchar](2) NULL,
[Forename] [varchar](35) NULL,
[Surname] [varchar](35) NULL,
[PatientUsualAddress1] [varchar](35) NULL,
[PatientUsualAddress2] [varchar](35) NULL,
[PatientUsualAddress3] [varchar](35) NULL,
[PatientUsualAddress4] [varchar](35) NULL,
[Postcode] [varchar](8) NULL,
[PCT_Res] [varchar](3) NULL,
[DOB] [varchar](10) NULL,
[PersonGenderCurrent] [varchar](1) NULL,
[EthnicCategory] [varchar](2) NULL,
[Live_StillBirth] [varchar](1) NULL,
[Birth_Weight] [varchar](4) NULL,
[HospitalProviderSpellNumber] [varchar](12) NULL,
[Pat_Category] [varchar](2) NULL,
[Pat_Class] [varchar](1) NULL,
[Admission_Method] [varchar](2) NULL,
[Source_of_Admission] [varchar](2) NULL,
[ADM_DATE] [varchar](10) NULL,
[AgeOnAdmission] [int] NULL,
[Discharge_Dest] [varchar](2) NULL,
[Discharge_Method] [varchar](2) NULL,
[Discharge_Date] [varchar](10) NULL,
[EpisodeNumber] [varchar](2) NULL,
[LastEpisodeInSpell] [varchar](1) NULL,
[OperationStatus] [varchar](1) NULL,
[NeonatalLevelOfCare] [varchar](1) NULL,
[Ep_Start_Date] [varchar](10) NULL,
[Ep_End_Date] [varchar](10) NULL,
[CommissioningSerialNumber] [varchar](6) NULL,
[Site_Code] [varchar](5) NULL,
[Commissioner] [varchar](5) NULL,
[Consultant_Code] [varchar](8) NULL,
[Main_Spec] [varchar](3) NULL,
[TreatmentFunctionCode] [varchar](3) NULL,
[Prim_Diag] [varchar](6) NULL,
[Diag_1] [varchar](6) NULL,
[Diag_2] [varchar](6) NULL,
[Diag_3] [varchar](6) NULL,
[Diag_4] [varchar](6) NULL,
[Diag_5] [varchar](6) NULL,
[Diag_6] [varchar](6) NULL,
[Diag_7] [varchar](6) NULL,
[Diag_8] [varchar](6) NULL,
[Diag_9] [varchar](6) NULL,
[Diag_10] [varchar](6) NULL,
[Diag_11] [varchar](6) NULL,
[Diag_12] [varchar](6) NULL,
[Prim_Proc] [varchar](4) NULL,
[Prim_Proc_DATE] [varchar](10) NULL,
[Proc_1] [varchar](4) NULL,
[OP1_DATE] [varchar](10) NULL,
[Proc_2] [varchar](4) NULL,
[OP2_Date] [varchar](10) NULL,
[Proc_3] [varchar](4) NULL,
[OP3_Date] [varchar](10) NULL,
[Proc_4] [varchar](4) NULL,
[OP4_DATE] [varchar](10) NULL,
[Proc_5] [varchar](4) NULL,
[OP5_DATE] [varchar](10) NULL,
[Proc_6] [varchar](4) NULL,
[OP6_DATE] [varchar](10) NULL,
[Proc_7] [varchar](4) NULL,
[OP7_DATE] [varchar](10) NULL,
[Proc_8] [varchar](4) NULL,
[OP8_DATE] [varchar](10) NULL,
[Proc_9] [varchar](4) NULL,
[OP9_DATE] [varchar](10) NULL,
[Proc_10] [varchar](4) NULL,
[OP10_DATE] [varchar](10) NULL,
[Proc_11] [varchar](4) NULL,
[OP11_DATE] [varchar](10) NULL,
[Proc_12] [varchar](4) NULL,
[OP12_DATE] [varchar](10) NULL,
[GP] [varchar](8) NULL,
[GP_Practice] [varchar](6) NULL,
[ReferrerCode] [varchar](8) NULL,
[ReferringOrganisationCode] [varchar](6) NULL,
[NumberofBabies] [varchar](1) NULL,
[DurationOfElectiveWait] [varchar](4) NULL,
[I_Mgmnt] [varchar](1) NULL,
[DTA_DATE] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
I know most of the date fields are being created as varchar on here, but this is something I inherited and the SELECT is outputting the dates as text.
Don't know if it makes any difference, but the server is running SQL2008.
May 22, 2015 at 9:06 am
Thanks for the table. Unfortunately you didn't post the select statement, the insert statement, the source table ddl or the sample data. From what you posted it sounds like maybe there is something wrong when you added "insert into" but without some details there is no way to know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2015 at 7:13 am
Hi Sean
Here's the insert statement:-
set dateformat dmy
truncate table [CDSExtractInpatients6.2].dbo.CDS_Inpatients_CDS_Feeds_Import
INSERT INTO [CDSExtractInpatients6.2].[dbo].[CDS_Inpatients_CDS_Feeds_Import]
SELECT
CdsType,
i.CdsUniqueIdentifier,
CONVERT(VARCHAR(10),CdsExtractDate,120) AS [Extract_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodStartDate,120) AS [Report_Start_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodEndDate,120) AS [Report_End_DATE],
CONVERT(VARCHAR(10),cdsactivitydate,120) AS [Activity_Date],
-- Updates Prime Recipients for Null & Old Pct_Codes
Case
When CdsPrimeRecipient like '5%' THEN ccg.CCG
When CdsPrimeRecipient LIKE 'Q51' THEN PctOfResidence
When CdsPrimeRecipient IS Null THEN '03L'
ELSE CdsPrimeRecipient
end as [Prime_Recipient],
-- Attempts to complete Copy_1 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When SCG.CdsUniqueIdentifier IS NOT NULL
AND (CdsCopyRecipients1 LIKE 'Q51' OR CdsCopyRecipients1 IS NULL) THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
When CdsCopyRecipients1 LIKE '5%' THEN ccg.CCG
Else ISNULL(CdsCopyRecipients1,'')
End,'') as [Copy_1],
-- Attempts to complete Copy_2 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When CdsCopyRecipients2 like 'Q%' THEN SCG.Comm
When CdsCopyRecipients2 IS Null AND CdsCopyRecipients1 Not like 'Q%'
AND TreatmentFunctionCode IN ('140','143') THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
When CdsCopyRecipients2 LIKE '5%' THEN ccg.CCG
Else IsNuLL(CdsCopyRecipients2,'')
End,'') as [Copy_2],
ISNULL(PatientPathwayIdentifier,'')as Pathway_ID,
ISNULL(PatientPathwayIssuerId,'')as Pathway_Issuer,
ISNULL(ReferralToTreatmentPeriodStatus,'') asRTTP_Status,
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodStartDate,120) AS [RTT_START_DATE],
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodEndDate,120) AS [RTT_END_DATE],
isnull(LocalPatientIdentifier,'') as LocalPatientIdentifier,
isnull(LocalPatientIdentifierOrganisationCode,'') as LocalPatientIdentifierOrganisationCode,
isnull(NhsNumber,'') as NhsNumber,
--Tidy's up incorrect combinations of NHS Number Status Codes
Case
When NhsNumberStatusIndicator like '01' and NhsNumber IS Null then '03'
When NhsNumber IS Null and NhsNumberStatusIndicator Not IN ('07','08') Then '07'
When PostcodeOfUsualAddress IS Null and NhsNumber IS Not Null and NhsNumberStatusIndicator like '01' then '02'
Else NhsNumberStatusIndicator
End as [NhsNumberStatusIndicator],
ISNULL(PatientGivenName,'')as Forename,
ISNULL(PatientFamilyName,'') as Surname,
ISNULL(REPLACE(PatientUsualAddress1,',',' '),'') AS PatientUsualAddress1,
ISNULL(REPLACE(PatientUsualAddress2,',',' '),'') AS PatientUsualAddress2,
ISNULL(REPLACE(PatientUsualAddress3,',',' '),'') AS PatientUsualAddress3,
ISNULL(REPLACE(PatientUsualAddress4,',',' '),'') AS PatientUsualAddress4,
ISNULL(PostcodeOfUsualAddress,'')as Postcode,
isnull(Case
When PctOfResidence like '5%' then ccg.CCG
When PostcodeOfUsualAddress Is Null then NuLL
Else PctOfResidence
End,'') as [PCT_Res],
isnull(CONVERT(VARCHAR(10),PatientDateofBirth,120),'') AS [DOB],
PersonGenderCurrent,
EthnicCategory,
IsNull(LiveOrStillBirth,'') as Live_StillBirth,
IsNull(BirthWeight,'') as Birth_Weight,
-- The below sets the Hospital Spell Number to '' for confidential procedures
Case
When NhsNumberStatusIndicator like '07' THEN ''
Else HospitalProviderSpellNumber
End as [HospitalProviderSpellNumber],
-- Updates an Null Patient Category to 01 (NHS) where is Null
Case
When AdministrativeCategoryOnAdmission IS Null then '01'
Else ISNULL(AdministrativeCategoryOnAdmission,'')
End as [Pat_Category],
-- Updates any codes of 3 (RDA)to daycase. To be picked up by DQ Team
Case
When PatientClassification like '3' then '2'
Else PatientClassification
End as [Pat_Class],
--- Updates Admission_Method Codes for Deliveries. To be picked up by DQ Team as this is user error!
isnull(Case
When AdmissionMethod like '28' and CdsType like '140' then '31'
Else AdmissionMethod
End,'') as [Admission_Method],
-- Updates SourceofAdmission code where Null
Case
When SourceOfAdmission IS Null then '19'
Else SourceOfAdmission
End as [Source_of_Admission],
CONVERT(VARCHAR(10),StartDateOfSpell,120) AS [ADM_DATE],
AgeOnAdmission,
-- Updates Discharge_Destination where Null
Case
When DischargeDestination IS Null and DischargeDate IS Not Null Then '19'
Else IsNull(DischargeDestination,'')
End as [Discharge_Dest],
--Updates Discharge_Method where incorrect 'A&E' Codes have been chosen
Case
When DischargeMethod Like '10' and DischargeDate IS Not Null THEN '4'
When DischargeMethod Like '0%' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '11' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '13' and DischargeDate IS Not Null THEN '1'
Else ISNULL(DischargeMethod,'')
End as [Discharge_Method],
CONVERT(VARCHAR(10),DischargeDate,120) AS [Discharge_Date],
EpisodeNumber,
LastEpisodeInSpell,
OperationStatus,
NeonatalLevelOfCare,
CONVERT(VARCHAR(10),EpisodeStartDate,120) AS [Ep_Start_Date],
CONVERT(VARCHAR(10),EpisodeEndDate,120) AS [Ep_End_Date],
-- Set serial number for photphersis as different
isnull(Case
when a.AbsServiceID like 'PHOTOPHSIS' THEN 'NPSA0='
else CommissioningSerialNumber
End,'') as [CommissioningSerialNumber],
--Creates Site Code for DRI OMFS, otherwise will default to RFR00
Case
When ConsultantCode like 'C4014531' then 'RP5RH'
Else CodeOfProvider
End as [Site_Code],
Case
when SCG.CdsUniqueIdentifier IS NOT NULL THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when i.CodeOfCommissioner LIKE '5%' then ccg.CCG
when i.CodeOfCommissioner IS Null then '03L'
ELSE i.CodeOfCommissioner
end as [Commissioner],
Case
When ConsultantCode IS Null then 'C9999998'
Else ConsultantCode
End as [Consultant_Code],
Case
When MainSpecialtyCode like '560' then '501'
When MainSpecialtyCode like '822' then '303'
when MainSpecialtyCode like '960' and TreatmentFunctionCode like '658' then '110'
else MainSpecialtyCode
End as [Main_Spec],
TreatmentFunctionCode,
ISNULL(i.PrimaryDiagnosisIcd,'')as Prim_Diag,
ISNULL(SecondaryDiagnosisIcd1,'')as Diag_1,
ISNULL(SecondaryDiagnosisIcd2,'')as Diag_2,
ISNULL(SecondaryDiagnosisIcd3,'')as Diag_3,
ISNULL(SecondaryDiagnosisIcd4,'')as Diag_4,
ISNULL(SecondaryDiagnosisIcd5,'')as Diag_5,
ISNULL(SecondaryDiagnosisIcd6,'')as Diag_6,
ISNULL(SecondaryDiagnosisIcd7,'')as Diag_7,
ISNULL(SecondaryDiagnosisIcd8,'')as Diag_8,
ISNULL(SecondaryDiagnosisIcd9,'')as Diag_9,
ISNULL(SecondaryDiagnosisIcd10,'')as Diag_10,
ISNULL(SecondaryDiagnosisIcd11,'')as Diag_11,
ISNULL(SecondaryDiagnosisIcd12,'')as Diag_12,
ISNULL(PrimaryProcedureOpcs,'')as Prim_Proc,
CONVERT(VARCHAR(10),PrimaryProcedureDate,120) AS [Prim_Proc_DATE],
ISNULL(SecondaryProcedureOpcs1,'')as Proc_1,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs1Date,120) AS [OP1_DATE],
ISNULL(SecondaryProcedureOpcs2,'') as Proc_2,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs2Date,120) AS [OP2_Date],
ISNULL (SecondaryProcedureOpcs3,'') as Proc_3,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs3Date,120) AS [OP3_Date],
ISNULL(SecondaryProcedureOpcs4,'') as Proc_4,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs4Date,120) AS [OP4_DATE],
ISNULL(SecondaryProcedureOpcs5,'') as Proc_5,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs5Date,120) AS [OP5_DATE],
ISNULL(SecondaryProcedureOpcs6,'') as Proc_6,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs6Date,120) AS [OP6_DATE],
ISNULL(SecondaryProcedureOpcs7,'')as Proc_7,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs7Date,120) AS [OP7_DATE],
ISNULL(SecondaryProcedureOpcs8,'')as Proc_8,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs8Date,120) AS [OP8_DATE],
ISNULL(SecondaryProcedureOpcs9,'')as Proc_9,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs9Date,120) AS [OP9_DATE],
ISNULL(SecondaryProcedureOpcs10,'')as Proc_10,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs10Date,120) AS [OP10_DATE],
ISNULL(SecondaryProcedureOpcs11,'')as Proc_11,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs11Date,120) AS [OP11_DATE],
ISNULL(SecondaryProcedureOpcs12,'')as Proc_12,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs12Date,120) AS [OP12_DATE],
--- Tidy's up Null GP codes. Should be picked up by Data Quality Team
Case
When GeneralMedicalPractitioner IS Null then 'G9999998'
Else GeneralMedicalPractitioner
End as [GP],
-- Tidy's up GP Practice where is Null. Should be picked up by Data Quality Team
Case
When i.GeneralMedicalPractice Is Null then 'V81999'
ELSE i.GeneralMedicalPractice
End as [GP_Practice],
Case
When ReferrerCode IS Null then 'X9999998'
Else ReferrerCode
End as [ReferrerCode],
Case
When ReferringOrganisationCode IS Null then 'X99998'
when ReferringOrganisationCode = 'RFR00' then 'RFRPA' -- Needs to be removed once fixed in Meditech
Else ReferringOrganisationCode
End as [ReferringOrganisationCode],
ISNULL(NumberOfBabies,'') as [NumberofBabies],
Case
When DurationOfElectiveWait like '-%' then ''
Else ISNULL(DurationOfElectiveWait,'')
End as [DurationOfElectiveWait],
ISNULL(IntendedManagement,'') as I_Mgmnt,
CONVERT(VARCHAR(10),DecidedToAdmitDate,120) AS [DTA_DATE]
from
RFT_CDS_REPORTING.dbo.CDS_Inpatients i
------------------------------------------------------------------------------------------------
--Exclude ENT DRI & sets serioal number for Photophersis
Left Outer Join RFT_RS_REPORTING.dbo.RFT_DR_ABS a
on i.HospitalProviderSpellNumber=a.AccountNumber
AND i.EpisodeNumber=a.ServiceSeqID
-----------------------------------------------------------------------------
-- Adjust Code of Commissioner to SCG for Oral Surgery & Orthodontics
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
CASE
WHEN gmp.[HA Code] IS NULL THEN 'Q51'
WHEN gmp.[HA Code] IN ('Q32','Q33') THEN 'Q51'
ELSE [HA Code]
END as [Comm]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and TreatmentFunctionCode IN ('140','143'))SCG
ON i.CdsUniqueIdentifier=SCG.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and AgeAtCdsActivityDate >24
AND cc.AgeCat = 1) SCG1
ON i.CdsUniqueIdentifier=SCG1.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG_2 Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@initial
And AgeAtCdsActivityDate between 19 and 24
AND cc.AgeCat = 2)SCG2
ON i.CdsUniqueIdentifier=SCG2.CdsUniqueIdentifier
-----------------------------------------------------------------------------------------------------
-- Create link to CCG look_Up to clean up any old PCT Codes
LEFT OUTER JOIN RFT_LOOKUPS.dbo.LU_PCT_CCG_MAP ccg
on i.CodeOfCommissioner=ccg.PCT
-----------------------------------------------------------------------------------------------------
where
(PatientGivenName IS NULL OR PatientGivenName NOT LIKE 'DONOTUSE')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'YYTESTPATIENTII')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'XXTESTPATIENT%')
AND cast(cdsactivitydate as date) between @start and @end
--And CdsType Not LIKE '120'
-- Excludes ENTDRI as DRI submit this
AND a.AbsServiceID != 'ENTDRI'
AND (a.LatestWardLocationInSpell!='DRI' OR a.LatestWardLocationInSpell IS NULL) --SL 20150312 Some Don ENT records have "ENT" service id
--AND HospitalProviderSpellNumber Like 'RA0000776946'
AND MainSpecialtyCode NOT IN ('800','810') --Exclude Radiology, needs to be uploaded seperately
All the relevant records are available in the source view (RFT_CDS_REPORTING.dbo.CDS_Inpatients) and can be selected. It's only when I insert them into another table, the records fall off for no apparent reason.
I'll have to make up some raw data for you as the stuff we have has identifiable data in it.
I'll get that to you as soon as I can.
May 28, 2015 at 7:25 am
richardmgreen1 (5/28/2015)
Hi SeanHere's the insert statement:-
set dateformat dmy
truncate table [CDSExtractInpatients6.2].dbo.CDS_Inpatients_CDS_Feeds_Import
INSERT INTO [CDSExtractInpatients6.2].[dbo].[CDS_Inpatients_CDS_Feeds_Import]
SELECT
CdsType,
i.CdsUniqueIdentifier,
CONVERT(VARCHAR(10),CdsExtractDate,120) AS [Extract_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodStartDate,120) AS [Report_Start_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodEndDate,120) AS [Report_End_DATE],
CONVERT(VARCHAR(10),cdsactivitydate,120) AS [Activity_Date],
-- Updates Prime Recipients for Null & Old Pct_Codes
Case
When CdsPrimeRecipient like '5%' THEN ccg.CCG
When CdsPrimeRecipient LIKE 'Q51' THEN PctOfResidence
When CdsPrimeRecipient IS Null THEN '03L'
ELSE CdsPrimeRecipient
end as [Prime_Recipient],
-- Attempts to complete Copy_1 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When SCG.CdsUniqueIdentifier IS NOT NULL
AND (CdsCopyRecipients1 LIKE 'Q51' OR CdsCopyRecipients1 IS NULL) THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
When CdsCopyRecipients1 LIKE '5%' THEN ccg.CCG
Else ISNULL(CdsCopyRecipients1,'')
End,'') as [Copy_1],
-- Attempts to complete Copy_2 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When CdsCopyRecipients2 like 'Q%' THEN SCG.Comm
When CdsCopyRecipients2 IS Null AND CdsCopyRecipients1 Not like 'Q%'
AND TreatmentFunctionCode IN ('140','143') THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
When CdsCopyRecipients2 LIKE '5%' THEN ccg.CCG
Else IsNuLL(CdsCopyRecipients2,'')
End,'') as [Copy_2],
ISNULL(PatientPathwayIdentifier,'')as Pathway_ID,
ISNULL(PatientPathwayIssuerId,'')as Pathway_Issuer,
ISNULL(ReferralToTreatmentPeriodStatus,'') asRTTP_Status,
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodStartDate,120) AS [RTT_START_DATE],
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodEndDate,120) AS [RTT_END_DATE],
isnull(LocalPatientIdentifier,'') as LocalPatientIdentifier,
isnull(LocalPatientIdentifierOrganisationCode,'') as LocalPatientIdentifierOrganisationCode,
isnull(NhsNumber,'') as NhsNumber,
--Tidy's up incorrect combinations of NHS Number Status Codes
Case
When NhsNumberStatusIndicator like '01' and NhsNumber IS Null then '03'
When NhsNumber IS Null and NhsNumberStatusIndicator Not IN ('07','08') Then '07'
When PostcodeOfUsualAddress IS Null and NhsNumber IS Not Null and NhsNumberStatusIndicator like '01' then '02'
Else NhsNumberStatusIndicator
End as [NhsNumberStatusIndicator],
ISNULL(PatientGivenName,'')as Forename,
ISNULL(PatientFamilyName,'') as Surname,
ISNULL(REPLACE(PatientUsualAddress1,',',' '),'') AS PatientUsualAddress1,
ISNULL(REPLACE(PatientUsualAddress2,',',' '),'') AS PatientUsualAddress2,
ISNULL(REPLACE(PatientUsualAddress3,',',' '),'') AS PatientUsualAddress3,
ISNULL(REPLACE(PatientUsualAddress4,',',' '),'') AS PatientUsualAddress4,
ISNULL(PostcodeOfUsualAddress,'')as Postcode,
isnull(Case
When PctOfResidence like '5%' then ccg.CCG
When PostcodeOfUsualAddress Is Null then NuLL
Else PctOfResidence
End,'') as [PCT_Res],
isnull(CONVERT(VARCHAR(10),PatientDateofBirth,120),'') AS [DOB],
PersonGenderCurrent,
EthnicCategory,
IsNull(LiveOrStillBirth,'') as Live_StillBirth,
IsNull(BirthWeight,'') as Birth_Weight,
-- The below sets the Hospital Spell Number to '' for confidential procedures
Case
When NhsNumberStatusIndicator like '07' THEN ''
Else HospitalProviderSpellNumber
End as [HospitalProviderSpellNumber],
-- Updates an Null Patient Category to 01 (NHS) where is Null
Case
When AdministrativeCategoryOnAdmission IS Null then '01'
Else ISNULL(AdministrativeCategoryOnAdmission,'')
End as [Pat_Category],
-- Updates any codes of 3 (RDA)to daycase. To be picked up by DQ Team
Case
When PatientClassification like '3' then '2'
Else PatientClassification
End as [Pat_Class],
--- Updates Admission_Method Codes for Deliveries. To be picked up by DQ Team as this is user error!
isnull(Case
When AdmissionMethod like '28' and CdsType like '140' then '31'
Else AdmissionMethod
End,'') as [Admission_Method],
-- Updates SourceofAdmission code where Null
Case
When SourceOfAdmission IS Null then '19'
Else SourceOfAdmission
End as [Source_of_Admission],
CONVERT(VARCHAR(10),StartDateOfSpell,120) AS [ADM_DATE],
AgeOnAdmission,
-- Updates Discharge_Destination where Null
Case
When DischargeDestination IS Null and DischargeDate IS Not Null Then '19'
Else IsNull(DischargeDestination,'')
End as [Discharge_Dest],
--Updates Discharge_Method where incorrect 'A&E' Codes have been chosen
Case
When DischargeMethod Like '10' and DischargeDate IS Not Null THEN '4'
When DischargeMethod Like '0%' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '11' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '13' and DischargeDate IS Not Null THEN '1'
Else ISNULL(DischargeMethod,'')
End as [Discharge_Method],
CONVERT(VARCHAR(10),DischargeDate,120) AS [Discharge_Date],
EpisodeNumber,
LastEpisodeInSpell,
OperationStatus,
NeonatalLevelOfCare,
CONVERT(VARCHAR(10),EpisodeStartDate,120) AS [Ep_Start_Date],
CONVERT(VARCHAR(10),EpisodeEndDate,120) AS [Ep_End_Date],
-- Set serial number for photphersis as different
isnull(Case
when a.AbsServiceID like 'PHOTOPHSIS' THEN 'NPSA0='
else CommissioningSerialNumber
End,'') as [CommissioningSerialNumber],
--Creates Site Code for DRI OMFS, otherwise will default to RFR00
Case
When ConsultantCode like 'C4014531' then 'RP5RH'
Else CodeOfProvider
End as [Site_Code],
Case
when SCG.CdsUniqueIdentifier IS NOT NULL THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when i.CodeOfCommissioner LIKE '5%' then ccg.CCG
when i.CodeOfCommissioner IS Null then '03L'
ELSE i.CodeOfCommissioner
end as [Commissioner],
Case
When ConsultantCode IS Null then 'C9999998'
Else ConsultantCode
End as [Consultant_Code],
Case
When MainSpecialtyCode like '560' then '501'
When MainSpecialtyCode like '822' then '303'
when MainSpecialtyCode like '960' and TreatmentFunctionCode like '658' then '110'
else MainSpecialtyCode
End as [Main_Spec],
TreatmentFunctionCode,
ISNULL(i.PrimaryDiagnosisIcd,'')as Prim_Diag,
ISNULL(SecondaryDiagnosisIcd1,'')as Diag_1,
ISNULL(SecondaryDiagnosisIcd2,'')as Diag_2,
ISNULL(SecondaryDiagnosisIcd3,'')as Diag_3,
ISNULL(SecondaryDiagnosisIcd4,'')as Diag_4,
ISNULL(SecondaryDiagnosisIcd5,'')as Diag_5,
ISNULL(SecondaryDiagnosisIcd6,'')as Diag_6,
ISNULL(SecondaryDiagnosisIcd7,'')as Diag_7,
ISNULL(SecondaryDiagnosisIcd8,'')as Diag_8,
ISNULL(SecondaryDiagnosisIcd9,'')as Diag_9,
ISNULL(SecondaryDiagnosisIcd10,'')as Diag_10,
ISNULL(SecondaryDiagnosisIcd11,'')as Diag_11,
ISNULL(SecondaryDiagnosisIcd12,'')as Diag_12,
ISNULL(PrimaryProcedureOpcs,'')as Prim_Proc,
CONVERT(VARCHAR(10),PrimaryProcedureDate,120) AS [Prim_Proc_DATE],
ISNULL(SecondaryProcedureOpcs1,'')as Proc_1,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs1Date,120) AS [OP1_DATE],
ISNULL(SecondaryProcedureOpcs2,'') as Proc_2,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs2Date,120) AS [OP2_Date],
ISNULL (SecondaryProcedureOpcs3,'') as Proc_3,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs3Date,120) AS [OP3_Date],
ISNULL(SecondaryProcedureOpcs4,'') as Proc_4,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs4Date,120) AS [OP4_DATE],
ISNULL(SecondaryProcedureOpcs5,'') as Proc_5,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs5Date,120) AS [OP5_DATE],
ISNULL(SecondaryProcedureOpcs6,'') as Proc_6,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs6Date,120) AS [OP6_DATE],
ISNULL(SecondaryProcedureOpcs7,'')as Proc_7,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs7Date,120) AS [OP7_DATE],
ISNULL(SecondaryProcedureOpcs8,'')as Proc_8,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs8Date,120) AS [OP8_DATE],
ISNULL(SecondaryProcedureOpcs9,'')as Proc_9,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs9Date,120) AS [OP9_DATE],
ISNULL(SecondaryProcedureOpcs10,'')as Proc_10,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs10Date,120) AS [OP10_DATE],
ISNULL(SecondaryProcedureOpcs11,'')as Proc_11,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs11Date,120) AS [OP11_DATE],
ISNULL(SecondaryProcedureOpcs12,'')as Proc_12,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs12Date,120) AS [OP12_DATE],
--- Tidy's up Null GP codes. Should be picked up by Data Quality Team
Case
When GeneralMedicalPractitioner IS Null then 'G9999998'
Else GeneralMedicalPractitioner
End as [GP],
-- Tidy's up GP Practice where is Null. Should be picked up by Data Quality Team
Case
When i.GeneralMedicalPractice Is Null then 'V81999'
ELSE i.GeneralMedicalPractice
End as [GP_Practice],
Case
When ReferrerCode IS Null then 'X9999998'
Else ReferrerCode
End as [ReferrerCode],
Case
When ReferringOrganisationCode IS Null then 'X99998'
when ReferringOrganisationCode = 'RFR00' then 'RFRPA' -- Needs to be removed once fixed in Meditech
Else ReferringOrganisationCode
End as [ReferringOrganisationCode],
ISNULL(NumberOfBabies,'') as [NumberofBabies],
Case
When DurationOfElectiveWait like '-%' then ''
Else ISNULL(DurationOfElectiveWait,'')
End as [DurationOfElectiveWait],
ISNULL(IntendedManagement,'') as I_Mgmnt,
CONVERT(VARCHAR(10),DecidedToAdmitDate,120) AS [DTA_DATE]
from
RFT_CDS_REPORTING.dbo.CDS_Inpatients i
------------------------------------------------------------------------------------------------
--Exclude ENT DRI & sets serioal number for Photophersis
Left Outer Join RFT_RS_REPORTING.dbo.RFT_DR_ABS a
on i.HospitalProviderSpellNumber=a.AccountNumber
AND i.EpisodeNumber=a.ServiceSeqID
-----------------------------------------------------------------------------
-- Adjust Code of Commissioner to SCG for Oral Surgery & Orthodontics
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
CASE
WHEN gmp.[HA Code] IS NULL THEN 'Q51'
WHEN gmp.[HA Code] IN ('Q32','Q33') THEN 'Q51'
ELSE [HA Code]
END as [Comm]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and TreatmentFunctionCode IN ('140','143'))SCG
ON i.CdsUniqueIdentifier=SCG.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and AgeAtCdsActivityDate >24
AND cc.AgeCat = 1) SCG1
ON i.CdsUniqueIdentifier=SCG1.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG_2 Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@initial
And AgeAtCdsActivityDate between 19 and 24
AND cc.AgeCat = 2)SCG2
ON i.CdsUniqueIdentifier=SCG2.CdsUniqueIdentifier
-----------------------------------------------------------------------------------------------------
-- Create link to CCG look_Up to clean up any old PCT Codes
LEFT OUTER JOIN RFT_LOOKUPS.dbo.LU_PCT_CCG_MAP ccg
on i.CodeOfCommissioner=ccg.PCT
-----------------------------------------------------------------------------------------------------
where
(PatientGivenName IS NULL OR PatientGivenName NOT LIKE 'DONOTUSE')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'YYTESTPATIENTII')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'XXTESTPATIENT%')
AND cast(cdsactivitydate as date) between @start and @end
--And CdsType Not LIKE '120'
-- Excludes ENTDRI as DRI submit this
AND a.AbsServiceID != 'ENTDRI'
AND (a.LatestWardLocationInSpell!='DRI' OR a.LatestWardLocationInSpell IS NULL) --SL 20150312 Some Don ENT records have "ENT" service id
--AND HospitalProviderSpellNumber Like 'RA0000776946'
AND MainSpecialtyCode NOT IN ('800','810') --Exclude Radiology, needs to be uploaded seperately
All the relevant records are available in the source view (RFT_CDS_REPORTING.dbo.CDS_Inpatients) and can be selected. It's only when I insert them into another table, the records fall off for no apparent reason.
I'll have to make up some raw data for you as the stuff we have has identifiable data in it.
I'll get that to you as soon as I can.
Wow that is quite a select statement. I don't quite get what is happening. You are saying that when you run just the select statement you get some number of rows returned, but when you run this with the insert you get fewer rows inserted than the select statement returns? You have either uncovered the most rare bug yet in sql server or there is something else going on.
Try this code. The first part gets a count of rows that will be inserted. Then we do the insert and check @@ROWCOUNT Assuming there are no insert triggers on your table this should be accurate.
truncate table [CDSExtractInpatients6.2].dbo.CDS_Inpatients_CDS_Feeds_Import
select COUNT(*)
from
RFT_CDS_REPORTING.dbo.CDS_Inpatients i
------------------------------------------------------------------------------------------------
--Exclude ENT DRI & sets serioal number for Photophersis
Left Outer Join RFT_RS_REPORTING.dbo.RFT_DR_ABS a
on i.HospitalProviderSpellNumber=a.AccountNumber
AND i.EpisodeNumber=a.ServiceSeqID
-----------------------------------------------------------------------------
-- Adjust Code of Commissioner to SCG for Oral Surgery & Orthodontics
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
CASE
WHEN gmp.[HA Code] IS NULL THEN 'Q51'
WHEN gmp.[HA Code] IN ('Q32','Q33') THEN 'Q51'
ELSE [HA Code]
END as [Comm]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and TreatmentFunctionCode IN ('140','143'))SCG
ON i.CdsUniqueIdentifier=SCG.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and AgeAtCdsActivityDate >24
AND cc.AgeCat = 1) SCG1
ON i.CdsUniqueIdentifier=SCG1.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG_2 Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@initial
And AgeAtCdsActivityDate between 19 and 24
AND cc.AgeCat = 2)SCG2
ON i.CdsUniqueIdentifier=SCG2.CdsUniqueIdentifier
-----------------------------------------------------------------------------------------------------
-- Create link to CCG look_Up to clean up any old PCT Codes
LEFT OUTER JOIN RFT_LOOKUPS.dbo.LU_PCT_CCG_MAP ccg
on i.CodeOfCommissioner=ccg.PCT
-----------------------------------------------------------------------------------------------------
where
(PatientGivenName IS NULL OR PatientGivenName NOT LIKE 'DONOTUSE')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'YYTESTPATIENTII')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'XXTESTPATIENT%')
AND cast(cdsactivitydate as date) between @start and @end
--And CdsType Not LIKE '120'
-- Excludes ENTDRI as DRI submit this
AND a.AbsServiceID != 'ENTDRI'
AND (a.LatestWardLocationInSpell!='DRI' OR a.LatestWardLocationInSpell IS NULL) --SL 20150312 Some Don ENT records have "ENT" service id
--AND HospitalProviderSpellNumber Like 'RA0000776946'
AND MainSpecialtyCode NOT IN ('800','810') --Exclude Radiology, needs to be uploaded seperately
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
--Now we will do the insert and compare row counts
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
--#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@
INSERT INTO [CDSExtractInpatients6.2].[dbo].[CDS_Inpatients_CDS_Feeds_Import]
SELECT
CdsType,
i.CdsUniqueIdentifier,
CONVERT(VARCHAR(10),CdsExtractDate,120) AS [Extract_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodStartDate,120) AS [Report_Start_DATE],
CONVERT(VARCHAR(10),CdsReportPeriodEndDate,120) AS [Report_End_DATE],
CONVERT(VARCHAR(10),cdsactivitydate,120) AS [Activity_Date],
-- Updates Prime Recipients for Null & Old Pct_Codes
Case
When CdsPrimeRecipient like '5%' THEN ccg.CCG
When CdsPrimeRecipient LIKE 'Q51' THEN PctOfResidence
When CdsPrimeRecipient IS Null THEN '03L'
ELSE CdsPrimeRecipient
end as [Prime_Recipient],
-- Attempts to complete Copy_1 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When SCG.CdsUniqueIdentifier IS NOT NULL
AND (CdsCopyRecipients1 LIKE 'Q51' OR CdsCopyRecipients1 IS NULL) THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL and CdsCopyRecipients1 IS NULL THEN 'Q51'
When CdsCopyRecipients1 LIKE '5%' THEN ccg.CCG
Else ISNULL(CdsCopyRecipients1,'')
End,'') as [Copy_1],
-- Attempts to complete Copy_2 for all SCG eventualities. Very Difficult!!! Probably could do with reviewing
isnull(Case
When CdsCopyRecipients2 like 'Q%' THEN SCG.Comm
When CdsCopyRecipients2 IS Null AND CdsCopyRecipients1 Not like 'Q%'
AND TreatmentFunctionCode IN ('140','143') THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL AND CdsCopyRecipients1 NOT LIKE 'Q%' THEN 'Q51'
When CdsCopyRecipients2 LIKE '5%' THEN ccg.CCG
Else IsNuLL(CdsCopyRecipients2,'')
End,'') as [Copy_2],
ISNULL(PatientPathwayIdentifier,'')as Pathway_ID,
ISNULL(PatientPathwayIssuerId,'')as Pathway_Issuer,
ISNULL(ReferralToTreatmentPeriodStatus,'') asRTTP_Status,
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodStartDate,120) AS [RTT_START_DATE],
CONVERT(VARCHAR(10),ReferralToTreatmentPeriodEndDate,120) AS [RTT_END_DATE],
isnull(LocalPatientIdentifier,'') as LocalPatientIdentifier,
isnull(LocalPatientIdentifierOrganisationCode,'') as LocalPatientIdentifierOrganisationCode,
isnull(NhsNumber,'') as NhsNumber,
--Tidy's up incorrect combinations of NHS Number Status Codes
Case
When NhsNumberStatusIndicator like '01' and NhsNumber IS Null then '03'
When NhsNumber IS Null and NhsNumberStatusIndicator Not IN ('07','08') Then '07'
When PostcodeOfUsualAddress IS Null and NhsNumber IS Not Null and NhsNumberStatusIndicator like '01' then '02'
Else NhsNumberStatusIndicator
End as [NhsNumberStatusIndicator],
ISNULL(PatientGivenName,'')as Forename,
ISNULL(PatientFamilyName,'') as Surname,
ISNULL(REPLACE(PatientUsualAddress1,',',' '),'') AS PatientUsualAddress1,
ISNULL(REPLACE(PatientUsualAddress2,',',' '),'') AS PatientUsualAddress2,
ISNULL(REPLACE(PatientUsualAddress3,',',' '),'') AS PatientUsualAddress3,
ISNULL(REPLACE(PatientUsualAddress4,',',' '),'') AS PatientUsualAddress4,
ISNULL(PostcodeOfUsualAddress,'')as Postcode,
isnull(Case
When PctOfResidence like '5%' then ccg.CCG
When PostcodeOfUsualAddress Is Null then NuLL
Else PctOfResidence
End,'') as [PCT_Res],
isnull(CONVERT(VARCHAR(10),PatientDateofBirth,120),'') AS [DOB],
PersonGenderCurrent,
EthnicCategory,
IsNull(LiveOrStillBirth,'') as Live_StillBirth,
IsNull(BirthWeight,'') as Birth_Weight,
-- The below sets the Hospital Spell Number to '' for confidential procedures
Case
When NhsNumberStatusIndicator like '07' THEN ''
Else HospitalProviderSpellNumber
End as [HospitalProviderSpellNumber],
-- Updates an Null Patient Category to 01 (NHS) where is Null
Case
When AdministrativeCategoryOnAdmission IS Null then '01'
Else ISNULL(AdministrativeCategoryOnAdmission,'')
End as [Pat_Category],
-- Updates any codes of 3 (RDA)to daycase. To be picked up by DQ Team
Case
When PatientClassification like '3' then '2'
Else PatientClassification
End as [Pat_Class],
--- Updates Admission_Method Codes for Deliveries. To be picked up by DQ Team as this is user error!
isnull(Case
When AdmissionMethod like '28' and CdsType like '140' then '31'
Else AdmissionMethod
End,'') as [Admission_Method],
-- Updates SourceofAdmission code where Null
Case
When SourceOfAdmission IS Null then '19'
Else SourceOfAdmission
End as [Source_of_Admission],
CONVERT(VARCHAR(10),StartDateOfSpell,120) AS [ADM_DATE],
AgeOnAdmission,
-- Updates Discharge_Destination where Null
Case
When DischargeDestination IS Null and DischargeDate IS Not Null Then '19'
Else IsNull(DischargeDestination,'')
End as [Discharge_Dest],
--Updates Discharge_Method where incorrect 'A&E' Codes have been chosen
Case
When DischargeMethod Like '10' and DischargeDate IS Not Null THEN '4'
When DischargeMethod Like '0%' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '11' and DischargeDate IS Not Null THEN '1'
When DischargeMethod Like '13' and DischargeDate IS Not Null THEN '1'
Else ISNULL(DischargeMethod,'')
End as [Discharge_Method],
CONVERT(VARCHAR(10),DischargeDate,120) AS [Discharge_Date],
EpisodeNumber,
LastEpisodeInSpell,
OperationStatus,
NeonatalLevelOfCare,
CONVERT(VARCHAR(10),EpisodeStartDate,120) AS [Ep_Start_Date],
CONVERT(VARCHAR(10),EpisodeEndDate,120) AS [Ep_End_Date],
-- Set serial number for photphersis as different
isnull(Case
when a.AbsServiceID like 'PHOTOPHSIS' THEN 'NPSA0='
else CommissioningSerialNumber
End,'') as [CommissioningSerialNumber],
--Creates Site Code for DRI OMFS, otherwise will default to RFR00
Case
When ConsultantCode like 'C4014531' then 'RP5RH'
Else CodeOfProvider
End as [Site_Code],
Case
when SCG.CdsUniqueIdentifier IS NOT NULL THEN SCG.Comm
when SCG1.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when SCG2.CdsUniqueIdentifier IS NOT NULL THEN 'Q51'
when i.CodeOfCommissioner LIKE '5%' then ccg.CCG
when i.CodeOfCommissioner IS Null then '03L'
ELSE i.CodeOfCommissioner
end as [Commissioner],
Case
When ConsultantCode IS Null then 'C9999998'
Else ConsultantCode
End as [Consultant_Code],
Case
When MainSpecialtyCode like '560' then '501'
When MainSpecialtyCode like '822' then '303'
when MainSpecialtyCode like '960' and TreatmentFunctionCode like '658' then '110'
else MainSpecialtyCode
End as [Main_Spec],
TreatmentFunctionCode,
ISNULL(i.PrimaryDiagnosisIcd,'')as Prim_Diag,
ISNULL(SecondaryDiagnosisIcd1,'')as Diag_1,
ISNULL(SecondaryDiagnosisIcd2,'')as Diag_2,
ISNULL(SecondaryDiagnosisIcd3,'')as Diag_3,
ISNULL(SecondaryDiagnosisIcd4,'')as Diag_4,
ISNULL(SecondaryDiagnosisIcd5,'')as Diag_5,
ISNULL(SecondaryDiagnosisIcd6,'')as Diag_6,
ISNULL(SecondaryDiagnosisIcd7,'')as Diag_7,
ISNULL(SecondaryDiagnosisIcd8,'')as Diag_8,
ISNULL(SecondaryDiagnosisIcd9,'')as Diag_9,
ISNULL(SecondaryDiagnosisIcd10,'')as Diag_10,
ISNULL(SecondaryDiagnosisIcd11,'')as Diag_11,
ISNULL(SecondaryDiagnosisIcd12,'')as Diag_12,
ISNULL(PrimaryProcedureOpcs,'')as Prim_Proc,
CONVERT(VARCHAR(10),PrimaryProcedureDate,120) AS [Prim_Proc_DATE],
ISNULL(SecondaryProcedureOpcs1,'')as Proc_1,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs1Date,120) AS [OP1_DATE],
ISNULL(SecondaryProcedureOpcs2,'') as Proc_2,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs2Date,120) AS [OP2_Date],
ISNULL (SecondaryProcedureOpcs3,'') as Proc_3,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs3Date,120) AS [OP3_Date],
ISNULL(SecondaryProcedureOpcs4,'') as Proc_4,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs4Date,120) AS [OP4_DATE],
ISNULL(SecondaryProcedureOpcs5,'') as Proc_5,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs5Date,120) AS [OP5_DATE],
ISNULL(SecondaryProcedureOpcs6,'') as Proc_6,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs6Date,120) AS [OP6_DATE],
ISNULL(SecondaryProcedureOpcs7,'')as Proc_7,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs7Date,120) AS [OP7_DATE],
ISNULL(SecondaryProcedureOpcs8,'')as Proc_8,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs8Date,120) AS [OP8_DATE],
ISNULL(SecondaryProcedureOpcs9,'')as Proc_9,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs9Date,120) AS [OP9_DATE],
ISNULL(SecondaryProcedureOpcs10,'')as Proc_10,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs10Date,120) AS [OP10_DATE],
ISNULL(SecondaryProcedureOpcs11,'')as Proc_11,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs11Date,120) AS [OP11_DATE],
ISNULL(SecondaryProcedureOpcs12,'')as Proc_12,
CONVERT(VARCHAR(10),SecondaryProcedureOpcs12Date,120) AS [OP12_DATE],
--- Tidy's up Null GP codes. Should be picked up by Data Quality Team
Case
When GeneralMedicalPractitioner IS Null then 'G9999998'
Else GeneralMedicalPractitioner
End as [GP],
-- Tidy's up GP Practice where is Null. Should be picked up by Data Quality Team
Case
When i.GeneralMedicalPractice Is Null then 'V81999'
ELSE i.GeneralMedicalPractice
End as [GP_Practice],
Case
When ReferrerCode IS Null then 'X9999998'
Else ReferrerCode
End as [ReferrerCode],
Case
When ReferringOrganisationCode IS Null then 'X99998'
when ReferringOrganisationCode = 'RFR00' then 'RFRPA' -- Needs to be removed once fixed in Meditech
Else ReferringOrganisationCode
End as [ReferringOrganisationCode],
ISNULL(NumberOfBabies,'') as [NumberofBabies],
Case
When DurationOfElectiveWait like '-%' then ''
Else ISNULL(DurationOfElectiveWait,'')
End as [DurationOfElectiveWait],
ISNULL(IntendedManagement,'') as I_Mgmnt,
CONVERT(VARCHAR(10),DecidedToAdmitDate,120) AS [DTA_DATE]
from
RFT_CDS_REPORTING.dbo.CDS_Inpatients i
------------------------------------------------------------------------------------------------
--Exclude ENT DRI & sets serioal number for Photophersis
Left Outer Join RFT_RS_REPORTING.dbo.RFT_DR_ABS a
on i.HospitalProviderSpellNumber=a.AccountNumber
AND i.EpisodeNumber=a.ServiceSeqID
-----------------------------------------------------------------------------
-- Adjust Code of Commissioner to SCG for Oral Surgery & Orthodontics
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
CASE
WHEN gmp.[HA Code] IS NULL THEN 'Q51'
WHEN gmp.[HA Code] IN ('Q32','Q33') THEN 'Q51'
ELSE [HA Code]
END as [Comm]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and TreatmentFunctionCode IN ('140','143'))SCG
ON i.CdsUniqueIdentifier=SCG.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@start
and AgeAtCdsActivityDate >24
AND cc.AgeCat = 1) SCG1
ON i.CdsUniqueIdentifier=SCG1.CdsUniqueIdentifier
-----------------------------------------------------------------------------
-- Adjust Commissioner Cancer SCG_2 Activity (now defaulted to 'Q51' in main query)
LEFT OUTER JOIN (SELECT
CdsUniqueIdentifier,
PrimaryDiagnosisIcd,
CodeOfCommissioner,
GeneralMedicalPractice,
[HA Code]
FROM
RFT_CDS_Reporting.dbo.CDS_Inpatients inp
Inner Join RFT_LOOKUPS.dbo.SCG_Cancer_Codes cc
on inp.PrimaryDiagnosisIcd=cc.ICD10_CODE
Left outer Join RFT_LOOKUPS.dbo.LU_DH_General_Medical_Practice gmp
on inp.GeneralMedicalPractice=gmp.[Organisation Code]
where
CdsActivityDate >=@initial
And AgeAtCdsActivityDate between 19 and 24
AND cc.AgeCat = 2)SCG2
ON i.CdsUniqueIdentifier=SCG2.CdsUniqueIdentifier
-----------------------------------------------------------------------------------------------------
-- Create link to CCG look_Up to clean up any old PCT Codes
LEFT OUTER JOIN RFT_LOOKUPS.dbo.LU_PCT_CCG_MAP ccg
on i.CodeOfCommissioner=ccg.PCT
-----------------------------------------------------------------------------------------------------
where
(PatientGivenName IS NULL OR PatientGivenName NOT LIKE 'DONOTUSE')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'YYTESTPATIENTII')
AND (PatientFamilyName IS NULL OR PatientFamilyName NOT LIKE 'XXTESTPATIENT%')
AND cast(cdsactivitydate as date) between @start and @end
--And CdsType Not LIKE '120'
-- Excludes ENTDRI as DRI submit this
AND a.AbsServiceID != 'ENTDRI'
AND (a.LatestWardLocationInSpell!='DRI' OR a.LatestWardLocationInSpell IS NULL) --SL 20150312 Some Don ENT records have "ENT" service id
--AND HospitalProviderSpellNumber Like 'RA0000776946'
AND MainSpecialtyCode NOT IN ('800','810') --Exclude Radiology, needs to be uploaded seperately
select @@ROWCOUNT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2015 at 7:59 am
Hi Sean
It's quite a monster select statement that normally runs quite happily (or so we thought).
You're basically correct. When the select runs on it's own for some records defined in a modified version of that select (I added a list to the WHERE clause to check record counts) I get 53 records. When they are inserted into the table, I get 39 records inserted. There's no trigger (or anything else) on the table.
I'll check the rowcounter (courtesy of your code) and let you know how I get on.
If I have uncovered the rarest SQL bug in history, do I get a prize??? 😀
::edit::
I've just double checked the receiving table (CDS_Inpatients_CDS_Feeds_Import) for Keys, Constraints, Triggers, Indexes and Statistics and found a grand total of 14 stats and nothing else (not even a primary key). They all start "_WA_Sys" so I'm assuming they are system generated statistics that won't have any bearing on this??
May 28, 2015 at 8:09 am
richardmgreen1 (5/28/2015)
Hi SeanIt's quite a monster select statement that normally runs quite happily (or so we thought).
You're basically correct. When the select runs on it's own for some records defined in a modified version of that select (I added a list to the WHERE clause to check record counts) I get 53 records. When they are inserted into the table, I get 39 records inserted. There's no trigger (or anything else) on the table.
I'll check the rowcounter (courtesy of your code) and let you know how I get on.
If I have uncovered the rarest SQL bug in history, do I get a prize??? 😀
It's nothing so exotic. You're comparing two different queries. Comment out the INSERT part and run - let's call it "Query 2" - again. This is what Sean is getting at.
The performance of this query is unlikely to sparkle. It could run quite a bit faster with just a small injection of ssc help. Would you be interested, on meagre nhs rates?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 28, 2015 at 8:11 am
Just out of cursiousity have you looked at some of the rows that aren't being inserted that you feel should be to some of the rows that are and compared the differences?
May 28, 2015 at 8:15 am
richardmgreen1 (5/28/2015)
Hi SeanIt's quite a monster select statement that normally runs quite happily (or so we thought).
You're basically correct. When the select runs on it's own for some records defined in a modified version of that select (I added a list to the WHERE clause to check record counts) I get 53 records. When they are inserted into the table, I get 39 records inserted. There's no trigger (or anything else) on the table.
I'll check the rowcounter (courtesy of your code) and let you know how I get on.
If I have uncovered the rarest SQL bug in history, do I get a prize??? 😀
::edit::
I've just double checked the receiving table (CDS_Inpatients_CDS_Feeds_Import) for Keys, Constraints, Triggers, Indexes and Statistics and found a grand total of 14 stats and nothing else (not even a primary key). They all start "_WA_Sys" so I'm assuming they are system generated statistics that won't have any bearing on this??
One of your LEFT JOINs is converted into an inner join by a predicate in your WHERE clause. Using table aliases for all columns, not just where they appear in joins, makes logic errors such as this much easier to spot.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 28, 2015 at 8:21 am
Confirm is no primary key or unique index with "ignore_dup_key = on" or "instead of" triggers.
Also, what happens if you simply SELECT .. INTO <table> ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 28, 2015 at 8:25 am
@ZZartin - Done that, there doesn't seem to be a reason I can see.
@ChrisM@Work - This is something I inherited so I'm still trying to untangle a lot of it (I'll be adding table aliases to every item when I get the chance. I've never come across a LEFT JOIN changing to an INNER JOIN, can you please explain?
@sean - This is just plain weird. My 53 records are now 53 records in all cases. Now I'm seriously confused :ermm:
May 28, 2015 at 8:32 am
Also, what happens if you simply SELECT .. INTO <table> ?
That could be interesting, try inserting into a temp table, verify the row count, then try inserting from the temp table into your table and see what happens 🙂
May 28, 2015 at 8:41 am
Hi Chris
I'll take all the help I can get (I'm always willing to learn).
I still don't understand why (without any changes) I could select 53 records last week and only insert 39 of them.
May 28, 2015 at 8:43 am
richardmgreen1 (5/28/2015)
Hi ChrisI'll take all the help I can get (I'm always willing to learn).
I still don't understand why (without any changes) I could select 53 records last week and only insert 39 of them.
Two different SELECTs. If you still have the exact same queries, then try running both of them with the INSERT commented out.
The LEFT JOINs to derived tables in your query could use a little help but I suggest you get to the bottom of this issue first?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 28, 2015 at 8:55 am
The SELECT query I posted earlier is the one I was using last week (with the exception of an extra bit in the WHERE clause which looks like this:-
and HospitalProviderSpellNumber in (
'RA0001626403','RA0001666473','RA0001670851','RA0001528852','RA0001598402','RA0001473901'
,'RA0001416964','RA0001559354','RA0001555761','RA0001634268','RA0001636593','RA0001620232'
,'RA0001651403','RA0001664113','RA0001641104','RA0001641303','RA0001625654','RA0001636540'
,'RA0001621485','RA0001621759','RA0001629065','RA0001627327','RA0001663825','RA0001663960'
,'RA0001664046','RA0001640885','RA0001443921','RA0001419727','RA0001666541','RA0001504730'
,'RA0001539988','RA0001625625','RA0001628751','RA0001638638','RA0001463905','RA0001555372'
,'RA0001645517','RA0001657614','RA0001663799','RA0001654354','RA0001661858','RA0001664915'
,'RA0001664065','RA0001659163','RA0001629498','RA0001660911','RA0001670970','RA0001540497'
,'RA0001576361','RA0001601599'
)
).
There hasn't been any changes to this since I inherited it (although I've now got a small list of changes to make to help debugging in future).
As you can see from Sean's edit, all that's been added (in both cases), is the list of references in the WHERE clause.
I've run the queries with and without the extra bits and everything is now working as it should (or seems to be).
May 28, 2015 at 9:00 am
Confirm that both tests, the straight select and then the insert / select, are performed in the same SSMS query window, using the same session and default session settings. Settings like ANSI_NULLS, CONCAT_NULL_YIELDS_NULL, and ANSI_PADDING change the behaviour of conditional operators and can influence the number of rows returned. This often explains scenarios where a query returns X number of rows when run in SSMS but Y number of rows when run through an application.
If these queries are being run against a live production database with concurrent users, then just for the purpose of ruling out things like phantom reads and skipping past rows, try executing both queries using SERIALIZABLE or SNAPSHOT isolation level.
*** However, only use default READ_COMMITTED isolation level when deploying SQL code permanently to production.
Also, to back this problem into a corner, run both queries in SSMS with the 'Include Actual Execution Plan' option enabled. Examine both plans side by side, the operators used and the number of records flowing through each pipe. At what point do they differ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply