Notifications
Clear all
Topic starter
I do not know Crystal Reports. I'm trying to figure things out watching YouTube and Googling through different leads but I also keep getting side-tracked with other, non-Crystal Reports work and I keep losing track of what I've tried and if I saw something all the way through or not.
Anyhow, I need to create a CR that can be run from CPS that I imagine should be pretty simple, I just don't know whether I should be pulling from a View, executing a Stored Procedure or something else entirely. There are only four questions I need in the CR and I've figured out a method to get the answers from SQL, but it involved using temp tables and aliases and such (I'm more comfortable with SQL, but still learning that as I go as well).
Here are the scripts I'm using to answer one of the four questions. This gets the raw data from the necessary tables in SQL, but I'm not sure how to make it work in CR. I know this may take some time (or, it may be that it's simpler than I expect - that'd be a nice change), but I'm hoping someone can assist me in figuring out how to accomplish what I need.
create table #temp1 (
PID numeric(19),
HDID numeric(19),
OBSDATE datetime,
OBSVALUE varchar (2000)
)
insert into #temp1
select PID, HDID, OBSDATE, OBSVALUE from OBS
where HDID in ('6772','54782','4561')
order by pid
---------------------------------------------------------------------------------
create table #temp1a (
PID numeric(19)
)
insert into #temp1a
select distinct PID
from #temp1
order by pid
----------------------------------------------------------------------------------
create table #temp2 (
PatientId varchar (20),
PID numeric (19),
First varchar (35),
last varchar (60),
birthdate datetime,
Sex varchar (1),
Age numeric (3)
)
INSERT INTO #temp2
SELECT PatientId, PID, First, last, birthdate, Sex, Age
FROM (Select PatientId, PID, First, last, birthdate, Sex, DATEDIFF(hour,birthdate,GETDATE())/8766 AS Age
FROM PatientProfile) p
WHERE Age between '15' AND '44'
and Sex = 'F'
----------------------------------------------------------------------------------
/**/select COUNT (PID) AS Numerator from #temp1a
/**/select COUNT (Sex) AS Denominator from #temp2
----------------------------------------------------------------------------------
--drop table #temp1
--drop table #temp1a
--drop table #temp2
----------------------------------------------------------------------------------
The question I'm answering with this one is:
1. Pregnancy Intentions in the next year? Yes, No, Not Known, Refused.
Guidelines: only ask women of childbearing age 15-44
Report: All women with any answer to the query
• Numerator = # of women screened for pregnancy intentions; that were asked the question
• Denominator = All women patients age 15-44
----------------------------------------------------------------------------------
If someone has the time and inclination, I have the ability to share my computer and can do a telephone call at the same time. I'm working out of our test CPS box to get this figured out and will then transfer what I learn to a production box for further testing/refining before passing onto the end-user. Though this is for work, I'm willing to personally pay a little for the assistance.
Posted : May 10, 2019 2:49 am
Can you write out in English what you are trying to do?
I sort of see it, but not quite positive.
Seems like you are storing answers to some questions, but what are the OBS Names (instead of numbers).
Posted : May 10, 2019 5:50 am
I believe I understand what you are after. I have provided a query below that can possibly help you with this. I use temp tables as you have, but most of the time I CTEs, which are another way of creating a temp date set. I sort of went about this by saying, okay in the last 12 months, what number of women aged 15 to 44 had an appointment and in the same 12 months answered the Pregnancy Intentions questions. So below you can see I created two CTEs; the first one (called PregnancyIntentionsPatients) finds every female patient aged 15 to 44 at the time of their appointment. The ROW_NUMBER function numbers the patients' appointments in descending order by Appt Start to find the last appointment in the last 12 months. The second CTE finds those patients that in the last 12 months answered the pregnancy intentions questions. I used the 3 HDIDs you provided. Here again, I used the ROW_NUMBER function to find the last time the patient answered the intentions question in the last 12 months.
Then the final section of the query performs a join of the two CTEs.
With PregnancyIntentionsPatients AS (Select pp.PId
,OwnerId
,ApptStart
,m.Description AS ApptStatus
,f.ListName AS Facility
,DATEDIFF(YY,pp.Birthdate,ApptStart)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,ApptStart),pp.Birthdate)> ApptStart THEN 1 ELSE 0 END AgeAtAppointment
,ROW_NUMBER() OVER(PARTITION BY pp.Pid ORDER BY ApptStart DESC) AS AppointmentRowNumber
From Appointments a
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN MedLists m ON a.ApptStatusMId = m.MedListsId AND m.TableName = 'AppointmentStatus'
JOIN DoctorFacility f ON a.FacilityId = f.DoctorFacilityId
WHERE CAST(ApptStart AS DATE) BETWEEN '2018-05-13' AND '2019-05-13'
AND ApptKind = 1
AND m.Description IN ('Arrived','Late','Completed')
AND f.ListName LIKE '%Medical%'
AND DATEDIFF(YY,pp.Birthdate,ApptStart)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,ApptStart),pp.Birthdate)> ApptStart THEN 1 ELSE 0 END BETWEEN 15 AND 44
AND pp.Sex = 'F'),
PregnancyIntentionQuestions AS (Select pp.PatientId
,o.pid
,pp.First
,pp.Last
,pp.Birthdate
,DATEDIFF(YY,pp.Birthdate,obsdate)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,obsdate),pp.Birthdate)> obsdate THEN 1 ELSE 0 END AS AgeAtPregnancyIntentionQuestion
,pp.Sex
,hdid
,obsdate
,obsvalue
,ROW_NUMBER() OVER(PARTITION BY o.PID ORDER BY obsdate DESC) AS PregIntentionRowNumber
From RPTOBS o
LEFT JOIN PatientProfile pp ON o.PID = pp.PId
WHERE hdid IN (6772,54782,4561)
AND pp.Sex = 'F'
AND DATEDIFF(YY,pp.Birthdate,obsdate)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,obsdate),pp.Birthdate)> obsdate THEN 1 ELSE 0 END BETWEEN 15 AND 44
AND CAST(obsdate AS DATE) BETWEEN '2018-05-13' AND '2019-05-13')
Select *
,CASE WHEN pq.obsdate IS NOT NULL THEN 1 ELSE 0 END AS PregnancyIntentionQuestionAsked_Numerator
,CASE WHEN p.PId IS NOT NULL THEN 1 ELSE 0 END AS PregnancyIntentionPatients_Denominator
From PregnancyIntentionsPatients p
LEFT JOIN PregnancyIntentionQuestions pq ON p.PId = pq.pid AND pq.PregIntentionRowNumber = 1
WHERE AppointmentRowNumber = 1
Additionally, I added two CASE statements that can be used to get a numerator and denominator. You can either use Crystal Reports to do a sum of these two columns and divide them to get a percentage or you can change the query to this to get the numerator and denominator:
With PregnancyIntentionsPatients AS (Select pp.PId
,OwnerId
,ApptStart
,m.Description AS ApptStatus
,f.ListName AS Facility
,DATEDIFF(YY,pp.Birthdate,ApptStart)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,ApptStart),pp.Birthdate)> ApptStart THEN 1 ELSE 0 END AgeAtAppointment
,ROW_NUMBER() OVER(PARTITION BY pp.Pid ORDER BY ApptStart DESC) AS AppointmentRowNumber
From Appointments a
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN MedLists m ON a.ApptStatusMId = m.MedListsId AND m.TableName = 'AppointmentStatus'
JOIN DoctorFacility f ON a.FacilityId = f.DoctorFacilityId
WHERE CAST(ApptStart AS DATE) BETWEEN '2018-05-13' AND '2019-05-13'
AND ApptKind = 1
AND m.Description IN ('Arrived','Late','Completed')
AND f.ListName LIKE '%Medical%'
AND DATEDIFF(YY,pp.Birthdate,ApptStart)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,ApptStart),pp.Birthdate)> ApptStart THEN 1 ELSE 0 END BETWEEN 15 AND 44
AND pp.Sex = 'F'),
PregnancyIntentionQuestions AS (Select pp.PatientId
,o.pid
,pp.First
,pp.Last
,pp.Birthdate
,DATEDIFF(YY,pp.Birthdate,obsdate)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,obsdate),pp.Birthdate)> obsdate THEN 1 ELSE 0 END AS AgeAtPregnancyIntentionQuestion
,pp.Sex
,hdid
,obsdate
,obsvalue
,ROW_NUMBER() OVER(PARTITION BY o.PID ORDER BY obsdate DESC) AS PregIntentionRowNumber
From RPTOBS o
LEFT JOIN PatientProfile pp ON o.PID = pp.PId
WHERE hdid IN (6772,54782,4561)
AND pp.Sex = 'F'
AND DATEDIFF(YY,pp.Birthdate,obsdate)-CASE WHEN DATEADD(YY,DATEDIFF(YY,pp.Birthdate,obsdate),pp.Birthdate)> obsdate THEN 1 ELSE 0 END BETWEEN 15 AND 44
AND CAST(obsdate AS DATE) BETWEEN '2018-05-13' AND '2019-05-13'),
Final AS (Select p.PId
,CASE WHEN pq.obsdate IS NOT NULL THEN 1 ELSE 0 END AS PregnancyIntentionQuestionAsked_Numerator
,CASE WHEN p.PId IS NOT NULL THEN 1 ELSE 0 END AS PregnancyIntentionPatients_Denominator
From PregnancyIntentionsPatients p
LEFT JOIN PregnancyIntentionQuestions pq ON p.PId = pq.pid AND pq.PregIntentionRowNumber = 1
WHERE AppointmentRowNumber = 1)
Select SUM(PregnancyIntentionQuestionAsked_Numerator) AS Numerator
,SUM(PregnancyIntentionPatients_Denominator) AS Denominator
From Final
Hopefully this helps.
Posted : May 13, 2019 10:31 am
