我如何获得最新日期？ [英] How do I get the latest dates?

问题描述

这是我的数据的样子：

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     

我想只拉出8/5/14的最新约会，我创建了公式：

maximum（{Command.lastappt} ）并且能够做到这一点！



但我也得到了供应商......

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!

推荐答案

我在Crystal中添加一个公式在数据库专家中添加一个命令，下面是我正在使用的sql，在PatientProfileId上的内连接：



CPSDORDB

选择一个。 PatientProfileId，

a.MedicalRecordNumber，

a.searchname，

a.edcdate，a.wk，b.lastappt来自

（SELECT PatientProfile.PID，PatientProfile.PatientProfileID，PatientProfile.MedicalRecordNumber，

PatientProfile.searchname，

EDCDATE = isdate时的情况（最大值（RPTOBS.obsvalue） ）= 1然后max（RPTOBS.obsvalue）结束，

WK = isdate（最大值（RPTOBS.obsvalue））= 1然后40-DATEDIFF（d，getdate（），max（RPTOBS。 obval））/ 7结束

来自OBSHEAD OBSHEAD

内部联接（（PatientProfile PatientProfile

在PatientProfile.PId = DOCUMENT上的内部联合文档文档。 PID）

INNER JOIN RPTOBS RPTOBS ON（DOCUMENT.PID = RPTOBS.pid）

AND（DOCUMENT.SDID = RPTOBS.sdid））OBSHEAD.HDID = RPTOBS.hdid

WHERE

RPTOBs.hdid ='8086'

group by PatientProfile.PID，PatientProfile.PatientProfileID，PatientProfile.MedicalRecordNumber，

PatientProfile。 searchname）a

内部联接

（选择a.ownerid，p.patientprofileid，p.searchname，max（a.ApptStart）作为LastAppt

来自约会a

左边加入patientprofile p on p.PatientProfileId = a.OwnerId

内部加入DoctorFacility f on f.DoctorFacilityId = a.FacilityId

内部加入DoctorFacility d在d.DoctorFacilityId = a.ResourceId

其中a.FacilityId在（'127'，'64'）和

a.Status in（ '完成'，'到达'）

group by a.OwnerId，p.patientprofileid，p.searchname）b

on a.patientprofileid = b.patientprofileid


其中E DCDATE> =施放（GETDATE（）作为DATE） - 和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

选择a.ownerid，p.patientprofileid，p.medicalrecordnumber，p.searchname， min（a.ApptStart）为NextAppt，d.listname

来自Appointments a

left join patientprofile p on p.PatientProfileId = a.OwnerId

内部加入DoctorFacility f on f.DoctorFacilityId = a.FacilityId

内部加入DoctorFacility d on d.DoctorFacilityId = a.ResourceId

其中a.FacilityId in（'127' ，'64'）和

a.Status ='预定'和

a.ApptTypeId in（'15'，'10'，'181'，'25' ，'24'，'263'，'235'，'89'，'90'）

- 和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}

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}

这篇关于我如何获得最新日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！