Most Recent Date

[unique12u]

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

 

[PHV]

Anyway, I'd change this:
fm_cost.fmc_terminal = f.fmc_vendor
with this:
fm_cost.fmc_terminal = f.fmc_terminal

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[unique12u]

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

Any other help?

 

[unique12u]

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)

 

[PHV]

What about this ?

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[unique12u]

PHV:

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

 

[unique12u]

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.

 

[unique12u]

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)