Thanks. I forgot that it was a RIGHT outer join.

Hey, no need to worry. I have managed to get the output I want using a delete and inner join statement combined. Many thanks for your help. You have helped...

Hi this is nearly what I want. After adding the insert statement the final output in #zzPBC_Budget_v_Actuals contains 7 records. two of those records are the original reords in the...

Here is the script that I am using:

CREATE TABLE #zzPBC_Budget_v_Actuals

(

providerCode varchar(20)

,practiceCode varchar(20)

,POD varchar(20)

...

I dont want table1 to match the contents of table3 exactly. I just want three records in table 1 where the 4 fields match the fields in table2. In my...

Thanks. That makes sense now.

I understand what you are saying. However the 98000 records produced from the join is the correct number of records to be updated. The finance team have a...

I have tried that script and I get 23000 records. The tables are such that there is not a 1 to 1 relationship where the join matches. If I...

Thank you very much. You have been a great help

I need the max appointment date

just one more thing. How can I alter your script so that I can bring in all the other fields that were in the original extract?

Many thanks.

I have attached a screenshot of the output.

I forgot to add.

CL.ClientID is the patient ID and

GS.CodeDescription AS Specialty is the care specialty field

the current query is

SELECT AR.SequenceID,CL.ClientID,GPP.PCG AS PrimaryTrust,AAP.AppointmentDate,APT.CodeDescription AS AppointmentType,ARS.CodeDescription AS ReferralSource,

GS.CodeDescription AS Specialty,convert(varchar(10),CL.DateOfBirth,110) as [Date Of Birth],CL.NNN AS NHS_NUMBER,GE.CodeDescription as Ethnicity, CGP.GPCode,CA.PostCode,WR.WARD_ID,WR.WARD_NAME

FROM AMSAppointment AAP

INNER JOIN AMSAppointmentContact APC ON...