I am trying to write a query to retrieve data from a rate table, that will basically prmpt for a date to be entered, then pull out all rates that are valid within this date. The rate table has an effective date and an expiration date. My problems now lies because there could be records that have all primary key values the same except for the expiration date. And I need to adjust to my current query a new query that pulls back only the most current about closest to expiring rate?
ie.
eff exp from to rate commodity type
9/1/01 12/1/01 a b 1.30 fak L
9/1/01 12/1/05 a b 1.20 fak L
with the above 2 records as an example I would only like to pull back the first row assuming my date entered when prompted was prior to 12/1/01...otherwise pull the other..
My query now is as such and I need help adding this additional part...
SELECT
a.SCAC,
a.[Lane From Point],
a.[Lane To Point],
a.[Effective Date],
a.[Rate Per Mile],
a.[Expiration Date],
a.[Equipment Type],
a.[Shipment Type],
a.Commodity,
FROM table2 AS a
WHERE (a.[New Effective Date] <= [Enter Effective Date]
and [Enter Effective Date] <= a.[New Expiration Date])
and a.[New Effective Date] = (select Max(b.[New Effective Date]) from table2 b
where ([New Effective Date] <= [Enter Effective Date] and [Enter Effective Date] <= [New Expiration Date])
and a.SCAC = b.SCAC
and a.[Equipment Type] = b.[Equipment Type]
and a.Commodity = b.Commodity
and a.[Lane From Point] = B.[Lane From Point]
and a.[Lane To Point] = B.[Lane To Point]
);
the pk's are scac, eff date, exp date, lane from, lane to, commodity, and equip type...
my current query pulls over more cols (deleted to save space here) then limits the query to the current effective date with the max piece. I assume I need some sort of addition to get the min exp date that is still greater than the entered date. Enter New Effective date is a prompted entry and new effective date and new exp date are cols in the table that are converting the eff date and exp date to true date values as they are stored as 010101 type numbers..
Thank you...
ie.
eff exp from to rate commodity type
9/1/01 12/1/01 a b 1.30 fak L
9/1/01 12/1/05 a b 1.20 fak L
with the above 2 records as an example I would only like to pull back the first row assuming my date entered when prompted was prior to 12/1/01...otherwise pull the other..
My query now is as such and I need help adding this additional part...
SELECT
a.SCAC,
a.[Lane From Point],
a.[Lane To Point],
a.[Effective Date],
a.[Rate Per Mile],
a.[Expiration Date],
a.[Equipment Type],
a.[Shipment Type],
a.Commodity,
FROM table2 AS a
WHERE (a.[New Effective Date] <= [Enter Effective Date]
and [Enter Effective Date] <= a.[New Expiration Date])
and a.[New Effective Date] = (select Max(b.[New Effective Date]) from table2 b
where ([New Effective Date] <= [Enter Effective Date] and [Enter Effective Date] <= [New Expiration Date])
and a.SCAC = b.SCAC
and a.[Equipment Type] = b.[Equipment Type]
and a.Commodity = b.Commodity
and a.[Lane From Point] = B.[Lane From Point]
and a.[Lane To Point] = B.[Lane To Point]
);
the pk's are scac, eff date, exp date, lane from, lane to, commodity, and equip type...
my current query pulls over more cols (deleted to save space here) then limits the query to the current effective date with the max piece. I assume I need some sort of addition to get the min exp date that is still greater than the entered date. Enter New Effective date is a prompted entry and new effective date and new exp date are cols in the table that are converting the eff date and exp date to true date values as they are stored as 010101 type numbers..
Thank you...
