cross tab on group summaries 1

[scottaherbst]

Hi I posted about this about a week ago and the responses I got almost has it working. Here's the original post updated with a couple of things I don't think I was 100% clear on.

I am working with a client database. I'm pulling from 2 tables. The first is service_records and that gives me the client_id and service_number. I've got it joined (left-inner) to the assessment_table on client_id and service_number so it returns the date_of_assessment.

What this returns is any instance where there is a service number and the date_of_assessment (if there is any). If there wasn't an assessment given at all, it returns the service_number with a Null value for date_of_assessment. If more than one assessment was given for that service_number, it returns one line for each assessment given. I've also got a formula field that calculates the days since the assessment and another that takes that result and categorizes that record into "0-90 days", "91-180", "181 days +", and "no assessment". Here's an example minus the formula fields:

Client_ID Service_Number Date_of_assessment Location

1310 12 Detroit
1311 6 4/1/2007 Hell
1311 6 1/2/2007 Hell
1311 6 8/15/2006 Hell
1311 13 Hell
1311 14 Hell

I then group by client_id and have the most recent assessment given, the days since that assessment, and the category in the header. Ultimately, all I'm really interested in is the last assessment.

(The feedback I got had me write the following command-line:

SELECT service_records.client_id, service_records.location, service_records.service_number, assessment_table.date_of_assessment
FROM SYSTEM.service_records service_records
LEFT OUTER JOIN SYSTEM.assessment_table assessment_table ON ((service_records.client_id = assessment_table.client_id) AND (service_records.service_number = assessment_table.service_number))
WHERE(assessment_table.date_of_assessment IS NULL or (assessment_table.date_of_assessment = (SELECT MAX(A.date_of_assessment) FROM SYSTEM.assessment_table A, service_records B WHERE B.client_id = assessment_table.client_id AND A.client_id = B.client_id AND A.service_number = b.service_number)))
ORDER BY service_records.cleint_id

What this returns are data that look like this:

Client_ID Service_Number Date_of_assessment Location

1310 12 Detroit
1311 6 4/1/2007 Hell
1311 13 Hell
1311 14 Hell

What I want are data that look like this:

1310 12 Detroit
1311 6 4/1/2007 Hell


So - basically, for some services, clients are given an assessmnet and for others they aren't and what I've got now gives me one line for each service they received. I'm only interested, however, in knowing if they were given an assessment at all and, if not, having the report return a line with a null value for the assessment. Any ideas. Obviously I'm stumped.

 

[lbass]

Please do NOT start a new thread on the same topic--this forced me to try to look back and forth between the two threads. Try this change to the SQL:

SELECT service_records.client_id, service_records.location, service_records.service_number, assessment_table.date_of_assessment
FROM SYSTEM.service_records service_records
LEFT OUTER JOIN SYSTEM.assessment_table assessment_table ON ((service_records.client_id = assessment_table.client_id) AND (service_records.service_number = assessment_table.service_number))
WHERE
(select max(A.date_of_assessment)
from SYSTEM.assessment_table A
WHERE A.client_id = SYSTEM.assessment_table.client_id) IS NULL or
assessment_table.date_of_assessment =
(SELECT MAX(A.date_of_assessment)
FROM SYSTEM.assessment_table A
WHERE A.client_id = SYSTEM.assessment_table.client_id)
ORDER BY service_records.client_id

-LB

 

[scottaherbst]

Got it. In the future I'll keep it in the same thread. Thanks for the reply. I put in the recommended changes and am now getting error message 14 - a comparison operator is required here. Been playing around with it without any progress. Thanks for the help.
-S

 

[lbass]

Please paste in your actual command.

-LB

 

[scottaherbst]

Here it is. I did change a couple things from the original so that I no longer get an error message (explanation below). However, now its only returning records for clients where there was an assessment given at some point. Yes, only one record per client, but I also want to know about those for whom no assessment was given.

Also, I'm pasting it in as I have it in my report. What I had before was a watered down version with alias table names.

SELECT view_episode_summary_current.PATID, view_episode_summary_current.program_code, view_episode_summary_current.program_X_dflt_location_value,
View_episode_summary_current.EPISODE_NUMBER,
View_episode_summary_current.preadmit_admission_date,
View_episode_summary_current.program_X_RRG_code,
LOCUS_Adult_Assessment.functional_status,
LOCUS_Adult_Assessment.PATID,
LOCUS_Adult_Assessment.EPISODE_NUMBER,
LOCUS_Adult_Assessment.rating_date
FROM SYSTEM.view_episode_summary_current view_episode_summary_current
LEFT OUTER JOIN SYSTEM.LOCUS_adult_Assessment LOCUS_Adult_assessment ON ((view_episode_summary_current.PATID = LOCUS_Adult_Assessment.PATID) AND (view_episode_summary_current.Episode_Number = LOCUS_adult_assessment.episode_number))
WHERE View_episode_summary_current.program_X_RRG_code = {?RRG} AND locus_Adult_Assessment.rating_date = (select MAX(LOCUS_ADULT_Assessment.rating_date) FROM System.LOCUS_ADULT_ASSESSMENT WHERE LOCUS_adult_assessment.patid IS NULL or (LOCUS_Adult_assessment.rating_date = (SELECT MAX(A.rating_date) FROM SYSTEM.LOCUS_Adult_assessment A, View_episode_summary_current B WHERE B.patid = view_episode_summary_current.patid and A.patid = B.patid AND A.episode_number = b.episode_number)))
ORDER BY view_episode_summary_current.PATID,
LOCUS_Adult_Assessment.rating_date

The changes that I made to make it work were pretty small. First, in the original the piece that now says:

WHERE View_episode_summary_current.program_X_RRG_code = {?RRG} AND locus_Adult_Assessment.rating_date = (select MAX(LOCUS_ADULT_Assessment.rating_date) FROM...

Read as:

WHERE (select MAX(LOCUS_ADULT_Assessment.rating_date) FROM...

That seemed to take care of the error message, but I can't see why its not returning records for clients with no assessment.

 

[lbass]

You didn't really implement what I suggested. Try:

SELECT view_episode_summary_current.PATID, view_episode_summary_current.program_code, view_episode_summary_current.program_X_dflt_location_value,
View_episode_summary_current.EPISODE_NUMBER,
View_episode_summary_current.preadmit_admission_date,
View_episode_summary_current.program_X_RRG_code,
LOCUS_Adult_Assessment.functional_status,
LOCUS_Adult_Assessment.PATID,
LOCUS_Adult_Assessment.EPISODE_NUMBER,
LOCUS_Adult_Assessment.rating_date
FROM SYSTEM.view_episode_summary_current view_episode_summary_current
LEFT OUTER JOIN SYSTEM.LOCUS_adult_Assessment LOCUS_Adult_assessment ON ((view_episode_summary_current.PATID = LOCUS_Adult_Assessment.PATID) AND (view_episode_summary_current.Episode_Number = LOCUS_adult_assessment.episode_number))
WHERE View_episode_summary_current.program_X_RRG_code = {?RRG} AND
(
(
SELECT MAX(A.rating_date) FROM SYSTEM.LOCUS_Adult_assessment A
WHERE A.patid = SYSTEM.LOCUS_Adult_assessment.patid
) is null or
(
locus_Adult_Assessment.rating_date =
(
select MAX(A.rating_date) FROM System.LOCUS_ADULT_ASSESSMENT A WHERE
A.patid = SYSTEM.LOCUS_Adult_assessment.patid
)
)
)
ORDER BY view_episode_summary_current.PATID,
LOCUS_Adult_Assessment.rating_date

-LB

 

[scottaherbst]

Thanks for the suggestion, however, I keep getting the same thing as I did with the earlier SQL statement. When clients get multiple services, it returns a line for each service they get.

 

[lbass]

Did you copy the command just as I created it or did you adjust it manually? If the latter, please copy into the thread again.

-LB

 

[scottaherbst]

I made two minor changes b/c I was getting an error message saying that SYSTEM.Locus_adult_assessment wasn't an applicable table. So I removed 2 instances of "SYSTEM." (e.g., WHERE A.patid = SYSTEM.LOCUS_Adult_assessment.patid). Other than that I put it in verbatim. Here it is:

SELECT view_episode_summary_current.PATID, view_episode_summary_current.program_code, view_episode_summary_current.program_X_dflt_location_value,
View_episode_summary_current.EPISODE_NUMBER,
View_episode_summary_current.preadmit_admission_date,
View_episode_summary_current.program_X_RRG_code,
LOCUS_Adult_Assessment.functional_status,
LOCUS_Adult_Assessment.PATID,
LOCUS_Adult_Assessment.EPISODE_NUMBER,
LOCUS_Adult_Assessment.rating_date
FROM SYSTEM.view_episode_summary_current view_episode_summary_current
LEFT OUTER JOIN SYSTEM.LOCUS_adult_Assessment LOCUS_Adult_assessment ON ((view_episode_summary_current.PATID = LOCUS_Adult_Assessment.PATID) AND (view_episode_summary_current.Episode_Number = LOCUS_adult_assessment.episode_number))
WHERE View_episode_summary_current.program_X_RRG_code = {?RRG} AND
(
(
SELECT MAX(A.rating_date) FROM SYSTEM.LOCUS_Adult_assessment A
WHERE A.patid = LOCUS_Adult_assessment.patid
) is null or
(
locus_Adult_Assessment.rating_date =
(
select MAX(A.rating_date) FROM System.LOCUS_ADULT_ASSESSMENT A WHERE
A.patid = LOCUS_Adult_assessment.patid
)
)
)
ORDER BY view_episode_summary_current.PATID,
LOCUS_Adult_Assessment.rating_date

LBASS - I can't tell you how much I appreciate all your help on this. Thanks, Scott

 

[lbass]

I'm not sure--I can't test this-- but see what happens if you remove the link on the episode number in the left join clause.

-LB

 

[scottaherbst]

Neat. Its not what I want, but its getting there. It still returns a line of data for each different episode number, but now it fills in the most recent assessment date. So what used to look like this

client id episode # assessment date
1111 12
1111 13 4/4/07
1111 6 3/12/06
2222 10
2222 14


Now looks like this:
client id episode # assessment date
1111 12 4/4/07
1111 13 4/4/07
1111 6 4/4/07
2222 10
2222 14

 

[lbass]

How does the crosstab fit into this?

-LB

 

[scottaherbst]

Beautifully! LB - I hope you live 1000 years and populate the world with your children. -Scott

 

[lbass]

Are you saying you have this working as you wish?

-LB

 

[scottaherbst]

Yes. It doesn't return one line but when I thought about that didn't matter so much, as I really just needed the most recent date or, if one wasn't done, a null value. So if there are three lines, each with the same date, then the formula I use to calculate how many days have passed since then will return the same value for each record, which will in turn return the same category value (e.g., 0-90 days since last; 91-180 days since; etc.). Then, all I had to do was do a cross-tab with program location as the row value and categories as the columns, and then do a distinct count on patient_id.

So it worked perfect. Again, thanks for the help and patience. -Scott