Getting just one of multiple records

[Dave]

Maybe this is simple, I don't know. I have a SQL server
stored procedure (that I can't modify or have modified)
which returns all of the customer's orders since some
date. For example, when I execute the stored procedure I
give a date as part of the procedure. Then the store
procedure returns: the Customer Name, Order Date, and
Order Value. Depending on the date entered and the
customer, there could be from 0 to whatever records
returned for a customer. Having no records for a
particular customer isn't an issue, but if there are
multiple records what I really want is just the most
recent record. This most recent record is the one I want
(for each custormer).

Is there an easy way to do this? I need to save the most
recent record for each customer in another table for use
in a separate application but the number of customers
which might return a record is variable and the number or
records each customer may return is variable. I could do
this in VBA code using record comparisons but this sounds
like it would be VERY slow given that just a couple of
days may be a total of a couple of thousand records and
often we may want the most recent records from over a week
ago.

TIA for any help,
Dave

 

[Tom Ellison]

Dear Dave:

Depends on your definition of "easy."

You can filter the results you have by using a "correlated subquery"
that returns the maximum of the date for only the selected orders.

If you would post the SQL of a query that gives you everything you
want except that it produces results for too many orders, I can
probably modify that to do what you want.

Does your Order Date column have the time of day in it? Is it
included in a Primary Key or other Unique Index, so that you cannot
have tow orders for the same customer on the same date? Such a
restriction may not be desirable, so I'll assume you do not.

In that case, if there are multiple orders for any Customer on the
same day, the query cannot be expected to return "the most recent
order," at least not based on the Order Date, when there is a "tie."
It would return all the orders for a customer for that most recent
date, naturally enough. If it is essential to see ONLY ONE order, you
could use some natural or arbitrary method of selecting one out of the
possibly many orders when this happens. Just how you would choose to
do that would depend on all your inside knowledge of what information
you have, and how you might want it to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Tom,

First thank you for the prompt reply.

Now to answer some of your questions. No I can't post the
SQL statement that produces too many records as it is a
stored procedure on a MS-SQL database. But even if I
could, it is 'owned' by others and for some reason (I have
already asked) they can't/won't modify it (in fact I have
never actually seen the SQL or even the underlying table
structure.)

Yes the Date field is actually a date/time field with 1
sec resolution. The underlying system is structured such
that the date/time for any particular customer is unique
(but can be repeated for different customers).

I don't know if the date/time is a Primary Key or not (as
again I haven't seen either the associated tables or SQL
statement(s). But give a customer value and the data/time
value, the record is then 'uniquely identified'.

I think your idea of a "correlated subquery" is similar to
the approach I was 'thinkin about' (but 'thinking about'
might be too strong, maybe I was dreaming is better). But
I was and still am a little confused on the
necessary 'syntax'.

I suppose I would have to build a new query which uses the
store procedure (ala a pass thru query in this case) and
then apply the max() expression to the date field to
extract the unique records. Does this sound close?

One other issue I have with this is how do I (within the
queries) pass a parameter to a pass-thru query. For this
piece I believe I will have to use VBA to 'dynamically'
build the proper string to pass thru. Or do you know of a
way to pass a parameter into a pass-thru query?

Thanks again for the reply and assitance.
Dave

 

[Tom Ellison]

Dear Dave:

We can write new SQL without modifying the existing Stored Procedure,
but need some kind of information from which to start. Without the
existing SQL or the table layouts I'm not sure how I could help you.

If we are to use the existing Stored Procedure and manipulate its
results I guess you could lay that out for me. I'm casting about
trying to see how to use the resutls of a stored procedure to run
another query. I'm not too hopeful there would be a good way to do
that. Stored Procedures don't naturally "nest" like Views do! You
might have to take the results and drop them into a temporary table -
we could automate that through a VBA recordset. But this sounds messy
and slow.

If you can just get some read-only permission to see the table
structure and/or read the SP code, you'd be way ahead here!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Dave]

Tom,

It appears that you are correct and as I suspected I have
not found a way to pass a parameter into a pass thru
query. However, using some VBA code to create a temporary
table from the stored procedure doesn't have 'bad'
performance and then I can do all my queries against that
table using the max date/time ides you gave me.

I have to do multiple queries against the table as both
the sp results and the info I have to extract is a little
more complex than I posted. (I tried to keep it simple to
focus on my root issues and keep things short and sweet.)
The method you proposed is certainly better and faster
than doing record by record comparisions or doing
customer/customer sub-queries.

The good news is this appears to all work with reasonable
performance. I agree that read only access to the tables
such that I could link to them would be much
better/easier/etc. Unfortunately the group I am working
with tends to be rather anal (sometimes for good reasons,
others just because they can be.)

Again, thanks for all the assistance.
Dave