Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2013
    Posts
    8

    Unanswered: SQL to gain a most recent date.

    Hi Guys,

    I am trying to return the most recent date for the Tranaction date field. in the following code.

    SELECT

    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.Facility,
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.Warehouse,
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Item number],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Stock transaction type],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Order type],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Order number],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Transaction quantity],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Transaction date]

    FROM

    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View

    WHERE

    (((dbo_Fact_StockTransactionsDetailed_Filtered_Div ision_View.Facility)="200") AND ((dbo_Fact_StockTransactionsDetailed_Filtered_Divi sion_View.Warehouse)="B00") AND ((dbo_Fact_StockTransactionsDetailed_Filtered_Divi sion_View.[Transaction date])>=[Enter Start Date dd/mm/yy]));,


    So my thinking was selectmax(Transaction date) instead of the enter start date above.

    Will this work?

  2. #2
    Join Date
    Jun 2013
    Posts
    8
    as an update guys,

    the below query runs but does not return any results, Iam thinking that it is because they will be duplicates of the dates,

    how do i get round this,

    "SELECT
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.Facility,
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.Warehouse,
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Item number],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Stock transaction type],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Order type],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Order number],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Transaction quantity],
    dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View.[Transaction date]

    FROM dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View

    WHERE
    (((dbo_Fact_StockTransactionsDetailed_Filtered_Div ision_View.Facility)="200") AND ((dbo_Fact_StockTransactionsDetailed_Filtered_Divi sion_View.Warehouse)="B00") AND((dbo_Fact_StockTransactionsDetailed_Filtered_D ivision_View.[Transaction date])=(SELECT MAX (dbo_Fact_StockTransactionsDetailed_Filtered_Divis ion_View.[Transaction date]) FROM dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View )))"

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Whenever you use an aggregate function such as MAX, you also need to use the GROUP BY clause, including all unaggregated columns. Also, transaction dates are generally dependant on the item being transacted. Try the following:
    Code:
    SELECT
        m.Facility
    ,   m.Warehouse
    ,   m.[Item Number]
    ,   m.[Stock Transaction Type]
    ,   m.[Order Type]
    ,   m.[Order Number]
    ,   m.[Transaction Quantity]
    ,   m.[Transaction Date]
    FROM
        dbo_Fact_StockTransactionsDetailed_Filtered_Division_View AS m
    INNER JOIN
        (SELECT
            r.[Item Number]
        ,   MAX r.[Transaction Date]
        FROM
            dbo_Fact_StockTransactionsDetailed_Filtered_Division_View AS r
        GROUP BY
            r.[Item Number]) AS s
    ON
        m.[Item Number] = s.[Item Number]
    AND
        m.[Transaction Date] = s.[Transaction Date]
    WHERE
        m.Facility = '200'
    AND
        m.Warehouse = 'B00'
    Last edited by weejas; 06-03-13 at 06:19. Reason: A few typos
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jun 2013
    Posts
    8
    Many thanks,

    Access is having trouble with MAX r.[Transaction Date]

    stating it cannot find it as an object,

    Any work arounds?

    Thanks again

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sorry!
    Code:
    MAX(r.[Transaction Date])
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jun 2013
    Posts
    8
    Thank you very much,

    How can i adapt the coding to allow all entires and not have to enter a date.

    so I can gain all items in B00 and there corresponding last transaction date.

    Many thanks

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    In the code that I've give you, there is no need to enter a date. The subquery s returns all item numbers along with their most recent transaction dates, and uses this information to return only the transaction information from the main view that matches.

    If this is not what you require, please post the following:
    • Some sample data, including records to be output and records to be ignored
    • What you're seeing at the moment from the provided query
    • What you actually want to see from the provided query
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Jun 2013
    Posts
    8
    Thank you very much for your response's,

    I am finding the query has returned the attached,

    I would like the query to ignore any stock transaction type that is not 11,31,41,51 all the rest are not required.

    Also i would like it to return a single line per item number showing the most recent transaction date.

    Many thanks

    Much appreciated.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so add to your where clause an IN sub clause
    eg
    and transactiontype in (11,31,41,51)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I find it very hard to believe that the query as posted would return multiple dates for a single item number, on the grounds that the join statement is restricted to a single date per item number.

    You have now changed the requirements - none of your previous posts made any mention of transaction types. Also, this statement:
    I would like the query to ignore any stock transaction type that is not 11,31,41,51 all the rest are not required.
    is unclear. Are you saying that you don't want to see any transaction types, or only records where the transaction type is blank?

    In case my previous post was unclear, the following is what would be helpful in solving your request:
    • Some sample data, including records to be output and records to be ignored
      By this, I mean the current output of dbo_Fact_StockTransactionsDetailed_Filtered_Divisi on_View. Not necessarily the whole output, but certainly some records that should be included in your desired output and some that should be ignored.
    • What you're seeing at the moment from the provided query
      By this I mean the effect of running the provided code on the sample data. This will help me to see if the query is performing as I expect it to, and so will highlight flaws in my assumptions.
    • What you actually want to see from the provided query
      By this I mean the records from the sample data that you want to see in the final output.

    Without this information, I'm fumbling in the dark and being no help to either of us.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Jun 2013
    Posts
    8
    Apologies,

    the attached shows the issue,

    yellow denotes the lines that I need including ( the most recent transaction for that item)

    red lines are not to be included as they are earlier than the most recent date,

    White lines are lines that do not match the required stock transaction type.( th only stock transaction type that are to be included are 11, 31,41,51)


    I hope this make sense.

    Thank you so much
    Attached Files Attached Files

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right, that's actually more straight-forward than I thought.
    Code:
    SELECT
        a.Facility
    ,   a.Warehouse
    ,   a.[Item Number]
    ,   a.[Stock Transaction Type]
    ,   a.[Order Type]
    ,   a.[Transaction Quantity]
    ,   MAX(a.[Transaction Date]) AS [Transaction Date]
    FROM
        dbo_Fact_StockTransactionsDetailed_Filtered_Division_View AS a
    WHERE
        a.Facility = '200'
    AND
        a.Warehouse = 'B00'
    AND
        a.[Stock Transaction Type] IN (11,31,41,51)
    GROUP BY
        a.Facility
    ,   a.Warehouse
    ,   a.[Item Number]
    ,   a.[Stock Transaction Type]
    ,   a.[Order Type]
    ,   a.[Transaction Quantity]
    This assumes that Stock Transaction Type is numeric, which is implied by the format of the Excel sheet that you showed. If it's actually text, replace that line with
    Code:
    a.[Stock Transaction Type] IN ('11','31','41','51')
    That should work - if it doesn't, it should at least get you started.
    Last edited by weejas; 06-03-13 at 09:45. Reason: Mistyped a code tag
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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