If possible to record a patient only one time

Question

I believe i need to use the CAST but this is failing on the first 'WHERE'

 SELECT a.Patient_Number, a.Appt_DateTime, a.Appt_Resource_Descr, a.Appt_Type_Descr, a.Appt_Status, a.Appt_Sched_Department_Descr, p.Patient_Name
FROM vwGenPatApptInfo a
JOIN vwGenPatInfo p
WHERE CAST(a.Patient_Number as VARCHAR(30)) + CAST(a.Appt_DateTime as VARCHAR(30)) in (
select CAST(a.Patient_Number as VARCHAR(30)) + CAST(MIN(a.Appt_DateTime) as VARCHAR(30))
from vwGenPatApptInfo a
WHERE a.Appt_Status='A' 
AND a.Appt_DateTime>={ts '2017-01-01 02:25:00'} 
AND a.Appt_DateTime<{ts '2017-12-31 23:25:01'} 
AND a.Appt_Sched_Department_Descr='Mental Health'
group by a.Patient_Number)
ORDER BY a.Patient_Number, a.Appt_DateTime

Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Tab Alleman, Aug 31 '17 at 20:11

score 1 · Accepted Answer · answered Sep 01 '17 at 01:46

Something like this should work get you started

select a.patient_number
, other fields
, min(appt_dateTime) earliestAppointment
, datename(month(min(appt_dateTime)) monthName
, datepart(month(min(appt_dateTime)) monthNumber
from yourtables

WHERE  a.Appt_Status='A' 
AND a.Appt_DateTime>={ts '2017-01-01 02:25:00'} 
AND a.Appt_DateTime<{ts '2017-12-31 23:25:01'} 
AND a.Appt_Sched_Department_Descr='Mental Health'

group by a.patient_number
order by monthNumber

Your job is to figure out how to get the details of that appointment.

If possible to record a patient only one time

1 Answers1