Finding what is NOT there.

[LLowrance]

DB: SQL
CR: 8.5

I have a report that is trying to find open files which do not have a current diary date. I work in the insurance biz and case managers have files that are open. All open files should have a diary entry established to keep the case managers on track and that a file doesn't get "Lost". Each diary entry has a status (Not Started, Completed). Of course if the file has a "Not Started" entry then it would not appear. The problem I'm having is claim John Doe has an entry in the diary table with a status of completed but now he has no future diary entry. I need this claimant to appear on an exception report so the case manager can establish a new diary entry.
I've tried null, "", a count of entries which = 0, but I can't get this case to appear.
The best I can describe it is "Finding something that isn't there".
Any help is appreciated.

-LLL

 

[synapsevampire]

Show example data from the tables involved, and the expected output.

If you need to show data that isn't there, create a blank report

-k

 

[LLowrance]

The expected output would be:

Claim_no Claimant Date_Opened Case_Type
750123456 Doe, John 5/1/2006 Lost Time

Here is the selection criteria as it is now:

{tbl_DW_ActivePeriod_Info.Case_type_Key} in [1, 12, 2, 26, 27, 5, 8] and
{LOGIN.tcm_unit} = {?TCMUnit} and
isnull({tbl_DW_ActivePeriod_Info.close_date}) and
Count ({diary.diary_key}, {tbl_DW_Case_info.Case_Key}) = 0

Not if this information is helpful.
Thanks

-LLL

 

[lbass]

I can't tell what your record selection formula does, since you haven't provided the code descriptions, but I think you should allow all dates and statuses, so that you can determine the most recent date and the corresponding status. Change your record selection formula accordingly. Then go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{tbl_DW_Case_info.Case_Key}) and
{table.status} = "Completed"

That should pull those customers with most recent status of "Completed". Not sure that was the only thing you wanted to do though.

-LB

 

[synapsevampire]

Example data would have clarified this, a shame you only did the latter half of the request.

-k

 

[CR85user]

The key point as pointed out by those above and what they are trying to ascertain is 'what value IS being used against these files' so that they can help you to report on it.

Can you cl;arify the following?

"The problem I'm having is claim John Doe has an entry in the diary table with a status of completed but now he has no future diary entry."

By 'no future diary entry' do you mean no entry at all for {tbl_DW_ActivePeriod_Info.close_date} or that the date of {tbl_DW_ActivePeriod_Info.close_date} is not ahead of the current?

Looking at your selection criteria I can only assume you are looking for null/blank entries for this field.

If this is the case then you need to check what the default value of this field is. It may be that the field if not populated defaults to a set date value: 1/1/1901 etc...

If you have a specific case which you know matches what you are looking for simply report on that specific account - or run an SQL query against it to see exactly what value is showing for this field - Then you will know what you are looking for.

It is likely that as opposed to having a null value, a set date is being entered for this field from your description above.

Again - Sorry to bang the same drum as everyone else but sample data & any other information you can offer would help a lot.

'J

 

[LLowrance]

My apologies, it was late in the day yesterday. A new day, a new outlook:

tbl_ActivePeriod_info - this is populated by a sp which gives the maximum active period. An active period is defined as a period in which the file is open and closed along with a case type. Null(close_date) is just a way to get files that are open. There can be multiple active periods per claim as a file can open and close serveral times during the life of the file. The case type key is linked to a case_type table which is where the description of the case_type_key would be.
Table:
active_period_key - PK
case_key - FK
open_date
close_date
Case_type_key

Diary - this table tracks all diary entries a case manager enters. They are reminders that on a future date some action needs to be done. They are labled "Not Started" when entered. Once the action has been done, the diary entry is changed to "completed".
Table:
Diary_key - PK
case_key - FK
Due_date
login_id
completed_date
diary_status_key - linked to diary_status_tbl where the description is

With that, John Doe had a diary entry "completed" on 6/7/06, but the case manager didn't set a new diary for the future. Therefore, that case should appear on my exception report.

I hope this makes things more clear.

Thanks again,






-LLL

 

[lbass]

Sample data means showing several rows of detail level data, with identifying labels and showing group structure as well.

You might try a GROUP selection formula (report->selection formula->GROUP) like the following:

maximum({diary.completed_date},tbl_DW_Case_info.Case_Key}) > maximum({diary.due_date},tbl_DW_Case_info.Case_Key})

This would pick up cases where the most recent completed date was greater that the most recent due day, i.e., no new diary entry.

-LB