This is actually an SSRS query, rather than Crystal Reports, but if you replace :Parameter with {?Parameter}, it will work the same way. In Crystal, I would more-typically break these out using formulas, but because of the way SSRS works, I thought it would be better to get the query to do the lifting. If you have any questions about what I did and why, feel free to ask.
select * from (
select Person.pId, Person.lastName as patLast, Person.firstName as patFirst, Person.dateOfBirth
, Person.sex
, UsrInfo.loginName, UsrInfo.lastName as provLast, UsrInfo.firstName as provFirst
, LocReg.name as facility
, LastObs.ObsName, LastObs.obsDate, LastObs.obsValue
, count(distinct Document.sdId) over (partition by Document.pId) as ovCount
, max(case when LastObs.obsName = 'BP SYSTOLIC' then LastObs.obsDate else null end)
over (partition by LastObs.pId) as obsBPSysDate
, max(case when LastObs.obsName = 'BP SYS STAND' then LastObs.obsDate else null end)
over (partition by LastObs.pId) as obsBPSysStandDate
, max(case when LastObs.obsName = 'BP SYS LYING' then LastObs.obsDate else null end)
over (partition by LastObs.pId) as obsBPSysLyingDate
, max(case when LastObs.obsName = 'BP SYS REP' then LastObs.obsDate else null end)
over (partition by LastObs.pId) as obsBPSysRepDate
, max(case when LastObs.obsName = 'BP SYSTOLIC' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPSysValue
, max(case when LastObs.obsName = 'BP SYS STAND' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPSysStandValue
, max(case when LastObs.obsName = 'BP SYS LYING' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPSysLyingValue
, max(case when LastObs.obsName = 'BP SYS REP' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPSysRepValue
, max(case when LastObs.obsName = 'BP DIASTOLIC' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPDiaValue
, max(case when LastObs.obsName = 'BP DIA STAND' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPDiaStandValue
, max(case when LastObs.obsName = 'BP DIA LYING' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPDiaLyingValue
, max(case when LastObs.obsName = 'BP DIAS REP' then LastObs.obsValue else null end)
over (partition by LastObs.pId) as obsBPDiaRepValue
from ml.Person
join ml.UsrInfo on Person.respProvId = UsrInfo.pvId
join ml.LocReg on UsrInfo.homeLocation = LocReg.locId
join ml.Document on Person.pId = Document.pId
join ml.Problem on Person.pId = Problem.pId
left join (
select Obs.pId, Obs.obsValue, Obs.obsDate
, ObsHead.name as ObsName
, max(Obs.obsDate) over (partition by Obs.pId, Obs.hdId) as maxObsDate
from ml.Obs Obs
join ml.ObsHead ObsHead on ObsHead.hdId = Obs.hdId
where ObsHead.name in ('BP SYSTOLIC', 'BP DIASTOLIC'
, 'BP SYS STAND', 'BP DIA STAND'
, 'BP SYS LYING', 'BP DIA LYING'
, 'BP SYS REP', 'BP DIAS REP')
and Obs.obsDate >= :DateBegin
and Obs.obsDate < cast(:DateEnd as date) + 1
and Obs.xId = 1.e+035
and Obs.change = 2
) LastObs on Person.pId = LastObs.pId
and LastObs.obsDate = LastObs.maxObsDate
where Person.isPatient = 'Y'
and Person.pStatus = 'A'
and Problem.qualifier = 'Dx of'
and Problem.code like 'ICD-250%'
and (Problem.onsetDate is null
or Problem.onsetDate <= :DateEnd)
and (Problem.stopDate is null
or Problem.stopDate > :DateEnd)
and Problem.stopReason is null
and Document.docType = 1
and Document.clinicalDate >= ml.convert_date_to_id(:DateBegin)
and Document.clinicalDate < ml.convert_date_to_id(cast(:DateEnd as date) + 1)
and Person.dateOfBirth > cast(:DateDec31 as date) - interval '86' year
and Person.dateOfBirth <= cast(:DateDec31 as date) - interval '18' year
and UsrInfo.loginName <> 'ddurant'
order by LocReg.name
, UsrInfo.loginName
, Person.lastName
, Person.firstName
, Person.pId
) patList
where ovCount >= 2
Posted : July 12, 2012 11:51 pm
