Start Date but no End Date.

[celauritz]

I have a database that has start dates but no end dates. I am trying to figure out what pieces of inventory were in a certain status on a certain date. The DB looks like this:

Inv # Status Start Date
123 AS 20060101
123 CAN 20060201
123 AS 20060301

If there were end dates, I could just write the formula (Status = AS and Start Date < X < End Date) where X is the date in question. However, with no end dates, it is not so easy. However, the database is set up in this way for a reason, I'm sure, because the start date for the next status is the end date for the one previous. It is obvious this is what's happening, and whoever wrote it could likely easily answer what inventory items were in AS status on date X. Can you?

 

[synapsevampire]

Yeah, it was set up this way because it's deisgned for inserts by applications, not querying the database.

In my oipinion, a lazy approach, but for performance reasons it can be beneficial.

It think what you want is:

Report->Selection Formula->Record

{table.date} <= {?MyDateParameter}

Then sort by the date ascending, group by the inventory item, place the fields in the group footer and suppress the details and the group header. So the last date <= the parameter for the items of interest will be displayed
in the group footer.

-k

 

[celauritz]

Almost, but not quite. That will give me any piece of inventory that was ever active before date X, whether it was cancelled on that date or not. For instance, if I do what you say, and use the above "Database" in my earlier post, and use 20060217 for the date, it still prints the top record.

Did I do it right, or am I misunderstanding?

 

[synapsevampire]

Well by top record do you mean the row with 20060101?
ou need to sort by the date field ascending because
if you did as suggested, only one row would display, and that would be the latest dates data which is less than or equal to the date passed by the parameter.

-k

 

[celauritz]

I have sorted by date (just as above). If I make MyParamaterDate 20060217 and status equal to AS, the details field shows the first line because 20060101 is less than 20060217 and the status on that line is AS.

Even though I'm grouping everything and pulling the information into the group footer, because this info would still be in the details field, it shows up in the report, when, because the inventory item was in CAN status on 2/17/06, it should not.

I appreciate your help and I'm probably just confused as I've been wrestling with this problem in this database for seemingly ever now.

 

[synapsevampire]

I see, well I hadn't suggested using the status = AS, so yes, that should be the row displayed, because that's exactly what you're asking for.

Why are you discussing the details section, my suggestion was "Then sort by the date ascending, group by the inventory item, place the fields in the group footer and suppress the details and the group header."

The details are suppressed, you can't see what's in there.

-k

 

[celauritz]

What I'm saying is "though the details field is suppressed, this is what would be in there," because though it is suppressed, it is still there.

Basically, if I use the example in the first post, it cannot show up in the report because it wasn't under AS status on Feburary 17th. I need to get a list of only those items of inventory that were under AS status on that date. This one, since it was under AS before 2/17, keeps showing up.

 

[lbass]

You should also add a suppression formula to the group footer:

{table.status} <> "AS"

Or, instead you could unsuppress the details section, and go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.inv#}) and
{table.status} = "AS"

This assumes that you still have a record selection formula of:

{table.date} <= {?date}

-LB

 

[synapsevampire]

As LB points out, you can do the equivalent of the suppression in the group selection formula and add to it whatever other criteria you need.

In either case, to get optimal performance we'd have to use advanced SQL. If your SQL is good or you have a dba with resources available, create a View or use a Command Object to add the SQL.

-k

 

[DataDivaJill]

how about linking the table twice?
and using the inv# as the link between them.
then use one as the start and the other as the end?
just thinking out loud here.