I'm trying to figure out how to return a listing of all patients that have a birthday before 8/15/2015 but also need to include Doctor Name if they have a PCP
APPOINTMENTS PATIENTS DOCTORS LOCATIONS PATIENTPCP
AppointmentID PatientID DoctorID LocationID PatientID
PatientID FirstName FirstName LocationName DoctorID
DoctorID LastName LastName Address UpdateDate
LocationID SSN NPI City CreateDate
DateTime Birthdate TIN State
AppointmentStatusID Gender Suffix Zip
Also need to update LocationID for all appointments between January 1, 2010 and January 1,2011 for patientID456 from location 7 to 9 and exclude appointments that are set to cancelled....appointment status 900
Just realized the swarm of fields, is table description aligned vertical.
Try this:
select p.*
,d.*
from patients as p
left outer join patientpcp as pcp
on pcp.patientid
left outer join doctors as d
on d.doctorid=pcp.doctorid
where p.birthdate<'20150815'
And for the update part:
update a
set locationid=9
from appointments as a
where [datetime]>='20100101'
and [datetime]<'20110101'
and locationid=7
and appointmentstatusid<>900
@bitsmed...thank you for this..this is a huge help..let me finish up on my tutorial so I fully understand this and can confirm it..but it looks like it works!!
Heh... Hopefully this IS just for a tutorial and not a real database. I'm thinking that neither the SSN or TIN columns are properly encrypted. If this is for a real database for a company, I hope we don't end up reading about his company getting hacked.
As a bit of a sidebar and shifting gears from the above, well done on taking the initiative to follow this up in the tutorial so that you understand it.
Don't forget to tell them how important it is to encrypt the SSN etc. If they didn;t realise they needed to do that they may hire you on the spot ... or ... you'll decide you couldn't possible work for an organisation so slack ... well ... "Not for THAT money"
