find records from the day before a record in another field 1

[scottaherbst]

I'm using CR 10 with an sql server database.

I need to look at table_x and pull Client#, Client_visit, and start_date. No problem

Then I need to look at table_y and find any assessment for that client that was done on that start_date or the day after.

I'm trying to do this with command objects, so forgive me if this question really belongs on the SQL page.

Let me know what you think

 

[lbass]

You should be able to use something like:

Select table_x.`Client#`, table_x.`Client_visit`, table_x.`start_date`, table_y.`assessment`, table_y.`testdate`
From table_x inner join table_y on
table_x.`Client#` = table_y.`Client#`
where
(
table_y.`testdate` >= table_x.`start_date` and
table_y.`testdate` < table_x.`start_date` + 2
)

This would show those customers with test dates on the same or next day. If, however, you want to show ALL table_x customers, then you cannot use any selection on table_y and you would have to change the join to a left join.

-LB

 

[scottaherbst]

I actually do need it to be a left join and when I did it it worked fine as long as I joined on the two dates being equal (i.e., tablex.date = tabley.rating date). When I tried to add in the 'and' statement (tried it with an 'or' as well) I got an error message. Any ideas?

 

[lbass]

The left join will not work if you reference tabley in the where clause. What are you trying to display in your report? Why do you need the left join? If you want help troubleshooting, please show the command you developed.

-LB

 

[scottaherbst]

Below is the command line that I am using currently. I need a left join because I also need to show the records where no assessment was given within a day of admission. Ultimately what I want to report is the percentage of people who are not getting the assessment when they come in. So far I'm not referencing tabley (i.e., LOCUS_Adult_Assessment) in the where clause. Let me know what you think. Thanks:

SELECT view_episode_summary_discharge.PATID,
view_episode_summary_discharge.episode_number,
view_episode_summary_discharge.preadmit_admission_date,
view_episode_summary_discharge.date_of_discharge,
LOCUS_Adult_assessment.rating_date FROM
SYSTEM.view_episode_summary_discharge view_episode_summary_discharge
LEFT OUTER JOIN SYSTEM.LOCUS_Adult_assessment LOCUS_Adult_assessment
ON ((view_episode_summary_discharge.PATID = LOCUS_Adult_assessment.PATID)
AND (view_episode_summary_discharge.Facility = LOCUS_Adult_assessment.Facility)
AND (view_episode_summary_discharge.preadmit_admission_date = LOCUS_Adult_assessment.rating_date))
WHERE (SUBSTRING(view_episode_summary_discharge.program_code, 4, 2) = "IP")
AND ((view_episode_summary_discharge.preadmit_admission_date >= {?From_Date}) AND
(view_episode_summary_discharge.preadmit_admission_date <= {?To_Date}) AND
(view_episode_summary_discharge.program_X_RRG_code = {?RRG})) ORDER BY
view_episode_summary_discharge.PATID,
view_episode_summary_discharge.preadmit_admission_date

 

[lbass]

Looks okay to me. Then you would create a formula for the test dates like {@intesttimeframe}:

if {table_y.testdate} >= {table_x.start_date} and
{table_y.testdate} < {table_x.start_date} + 2 then
1

If you have a group on {table.patientID}, you can then test for whether they received a test within a day or not, by using a formula like this:

if sum({intesttimeframe},{table.patientID}) = 0 then "Not Within a Day"

You would have to use variables to do the percentage.

-LB