Record Seclect Based on Last Entry Only 2

[jtflex]

Hi Everybody,

My organization is going to Crystal Reports and I'm just now learning it. We are using Crystal Reports 9, and have an Oracle database.

Here's my question; I'm trying to write a report that is based on a storage location. It involves two tables: (This is a simplified version

Case_# Tag_# Description
04-1234 556123 Clock Radio
04-1234 556124 CD Player
04-1234 556125 Telephone
04-1234 556126 Remote Control
04-1256 621335 Television
04-1256 621336 Dresser
04-1256 621337 Couch

Tag_# Location Date_Moved Time_Moved
556123 Receipt1 2/4/2004 15:31
556123 097B 2/4/2004 15:00
556123 Pallet 2/3/2004 8:00

556124 Receipt2 2/4/2004 16:00
556124 097B 2/4/2004 15:30
556124 Receipt1 2/4/2004 15:00

556125 097B 2/4/2004 15:00
556125 Pallet 2/3/2004 8:00

What I'm trying to do is print a report that list all tag_#'s in Case_# 04-1234 that are in the location Receipt1.

The problem I'm having is tag_# 556124 shows up on the list because it once was in Receipt1, but it shouldn't it's now in Receipt2.

I'm thinking the solution is to go to each tag_# in case_# 04-1234 and read the last entry only. If it matches the location I want, it selects that record, else it moves on to the next tag_# for that case.

I havn't found anything in a couple books I bought that talks about selecting just that last record. Is there such a way to do that? Or is there another solution to this problem?

Keep in mind that this is a huge database with over 100,000 tag_# and growing, not to mention all the locations those tag_#'s move around in.

You help is most appreciated on this (sorry my cut&paste looks bad on this).
Thanks,
Joe

 

[synapsevampire]

It's odd (and wasteful) that they store date and time in different fields, however I think that this is easy enough to work around.

To simplify your request, I think that you want the Location for each case (or perhaps every case) for each maximum date_moved.

Group by case, and select Report->Edit Record Selection-Group and place something like:

(table.date_moved} = maximum({table.date_moved},{table.case})

This will limit rows to only the maximum date_moved for each case.

If you only want a specific case, create a parameter (Insert->Field Object->Right click parameters and select new) and use the parameter in Report->Edit Selection Formula-Record:

{table.case} = {?MyCaseParm}

Now the report will prompt for the case of interest and also limit the rows to only that case.

-k

 

[synapsevampire]

Ooops, I forgot to mention that you should construct a SQL Expression to use instead of the Date and Time fields.

Please post the actual format of the date and time fields.

-k

 

[tektipdjango]

jtflex
I think there's a problem in the database design.
You're needing to check all the mvts to know where are the tags?
Do you know how big will be the size of the history file for mvts in one year or two. If the tag is not moving you have to scan a huge amount of data.
Like a warehouse app you have to history the mvts and at each mvt follow the store/location inventory.
Then the query will seem quite obvious, and a lot quicker!

 

[lbass]

Try grouping on {table.case} and then on {table.tab} and then go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.tag}) and
{table.location} = "Receipt1"

-LB

 

[jtflex]

Thanks Snapsevampire, here's how the Date and time fields are set up:
Date is DateTime data field; mm/dd/yyyy
Time is a String data field; 6 char for hhmmss.

I haven't tried it yet but you refer to(table.date_moved} = maximum({table.date_moved},{table.case})

The case table doesn't really contain move locations. It's the tag_# table that does. If I use that formula in the group section will it only bring back one tag_# (the last date)? I plan to use this to list multiple tag_#'s that are in the same location from one case_#.

Basically, we use locations to seperate items from single cases to make receipts for different persons.

Thanks for your help on this.

Joe

 

[jtflex]

Thanks Tektipdjango,

Yes it's a large database and we are coverting over to a new system that will use it (along with Crystal Reports). Our database has to maintain what we call Chain of Custody where every item that has been touched or moved is tracked, and a record is kept on all those movements. That's why I'm trying to see if Crystal Reports will only look at the last location a tag item moved to and ignore the rest of the entries in the Tag_# table.

