find previous time in another table?

[ericb123]

Using CR11 and SQL2000, I have a table of employee productivity, grouped by username:

claimID(number) Type(char) Completed(DateTime)

Now we have another table called "checkin-checkout" which is big table of every time a claimID was "checked out" to someone or "checked in" by someone.

So I've added this table, with a left outer join on the ClaimID, but when I add this field to my report, it adds multiple records for each claimID because it could have been checked in or out multiple times.

I'm trying to create a formula that can find the most recent record from this table based on the datetime from the productivity table. This would tell me the time it was last checked out to someone. And then the difference between these two datetimes would be the length of time the person was working on it.

So, for example, productivity table has a claimID:

ClaimID Completed
12345 6/30/08 11:30:00

And the Checkedin-Checkedout table has the following records:

ClaimID Date Activity
12345 6/28/08 13:00:00 Checked Out
12345 6/28/08 15:30:00 Checked In
12345 6/30/08 11:15:00 Checked Out
12345 6/28/08 11:30:00 Checked In

So I'm trying to create a formula that will find and show the datetime of 6/30/08 11:15:00 because it's one prior to the 6/30/08 11:30 one in the productivity table. Then I can create another formula to calculate the difference and show that the person worked on it for 15 minutes.

Any help on how to do this is greatly appreciated, thanks!

 

[lbass]

You should also link on the datetime fields, using a >= join FROM the productivity table TO the Activity table. Then create a SQL expression {%maxactdt}:

(
select max(`DateTime`)
from Activity A
where A.`ClaimID` = Activity.`ClaimID`
)

In the record selection formula, use:

{Activity.DateTime} = {%maxactdt}

Then you should be able to use a datediff formula like this:

datediff("n",{Activity.DateTime},{Productivity.DateTime})

-LB

 

[ericb123]

Thanks so much for the reply, but my SQL expressions option is greyed out, I cannot create one. Not sure why this is, thanks!

 

[ericb123]

According to this post (

http://www.tek-tips.com/viewthread.cfm?qid=1092642)

you have to have the same data source, and I failed to mention that these 2 tables are from different data sources.

 

[lbass]

Okay, do you still have the option of the >= link of the two date fields?

Then another option would be to go to report->selection formula->GROUP and enter:

{Activity.DateTime} = maximum({Activity.DateTime}, table.ClaimID})

You will get the correct value using the datediff formula, but if you plan to then summarize the results across claims, you would need to use running totals, instead of inserted summaries, since the non-groupselected records would still be "in" the report, though not displayed.

Or you could use:

datediff("n",maximum({Activity.DateTime}, table.ClaimID}), {Productivity.Datetime})

...but you would have to use a variable to summarize this if you need to.

-LB

 

[ericb123]

Once again you've saved me lbass! Thanks!

 

[ericb123]

One more question, I'm appending to this thread so I don't have to re-type the background:

Now that I've got thes 2 tables joined on ClaimID, the report details are sorted by ClaimID. So I added another field to the record sort expert of "Processed Date" because the manager requested it be sorted this way.

But the report doesn't change, it's still sorted by ClaimID in ascending order. I assume it because of this group selection formula, but is there any way to change the record sorting?

Thanks again!

 

[lbass]

Try doing a group sort (report->group sort) on the date field.

-LB

 

[ericb123]

Thanks lbass. I tried that, but the date field is not listed as a group option to sort on.

So I tried adding that date field as another group, then sorting on it, but it still doesn't show up as an option to group sort on.

Thanks!

 

[lbass]

First insert a maximum on the date at the claimID level. Then go to group sort->claimID tab and choose "maximum of {table.date} as your sort field.

-LB

 

[ericb123]

Yes, thanks. That was already there, I just didn't realize it would sort by the date/time

Thanks again!