Very urgent SQL problem! 1

[JCAD1]

I am really struggling to write a T-SQL program to update a table in SQL server 2000, using two other tables. I have been trying for one week now but to no avail.

The code for creating the table being updated is as follows:

create table acIPLeicsUpdate (NewNHSNumber varchar(10), PatientsRegisteredGPPractice varchar(6), XXOldPracReg varchar(6),
PatientsRegisteredGP varchar(8), AdmissionDate varchar(10), ConsEpisodeStartDate varchar(10), XXFailReason varchar(120))
insert acIPLeicsUpdate values ('7973567873', 'P53207',NULL, 'R8310343','2002-10-11','2003-01-09',NULL)
insert acIPLeicsUpdate values ('7973567873','P53207',NULL,'R8310343','2002-11-15','2002-11-15',NULL)
insert acIPLeicsUpdate values ('7725770226','P53214',NULL,'R3302343','2003-01-30','2003-01-30',NULL)
insert acIPLeicsUpdate values ('7725770226','P53214',NULL,'R3302343','2003-01-30','2003-02-11',NULL)
insert acIPLeicsUpdate values ('7973567873','P53207',NULL,'R9100743','2003-02-12','2003-02-12',NULL)
insert acIPLeicsUpdate values ('7970958973','P72444',NULL,'R9112143','2003-02-12','2003-02-12',NULL)
insert acIPLeicsUpdate values ('7973567873','P53207',NULL,'R9303843','2003-01-16','2003-01-17',NULL)
insert acIPLeicsUpdate values ('7973567873','P53207',NULL,'R8106043','2003-01-17','2003-01-17',NULL)
insert acIPLeicsUpdate values ('7973567873','P53207',NULL,'R8300443','2003-01-22','2003-01-22',NULL)
insert acIPLeicsUpdate values ('7202267877','P53226',NULL,'R3371943','2003-02-14','2003-02-15',NULL)

The codes for creating the tables used for updating the above table are as follows:

create table hnatemp..gp_details (prac_nat varchar(6), gp_nat varchar(8), s_date varchar(10))
insert gp_details values ('P53207','R8310343','2002-10-08')
insert gp_details values ('P53207','R8310343','2002-12-14')
insert gp_details values ('P53214','R3302343','2002-09-30')
insert gp_details values ('P53214','R8311743','2003-01-17')
insert gp_details values ('P53207','R9100743','2003-01-10')
insert gp_details values ('P53202','R9112143','2003-02-12')
insert gp_details values ('P53207','R9303843','2002-07-16')
insert gp_details values ('P84017','R3359443','2003-01-03')
insert gp_details values ('P53218','R8300443','2002-10-22')
insert gp_details values ('P84057','R3371943','2002-05-06')
insert gp_details values ('P53207','R8211243','2001-11-23')
insert gp_details values ('P53226','R3302343','2003-02-28')
insert gp_details values ('P53222','R3294043','2001-12-13')
insert gp_details values ('P84055','R8303243',NULL)
insert gp_details values ('P53226','R8310343','2000-04-19')

create table mi_p (nhs_num varchar(10), prac_nat varchar(6), gp_nat varchar(8), reg_date varchar(10))
insert mi_p values ('7973567873','P53207','R8310343','2002-10-09')
insert mi_p values ('7973567873','P53207','R8310343','2002-12-14')
insert mi_p values ('7725770226','P53214','R3302343','2002-09-30')
insert mi_p values ('7725770226','P53214','R3302343','2003-01-17')
insert mi_p values ('7973567873','P53207','R9100743','2003-01-10')
insert mi_p values ('7970958973','P72444','R9112143','2003-02-12')
insert mi_p values ('7202267878','P53223','R3371946','2002-07-16')
insert mi_p values ('7973567873','P53207','R8106043','2000-01-17')
insert mi_p values ('7202267877','P53226','R3371943','2003-02-15')
insert mi_p values ('7202547877','P53636','R3373043','2003-03-25')
insert mi_p values ('8602267877','M53226','T4371943','2003-03-05')
insert mi_p values ('7202117877','P53226','R3581943','2003-02-15')
insert mi_p values ('7725770226','P53214','R3302343','2003-02-27')
insert mi_p values (NULL,NULL,NULL,NULL)
insert mi_p values ('7202267877','P53226','R3371943','2000-04-19')

This is what I want the program to do:

Step 1. Check if a patient’s registered GP (acIPLeicsUpdate.PatientsRegisteredGP) begins with ‘A’ and consultant episode start date (acIPLeicsUpdate.ConsEpisodeStartDate) >= ‘1997-04-01’ and GP practice (acIPLeicsUpdate.PatientsRegisteredGPPractice) <> ‘P53COT’. If so, copy current GP practice code (in acIPLeicsUpdate.PatientsRegisteredGPPractice field) to old practice field (acIPLeicsUpdate.XXOldPracReg) and put ‘P53COT’ into GP practice field (acIPLeicsUpdate.PatientsRegisteredGPPractice),
Otherwise do the following:

Step 2. Search for GP practice code in gp_details table where gp_details.prac_nat = acIPLeicsUpdate.PatientsRegisteredGPPractice and length of patient’s registered GP in gp_details table, i.e len(gp_details.gp_nat) = 6.

