Nested SQL or IF END LOGIC ?

Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.

episode_key chart_component_key deficiency_type deficiency_status status_date 13789881 173398 408 C 8/4/2007 13789881 173488 409 S 8/4/2007 13789881 173703 409 S 8/6/2007 13789881 174568 1028 S 8/7/2007 13789881 176213 421 S 8/9/2007 13789881 176214 421 S 8/9/2007 13789881 176215 421 S 8/9/2007 13789881 176216 421 S 8/9/2007 13789881 176218 421 S 8/9/2007 13789881 176219 406 D 8/9/2007

Can someone help me here?

Chuck,

You problem and sample data are a bit ambiguous.

It appears that all of this data belongs to one Episode -since there is but one Episode_key value.

Every Chart_Component-key appears to be unique.

So what is the expected result from this set of data?

|||

This should do it for you

Code Snippet

DELETED - read edit

Edit : I re-read the question and realized you said "all deficiency_status is equal to 'C'" not just that one of the records in the episdoe is set to 'C'.

|||

This one makes sure that ALL the definciency code fields in the recordset of one episode are 'C'

Code Snippet

CREATE Table episode_table (

episode_key INT,

chart_component_key INT,

deficiency_type INT,

deficiency_status CHAR,

status_date DATETIME

)

INSERT INTO episode_table VALUES (13789881, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789881, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789881, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789881, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789881, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176219, 406, 'D', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789882, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789882, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789882, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789882, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176219, 406, 'D', '8/12/2007' )

INSERT INTO episode_table VALUES (13789883, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789883, 173488, 409, 'C', '8/4/2007' )

INSERT INTO episode_table VALUES (13789883, 176219, 406, 'C', '8/12/2007' )

SELECT et1.episode_key, MAX(et1.status_date)

FROM episode_table et1 LEFT OUTER JOIN episode_table et2 ON

-- You only have to include the unique primary key fields here, i didn't want to assume

-- that you had one though

(et1.episode_key = et2.episode_key AND et1.chart_component_key = et2.chart_component_key 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)

I joined the tables on all fields to be sure, but you only need to include the fields in the primary key if there is one, and the deficiency status clause.|||Each patient episode may involve several physicians involved in their case and making notes in the chart. Each chart_component_key should signify a different physician making notes in the chart. A "completed" chart is defined as when all physicians have completed their charting thus reflecting a "C" in the deficiency_status fields.

I need to return a "Completed Chart Date" which would mean that all chart_component_keys would be "C" and I would then take the MAX(status_date) as the completed chart date.

|||

Thanks Shawn. I was able to modify this and make it work. This piece of code was to pull the final field in a report I am working on. I am trying to plug it into my main SQL code for the big report, but having difficulties. I'll start a new thread for this issue.

Thanks!

|||

You can use the query below:

Code Snippet

select t.episode_key, max(t.status_date) as completed_chart_date
from <your_table> as t
group by t.episode_key
having count(*) = sum(case t.deficiency_status when 'C' then 1 end);

|||

Very nice Umachandar!

I have to remember that approach, very efficient.