I'm adding a formula in Crystal and adding a command in the Database Expert, below is the sql I'm using, innerjoin on PatientProfileId:
CPSDORDB
select a.PatientProfileId,
a.MedicalRecordNumber,
a.searchname,
a.edcdate, a.wk, b.lastappt from
(SELECT PatientProfile.PID,PatientProfile.PatientProfileID, PatientProfile.MedicalRecordNumber,
PatientProfile.searchname,
EDCDATE=case when isdate(max(RPTOBS.obsvalue))=1 then max(RPTOBS.obsvalue) end,
WK=case when isdate(max(RPTOBS.obsvalue))=1 then 40-DATEDIFF(d,getdate(),max(RPTOBS.obsvalue))/7 end
FROM OBSHEAD OBSHEAD
INNER JOIN ((PatientProfile PatientProfile
INNER JOIN DOCUMENT DOCUMENT ON PatientProfile.PId=DOCUMENT.PID)
INNER JOIN RPTOBS RPTOBS ON (DOCUMENT.PID=RPTOBS.pid)
AND (DOCUMENT.SDID=RPTOBS.sdid)) ON OBSHEAD.HDID=RPTOBS.hdid
WHERE
RPTOBs.hdid='8086'
group by PatientProfile.PID,PatientProfile.PatientProfileID, PatientProfile.MedicalRecordNumber,
PatientProfile.searchname)a
inner join
(Select a.ownerid, p.patientprofileid, p.searchname, max(a.ApptStart) as LastAppt
from Appointments a
left join patientprofile p on p.PatientProfileId= a.OwnerId
inner join DoctorFacility f on f.DoctorFacilityId = a.FacilityId
inner join DoctorFacility d on d.DoctorFacilityId = a.ResourceId
where a.FacilityId in ('127','64') and
a.Status in ('Completed','Arrived')
group by a.OwnerId,p.patientprofileid, p.searchname) b
on a.patientprofileid = b.patientprofileid
where EDCDATE>=cast(GETDATE() as DATE)-- and a.PatientProfileId = '635584'
group by a.PatientProfileId,
a.MedicalRecordNumber,
a.searchname,a.edcdate,
a.wk, b.lastappt
EXTERNAL JOIN Command.PatientProfileId={?CPSDORDB: Command_1.patientprofileid}
CPSDORDB
Select a.ownerid, p.patientprofileid, p.medicalrecordnumber, p.searchname, min(a.ApptStart) as NextAppt, d.listname
from Appointments a
left join patientprofile p on p.PatientProfileId= a.OwnerId
inner join DoctorFacility f on f.DoctorFacilityId = a.FacilityId
inner join DoctorFacility d on d.DoctorFacilityId = a.ResourceId
where a.FacilityId in ('127','64') and
a.Status = 'Scheduled' and
a.ApptTypeId in ('15','10','181','25','24','263','235','89','90')
--and a.ownerid = '635584'
group by a.OwnerId,p.patientprofileid, p.MedicalRecordNumber, p.searchname, d.listname
order by p.MedicalRecordNumber
EXTERNAL JOIN Command_1.patientprofileid={?CPSDORDB: Command.PatientProfileId}