Joe

 

[lbass]

Did you try my suggestion? This will return the most recent tag (per tag number) IF it is for receipt#1--which is what I thought you wanted.

-LB

 

[synapsevampire]

LB has it right if you want it grouped by the tag, but you still need to create a datetime field by which to test against, having a date and time as different fields won't work correctly.

Again, I'd need the format of these fields to help there.

-k

 

[jtflex]

Yes lbass I just tried it and it still picks up that tag that I didn't want. I'm just now reviewing what I have coded to see if I did something else wrong.

Thanks,
Joe

 

[jtflex]

Maybe I'm asking too much in the record selection. Maybe I should just ask for the last location on all tag's in that case and then filter out what doesn't match the location I'm looking for. (Not sure how that looks yet though).

What does everybody think?

Joe

 

[lbass]

SV --I totally missed that they were from separate fields, although jtflex does say:

"Date is DateTime data field; mm/dd/yyyy
Time is a String data field; 6 char for hhmmss."

But the date field is formatted as a date field, not datetime. If it is really a date field, with a separate time field, then I think you could make it a datetime field {@datetime} by using:

datetime(year({table.date}),month({table.date},day({table.date}),val(left({table.time},2),val(mid({table.time},3,2),val(mid({table.time},5,2)))

There might be a simpler way, but this seems to work.

jtflex--

I still think my solution should work, unless I've misunderstood your requirements.

With the changed datetime field, the group selection formula should read:

{@datetime} = maximum({@datetime},{table.tag}) and
{table.location} = "Receipt1"

Using my suggestion only the following record would be returned from your sample data:

556123 Receipt1 2/4/2004 15:31

If this is not what you want, I think you should give us another sample of data along with a sample of the data you want returned after the selection criteria are applied.

-LB

 

[synapsevampire]

LB: Yeah, your formula should work, it just won't be as fast as creating a SQL Expression so that the server can process it.

I missed the post with the formats available for date and time, and unfortunately I'm not in the office so I don't have an Oracle database to test aginst (only SQL Server currently on my laptop).

Someone here with Oracle and basic PL/SL should be able to work it out using a CAST function.

-k

 

[jtflex]

I thought it was odd that the date field is a DateTime data field but on my reports it only show date and time is seperated as a string.

I'm running upstairs to our data people with this printout and make sure.

Thanks,
Joe

 

[jtflex]

Just checked and it is a DateTime data type, however, the time on all data is 00:00:00. Guess I'll still have to combine the time with it.

Joe

 

[lbass]

I think my datetime formula will work as is even if your field is already a datetime.

-LB

 

[jtflex]

Here's the coding for my {@EvidDateTime} function:

datetime(year({INEVI7_VIEW.Activity_Date}),month({INEVI7_VIEW.Activity_Date}),day({INEVI7_VIEW.Activity_Date}),val(left({INEVI7_VIEW.Time},2),val(mid({INEVI7_VIEW.Time},3,2),val(mid({INEVI7_VIEW.Time},5,2)))))

I'm getting the error: Too many arguments have been given to this function.

Could this be caused because I'm trying to join a DateTime and String field?

Thanks for taking the time looking at this,

Joe

 

[synapsevampire]

Try:

datetime(year({INEVI7_VIEW.Activity_Date}),month({INEVI7_VIEW.Activity_Date}),day({INEVI7_VIEW.Activity_Date}),val(left({INEVI7_VIEW.Time},2),val(mid({INEVI7_VIEW.Time},3,2),val(mid({INEVI7_VIEW.Time},5,2))

There may be other concerns though if the time field only contains 5 places for times less than 10.

In that case you'd have to use a if len(trim({INEVI7_VIEW.Time})) = 5 then
... adjust to use a left(...,1)
else
above formula

Example data would have been useful.

-k

 

[jtflex]

It work!, it work, it work, it work!!!!!.

You guys just made my day.

Thanks a million,

Joe