I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]
SELECT
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE AS Visit_Type,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,
srm.EPISODES.EPISODE_DATE AS DISCHDT,
MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,
--Begin nested query
(SELECT et1.episode_key, MAX(et1.status_date)
FROM srm.chart_components et1, srm.chart_components et2
WHERE et1.episode_key = et2.episode_key
AND et1.chart_component_ke = et2.chart_component_ke
AND et1.deficiency_type = et2.deficiency_type
AND et1.deficiency_status = et2.deficiency_status
AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'
GROUP by et1.episode_key
HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date
-- End nested query
FROM srm.cdmab_base_info INNER JOIN
srm.EPISODES INNER JOIN
srm.PATIENTS INNER JOIN
srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON
srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN
srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON
srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN
srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN
srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY
WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'
AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'
Group by
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,
srm.EPISODES.EPISODE_DATE,
srm.EVENT_HISTORY.EVENT_DATE,
srm.CHART_COMPONENTS.STATUS_DATE
See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:
Code Snippet
-- Begin nested query:
(select max(c1.status_date)
from srm.chart_components as c1
group by c1.episode_key
having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date
|||
Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.
"The multi-part identifier "c.deficiency_status" could not be bound."
SELECT
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE AS Visit_Type,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,
srm.EPISODES.EPISODE_DATE AS DISCHDT,
MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,
-- begin nested query
(select max(c1.status_date)
from srm.chart_components as c1
group by c1.episode_key
having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date
--end nested query
FROM srm.cdmab_base_info INNER JOIN
srm.EPISODES INNER JOIN
srm.PATIENTS INNER JOIN
srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON
srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN
srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON
srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN
srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN
srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY
WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'
AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'
Group by
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,
srm.EPISODES.EPISODE_DATE,
srm.EVENT_HISTORY.EVENT_DATE,
srm.CHART_COMPONENTS.STATUS_DATE
|||
I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.
|||I just noticed that I get the same error is I run your code snippet (see below) just by itself.
(select max(c1.status_date)
from srm.chart_components as c1
group by c1.episode_key
having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date
|||
I made the change above and and now receiving this error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
My code follows:
SELECT
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE AS Visit_Type,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,
srm.EPISODES.EPISODE_DATE AS DISCHDT,
MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,
--begin nested query
(select max(c1.status_date)
from srm.chart_components as c1
group by c1.episode_key
having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date
-- end nested query
FROM srm.cdmab_base_info INNER JOIN
srm.EPISODES INNER JOIN
srm.PATIENTS INNER JOIN
srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON
srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN
srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON
srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN
srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN
srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY
WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'
AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'
Group by
srm.EPISODES.MEDREC_NO,
srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.EPISODE_TYPE,
Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,
srm.EPISODES.EPISODE_DATE,
srm.EVENT_HISTORY.EVENT_DATE,
srm.CHART_COMPONENTS.STATUS_DATE
|||You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.
Code Snippet
(select max()
...
where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY
having count(*) .... ) as Chart_Comp_Date
|||
Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! 
My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.
select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc
No comments:
Post a Comment