Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006

    Unanswered: select current / most recent date

    I am trying to write a select statement that for each SKU will give me the current cost. If a SKU is no longer active, I want to return the cost on the last date when that SKU was active.

    My cost change table has the following format:
    Company, SKU, Cost, Date_Active, Date_Inactive.

    For example:
    CompanyA 11111 $0.50 1/1/05 1/15/05
    CompanyA 11111 $0.53 1/15/05 2/15/05
    CompanyA 11111 $0.56 2/15/05
    CompanyA 22222 $5.00 1/1/05 1/30/05

    My problem is that the query I wrote (see below) does not work when there are future costs since LastActiveDate > Now. Please help. I am using MS Access 97. Thanks.

    PARAMETERS [Provide a date or select OK for current data] DateTime;
    SELECT [TblCost].Company, [TblCost].SKU, Max([TblCost].[DateActive]) AS LastActiveDate
    FROM [TblCost]
    GROUP BY [TblCost].Company, [TblCost].SKU
    HAVING (((Max([TblCost].[DateActive]))<[Provide a date or select OK for current data] Or (Max([TblCost].[DateActive]))<Now()));

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Hi querious - welcome to the forum.

    I'm not totally sure of your requirement however to get the latest SKU based on the latest date the SKU became active:

    SELECT Company, TblCost.SKU, CurrentCost, DateActive AS LastActiveDate
    FROM TblCost INNER JOIN (SELECT SKU, Max(DateActive) AS LastDate FROM TblCost GROUP BY SKU) AS A ON TblCost.SKU = A.SKU AND TbleCost.DateActive = A.LastDate
    Based on your sample data this would return:

    Company, SKU, CurrentCost, LastActiveDate
    CompanyA 11111 $0.56 2/15/05
    CompanyA 22222 $5.00 1/1/05

    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts