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.