SQLTeam.com | Weblogs | Forums

Need some help with advanced queries - Almost New User


#1

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


#2

Please provide table descriptions (create statements), sample data and expected outout (from your provided sample data).


#3

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

#4

@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!!


#5

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.


#6

lol...its all good...rest assured there actually is no database...this is just a pop quiz for an up coming interview that im trying to ace!


#7

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" :smiley: