×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Most Recent Date

Most Recent Date

Most Recent Date

(OP)
I have the below table and want to get the below results. I basically need a way to get only the most recent date entry from a table of prices.

I have tried this:

select fmc_terminal, fmc_vendor, fmc_prodlnk, fmc_date, fmc_time, fmc_cost
FROM fm_cost
WHERE fm_cost.fmc_date =
           (Select max(fmc_date) from fm_cost as f
             where fm_cost.fmc_terminal = f.fmc_vendor and
               fm_cost.fmc_vendor = f.fmc_vendor
              and fm_cost.fmc_prodlnk = f.fmc_prodlnk
              GROUP by f.fmc_terminal, f.fmc_vendor, f.fmc_prodlnk)
I am getting no where fast with everything I am trying.

Help please.
fmc_terminal fmc_vendor    fmc_date fmc_time fmc_prodlnk fmc_cost
1003    46040025    2/10/2010    18:00    9    1.9925
1003    46040025    2/11/2010    18:00    9    2.0085
1003    46040025    2/12/2010    18:00    9    1.9605
1003    71520016    2/10/2010    12:30    9    1.977
1003    71520016    2/10/2010    18:00    9    1.99
1003    71520016    2/11/2010    18:00    9    2.0015
1003    71520016    2/12/2010    11:30    9    1.9715
1003    71520016    2/12/2010    18:00    9    1.9725
1003    71520016    2/15/2010    10:00    9    1.9725
1004    46520030    2/10/2010    18:00    99    1.9992
1004    46520030    2/11/2010    18:00    99    1.9757
1004    46520030    2/12/2010    18:00    99    1.9689
1004    46520030    2/10/2010    18:00    100    2.2715
1004    46520030    2/11/2010    18:00    100    2.248
1004    46520030    2/12/2010    18:00    100    2.2412
1004    46520030    2/10/2010    18:00    101    2.0682
1004    46520030    2/11/2010    18:00    101    2.0446
1004    46520030    2/12/2010    18:00    101    2.0379

RESULTS

fmc_terminal fmc_vendor    fmc_date fmc_time fmc_prodlnk fmc_cost
1003    46040025    2/12/2010    18:00    9    1.9605
1003    71520016    2/15/2010    10:00    9    1.9725
1004    46520030    2/12/2010    18:00    99    1.9689
1004    46520030    2/12/2010    18:00    100    2.2412
1004    46520030    2/12/2010    18:00    101    2.0379

 

RE: Most Recent Date

(OP)
Sorry typo - I do have it correct. Still not getting results.

Any other help?

RE: Most Recent Date

(OP)
I have also just tried without success:

select fmc_terminal, fmc_vendor, fmc_prodlnk, fmc_date, fmc_time, fmc_cost
FROM fm_cost
WHERE fm_cost.fmc_date =
           (Select max(fmc_date) from fm_cost as f
             where fm_cost.fmc_terminal = f.fmc_terminal and
               fm_cost.fmc_vendor = f.fmc_vendor
              and fm_cost.fmc_prodlnk = f.fmc_prodlnk
              AND fm_cost.fmc_date = f.fmc_date
              AND fm_cost.fmc_time = f.fmc_time
       GROUP by f.fmc_terminal, f.fmc_vendor, f.fmc_prodlnk)
 

RE: Most Recent Date

(OP)
PHV:

This query gives me every record in the table - the join is not correct somewhere.

RE: Most Recent Date

(OP)
I can make this work using a temp table as follows:

SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;

select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

However, I need to be able to run this and send results to Excel. I tried creating a stored procedure that inserts the records into the temp table first and then telling MSQuery to execute the stored procedure. It will not allow the creation of the temp table from the ODBC connection in MSQuery.

CREATE PROCEDURE fm_cost_maxdate ()
SELECT fm_cost.fmc_terminal, fm_terminal.fmt_owner, fm_cost.fmc_vendor,
p_altname.pn_name, Max(fm_cost.fmc_date) as max_date, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
FROM fm_cost,fm_terminal,inv_header,p_altname
WHERE fm_cost.fmc_terminal = fm_terminal.fmt_code
AND fm_cost.fmc_prodlnk = inv_header.ivh_link
AND fm_cost.fmc_vendor = p_altname.pn_alt
GROUP BY fm_cost.fmc_terminal, fm_terminal.fmt_owner,
fm_cost.fmc_vendor, p_altname.pn_name, fm_cost.fmc_prodlnk,
inv_header.ivh_product, inv_header.ivh_desc
INTO TEMP t100;
END PROCEDURE

EXECUTE PROCEDURE fm_cost_maxdate ();
select t100.fmc_terminal, t100.fmt_owner,
t100.fmc_vendor,t100.pn_name,t100.max_date,t100.fmc_prodlnk,
t100.ivh_product,t100.ivh_desc, fm_cost.fmc_time, fm_cost.fmc_cost
from t100,fm_cost
where t100.fmc_terminal = fm_cost.fmc_terminal
and t100.fmc_vendor = fm_cost.fmc_vendor
and t100.fmc_prodlnk = fm_cost.fmc_prodlnk
and t100.max_date = fm_cost.fmc_date
and t100.max_date >= (TODAY-31)
order by t100.fmc_terminal, t100.fmc_vendor, t100.fmc_prodlnk

This also works fine. However, when I try to run this from the windows client I get the message that it cannot add the table t100.

This really should not be this hard to accomplish.

RE: Most Recent Date

(OP)
This query actually does work. The problem was performance on the actual table. Table has over 260,000 records. I re-indexed the table, Updated Statistics on the table and then added the date filter to get only records in the last 10 days. This still takes about 5 minutes to run, but it was running for over an hour and dumping garbage results. So much better.

Thanks for the input

select f1.fmc_terminal, f1.fmc_vendor, f1.fmc_prodlnk,
f1.fmc_date, f1.fmc_time, f1.fmc_cost
FROM fm_cost as f1
WHERE f1.fmc_date =
(Select max(f2.fmc_date) from fm_cost as f2
where f2.fmc_terminal = f1.fmc_terminal
and f2.fmc_vendor = f1.fmc_vendor
and f2.fmc_prodlnk = f1.fmc_prodlnk
and f2.fmc_date >= (TODAY-10)
GROUP by f2.fmc_terminal, f2.fmc_vendor, f2.fmc_prodlnk)
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close