How do I get the latest dates?

Question

2.00/5 (1 vote)

See more:

This is how my data looks:

635584 8/10/2012   Family Medicine  Ba NP            
635584 4/18/2013   Family Medicine  Egan MD 
635584 3/17/2014   Women's Health   Prenatal Registration   
635584 6/2/2014    Family Medicine  Matathia        
635584 8/5/2014    Women's Health   Forman CNM

I want to only pull the latest appointment which was 8/5/14, I created the formula:
maximum({Command.lastappt}) and was able to do just that!

but I also got the providers too...

635584 8/5/2014   Family Medicine  Ba NP            
635584 8/5/2014   Family Medicine  Egan MD 
635584 8/5/2014   Women's Health   Prenatal Registration   
635584 8/5/2014   Family Medicine  Matathia        
635584 8/5/2014   Women's Health   Forman CNM

So I went ahead and did this...

maximum({Command.lastappt}) & " " & {Command.provider}

and it gave me the same results.

It works the same way even if I group by Provider. What's the best approach?

Thanks in advance!

Posted 25-Aug-14 5:32am

Member 11035000

Updated 27-Aug-14 3:57am

Nelek

v2

Add a Solution

Comments

Kschuler 25-Aug-14 17:03pm

Where are you putting this formula? Is this data all in table? Or is it pulling from several? If several, have you setup your database links in the Database Expert?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11035000 · Answer 1 · 2014-08-27T03:42:00

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}