I want the MAX date and cases where there are no records

[scottaherbst]

I'm working with CR10, SQL server DB.

I'm working with two tables. The first (view_episode_summary_current) holds records for any open service. The second holds information for an assessment that all clients should have every 90 days. I want to return the most recent record of that assessment and, in cases where none was ever given, that too. The command I've written currently returns records for clients only where as assessment has been given. I'd like it to return a patient record even when an assessment has never been given. Can this be done in a command line?

Here's the one I'm using that is working perfectly with the exception that it only gives me records where there is an assessment:

SELECT view_episode_summary_current.PATID, view_episode_summary_current.program_code,
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 Locus_Adult_Assessment.rating_date = (SELECT MAX(A.rating_Date) FROM SYSTEM.LOCUS_ADULT_ASSESSMENT A, View_Episode_Summary_Current B
WHERE A.PATID = LOCUS_ADULT_ASSESSMENT.PATID AND B.PATID = View_episode_summary_current.PATID AND A.PATID = B.PATID AND View_episode_summary_current.program_X_RRG_code = '{?RRG}')
ORDER BY view_episode_summary_current.PATID,
LOCUS_Adult_Assessment.rating_date

 

[synapsevampire]

Try changing the where clause:

WHERE
Locus_Adult_Assessment.rating_date 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 A.PATID = LOCUS_ADULT_ASSESSMENT.PATID AND B.PATID = View_episode_summary_current.PATID AND A.PATID = B.PATID AND View_episode_summary_current.program_X_RRG_code = '{?RRG}')
These types of questions are generally better served posted in the appropriate database forum as this is a database programming question, not Crystal.

Plus, they're a whole lot better at it then most are hereabouts.

-k

 

[lbass]

scott,

It would also be helpful if you didn't start multiple related threads, but instead continued with one thread.

-LB