If GP practice code is not available in gp_details table, then do the following:
(a) Check if len(PatientsRegisteredGP) = 8. If so, do the following:
(b) Search for patient’s registered GP code in gp_details table where gp_details.gp_nat = acIPLeicsUpdate.PatientsRegisteredGP and (acIPLeicsUpdate.AdmissionDate >= gp_details.s_date or gp_details.s_date = null). If patient’s registered GP code is found/available, do the following:
Check if acIPLeicsUpdate.PatientsRegisteredGPPractice <> gp_details.prac_nat and acIPLeicsUpdate.PatientsRegisteredGPPractice <> 'V8199' and gp_details.prac_nat <> 'P53038', then copy GP practice code (acIPLeicsUpdate.PatientsRegisteredGPPractice) to old practice field (acIPLeicsUpdate.XXOldPracReg), copy GP practice code from gp_details.prac_nat to acIPLeicsUpdate.PatientsRegisteredGPPractice and put a text message ‘wrong-prac-code’ into acIPLeicsUpdate.XXFailReason field,
Otherwise do the following:
(c) Search mi_p table to get GP details, where mi_p.nhsnum = acIPLeicsUpdate.NewNHSNumber and mi_p.reg_date <= acIPLeicsUpdate.ConsEpisodeStartDate. If found, assign mi_p.gp_nat to acIPLeicsUpdate.PatientsRegisteredGP.

(d) Check if acIPLeicsUpdate.PatientsRegisteredGP <> null (i.e, if record found in mi_p table which has GP), then do the following:
(i) Search gp_details table (to find practice for GP) where gp_details.gp_nat = acIPLeicsUpdate.PatientsRegisteredGP. If found/available, assign gp_details.prac_nat to acIPLeicsUpdate. PatientsRegisteredGPPractice and copy old GP practice field in record (acIPLeicsUpdate.PatientsRegisteredGPPractice) to old GP practice field (acIPLeicsUpdate.XXOldPracReg) and add a text message ‘pmi-gp-prac’ to acIPLeicsUpdate. XXFailReason field.
(ii) If GP details are not found in mi_p table, put a text message ‘gp-unknown’ to indicate that GP is not known,
Otherwise [if len(PatientsRegisteredGP) <> 8]
(e) Repeat step 2(c) and 2(d) but add a text message to acIPLeicsUpdate. XXFailReason field as follows [see d(ii)]:
Check if len(PatientsRegisteredGP) = 0, then add the message ‘gp-blank’ else ‘gp-known’ to indicate respectively that the field is blank or GP is not known.

I guess the program code would consist of a series of update statements with case statements or something similar. If anyone could help me to write the program code, I would be extremely grateful.

Thank you in advance for your help.

R. Aba

 

[Jamfool]

STEP 1:

update acIPLeicsUpdate
set xxoldpracreg = patientsregisteredgppractice, patientsregisteredgppractice='P53COT'
where left(patientsregisteredgp,1)='A'
and consepisodestartdate >= '1997-04-01 00:00:00.000'
and patientsregisteredgppractice <>'P53COT'


Sorry the end of my day is here, so iam off home.Hopefully some1 will help u with the rest.

if no1 has maybe i will check when i get back home

 

[JCAD1]

No one has responded yet. I would be very grateful if you could code step 2 for me please. I am testing the one you have coded so far (Step 1) with real data and it is spot on.

Thank you for your time.

 

[Jamfool]

--part 2.a/b
UPDATE ac SET
ac.XXOldPracReg = ac.patientsregisteredgppractice,ac.XXFailReason = 'wrong-prac-code'
from acIPLeicsUpdate as ac inner join gp_details as gp on ac.PatientsRegisteredGP = gp.gp_nat
where
len(gp.gp_nat)<>6
and len(ac.patientsregisteredgp)=8
and ac.admissiondate >= gp.s_date or gp.s_date is null
and ac.patientsregisteredgppractice<>gp.prac_nat
and ac.patientsregisteredgppractice<>'V8199'
and gp.prac_nat<>'P53038'

--2c
UPDATE ac SET
ac.patientsregisteredgp = mi.gp_nat
from mi_p as mi inner join acIPLeicsUpdate as ac on ac.NewNHSNumber = mi.nhs_num
where mi.reg_date <= ac.ConsEpisodeStartDate


--d another easy one

--dii this is a simple update

--e

UPDATE ac SET XXFailReason = 'gp-blank'
from acIPLeicsUpdate as ac
where len(ac.patientsregisteredgp)=0


UPDATE ac SET XXFailReason = 'gp-unknown'
from acIPLeicsUpdate as ac
where len(ac.patientsregisteredgp)<>8



ok theres abit more, sorry if it aint exactly right, got other things to be doing today ;p i fya need more help then just ask, but i cant gurantee i will be able to look at it today

 

[JCAD1]

Thanks for your help. I have been busy with another project but am now turning to the SQL problem you're helping me to solve. I am trying to modify and test your code on real data. So far so good! I am getting the idea and would be most grateful if you could finish off the remaining bits, please. Thanks.

Aba

 

[Jamfool]

ok just had a look back thru this and you need to think about what you are trying to do, and clarify this. At the moment the only bits i left out was (d) and thats because it was similar to 2(a) etc, and (d) u could just write a update to set it unknown, if x is null, y is null , or z is invalid etc.