Query help for someone who has been using Linq-to-Sql too much lately

Question

Patient 
-------
PatientID

Visit
-----
VisitID
PatientID
HeartRate
VisitDate

How do I select all of the patients who have a visit, the date of their first visit, and their heart rate at that first visit?

Answer 1

SELECT TOP(1) PatientID, VisitDate, HeartRate FROM Visit ORDER BY VisitDate ASC

Patient has only PatientID? Not a name or something like that?

Answer 2

Is this what you want? Of course the join isn't really necessary here since there are no extra fields in Patient. But I guess IRL there are.

select PatientId, HeartRate, VisitDate from Patient p
left join Visit v on p.PatientID = v.PatientId

Answer 3

SELECT
  p.PatientID,
  v.VisitID,
  v.HeartRate,
  v.VisitDate
FROM
  Patient p
  INNER JOIN Visit v ON p.PatientID = v.PatientID
WHERE
  v.VisitDate = (
    SELECT MIN(VisitDate) 
    FROM Visit 
    WHERE PatientId = p.PatientId
  )

---

EDIT: Alternative version. Same thing. (less obvious, therefore less desirable IMHO)

SELECT
  p.PatientID,
  v.VisitID,
  v.HeartRate,
  v.VisitDate
FROM
  Patient p
  INNER JOIN Visit v ON p.PatientID = v.PatientID
  INNER JOIN (
    SELECT PatientID, MIN(VisitDate) VisitDate 
    FROM Visit 
    GROUP BY PatientID
  ) f ON f.PatientID = p.PatientID AND f.VisitDate = v.VisitDate

Answer 4

The final query

declare @practiceID int 
select 
 isnull(sum(isResponder),0) as [Responders],
 isnull(count(*) - sum(isResponder),0) as [NonResponders]
from ( 
 select 
  v.patientID
  ,firstVisit.hbLevel as startHb
  ,maxHbVisit.hblevel as maxHb
  , case when (maxHbVisit.hblevel - firstVisit.hbLevel >= 1) then 1 else 0 end as isResponder
 from patient p 
  inner join visit v on v.patientid = v.patientid 
  inner join practice on practice.practiceid = p.practiceid
  inner join (
   SELECT
     p.PatientID
     ,v.VisitID
     ,v.hblevel 
     ,v.VisitDate 
   FROM Patient p
     INNER JOIN Visit v ON p.PatientID = v.PatientID
   WHERE
    v.VisitDate = (
     SELECT MIN(VisitDate) 
     FROM Visit 
     WHERE PatientId = p.PatientId
     )
  ) firstVisit on firstVisit.patientID = v.patientID
  inner join (
   select 
    p.patientID
    ,max(v.hbLevel) as hblevel
   from Patient p
     INNER JOIN Visit v ON p.PatientID = v.PatientID
   group by
    p.patientID
  ) MaxHbVisit on maxHbVisit.patientid = v.patientId
 where
  p.practiceID = isnull(@practiceID, p.practiceID)
 group by
  v.patientID
  ,firstVisit.hbLevel
  ,maxHbVisit.hblevel
 having
  datediff(
   d,
   dateadd(
    day
    ,-DatePart(
     dw
     ,min(v.visitDate)
    ) + 1
    ,min(v.visitDate)
   )
   , max(v.visitDate)
  ) >= (7 * 8)
) responders