Hi,
I have a table generated as;
INSERT INTO dbo.CPRDLkupMedical (Snomed,SnomedDescription,EmisCode,ReadCode,Term)
SELECT DISTINCT Snomed,SnomedDescription,
case WHEN left(Emiscode,7) = 'EMISATT'
THEN 'EMISATT'
WHEN left(Emiscode,5)= 'PCSDT'
THEN 'PCSDT'
ELSE Emiscode
END AS Emiscode,
CASE WHEN left(Readcode,5)= 'PCSDT'
THEN 'PCSDT'
WHEN left(Readcode,7)= 'EMISATT'
THEN 'EMISATT'
ELSE Readcode
END AS Readcode, Term
FROM dbo.CPRDALLMedicalCodes with (tablock)
Then, I have another Select statement as;
SELECT EMISJun15Btch1.dbo.Referral.ReferralGuidDigest, dbo.EmisPatient.PatientID, dbo.EmisConsultation.ConsultationID, dbo.EmisStaff.StaffID,
EMISJun15Btch1.dbo.Referral.EffectiveDateTime AS ReferralDate, EMISJun15Btch1.dbo.Referral.Status AS ReferralStatus,
EMISJun15Btch1.dbo.Referral.Mode AS ReferralMode, EMISJun15Btch1.dbo.Referral.ServiceType, EMISJun15Btch1.dbo.Referral.Urgency,
EMISJun15Btch1.dbo.Referral.Direction, EMISJun15Btch1.dbo.Referral.Transport, EMISJun15Btch1.dbo.Referral.EndedDate,
EMISJun15Btch1.dbo.Referral.ReceivedDate, dbo.CPRDLkupMedical.MedicalCodeID, EMISJun15Btch1.dbo.Referral.Term,
EMISJun15Btch1.dbo.Referral.SnomedCTConceptId, EMISJun15Btch1.dbo.Referral.SnomedCTDescriptionId, EMISJun15Btch1.dbo.Referral.EmisCode,
EMISJun15Btch1.dbo.Referral.ReadCode
FROM EMISJun15Btch1.dbo.Referral WITH (tablock) INNER JOIN
dbo.EmisPatient ON EMISJun15Btch1.dbo.Referral.PatientGuidDigest = dbo.EmisPatient.PatientGuidDigest LEFT OUTER JOIN
dbo.EmisConsultation ON EMISJun15Btch1.dbo.Referral.ConsultationGuidDigest = dbo.EmisConsultation.ConsultationGuidDigest INNER JOIN
dbo.EmisStaff ON EMISJun15Btch1.dbo.Referral.AuthorisingUserRoleGuidDigest = dbo.EmisStaff.StaffGuidDigest LEFT OUTER JOIN
dbo.CPRDLkupMedical ON (EMISJun15Btch1.dbo.Referral.SnomedCTConceptId = dbo.CPRDLkupMedical.Snomed OR
EMISJun15Btch1.dbo.Referral.SnomedCTConceptId IS NULL AND dbo.CPRDLkupMedical.Snomed IS NULL) AND
(EMISJun15Btch1.dbo.Referral.SnomedCTDescriptionId = dbo.CPRDLkupMedical.SnomedDescription OR
EMISJun15Btch1.dbo.Referral.SnomedCTDescriptionId IS NULL AND dbo.CPRDLkupMedical.SnomedDescription IS NULL) AND
ISNULL(EMISJun15Btch1.dbo.Referral.Term, N'(novalue)') = ISNULL(dbo.CPRDLkupMedical.Term, N'(novalue)') AND
ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Referral.ReadCode, 5) = 'PCSDT' THEN 'PCSDT' WHEN LEFT(EMISJun15Btch1.dbo.Referral.Readcode, 7)
= 'EMISATT' THEN 'EMISATT' ELSE EMISJun15Btch1.dbo.Referral.ReadCode END, N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.Readcode,
N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS AND ISNULL(CASE WHEN LEFT(EMISJun15Btch1.dbo.Referral.Emiscode, 5)
= 'PCSDT' THEN 'PCSDT' WHEN LEFT(EMISJun15Btch1.dbo.Referral.Emiscode, 7) = 'EMISATT' THEN 'EMISATT' ELSE EMISJun15Btch1.dbo.Referral.EmisCode END,
N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS = ISNULL(dbo.CPRDLkupMedical.Emiscode, N'(novalue)') COLLATE SQL_Latin1_General_CP1_CS_AS
Now, EmisJun15batch1.dbo.Referral has 11,330,536 records
Running the 2nd Select statement gives me 11,350,426 records.
My question is - why am I receiving extra fields in the select statement and not as the same number of records as dbo.Referral in EmisJun15Batch1 DB.
Thanks