Getting the latest row from a batch - ping challenge Aaron

Discussion in 'ASP General' started by Robbie, Dec 8, 2003.

  1. Robbie

    Robbie Guest

    Hi All

    This is a belter that my little brain can't handle.

    Basically I have 1 SQL table that contains the following fields:

    Stock Code
    Stock Desc
    Reference
    Transaction Date
    Qty
    Cost Price

    Basically this table stores all the transaction lines of when a user
    books stock items into stock so that they can look at a journal of
    this goods in as and when they please.

    My task is that the user wants a list of all the stock items with the
    last cost price that they were booked in at.

    So I think I have to find the last transaction date used for each
    stock code and then bring this back as 1 row per stock code with the
    above fields of data.

    How the whats-its can I do this? Is it acutally possible?

    Any help you can give is much appreciated.

    Rgds

    Laphan
     
    Robbie, Dec 8, 2003
    #1
    1. Advertising

  2. Assuming SQL Server, you can use a correlated subquery.

    SELECT
    a.[Stock Code],
    ... a.other columns ...
    FROM
    [1 SQL Table] a
    INNER JOIN
    (
    SELECT [Stock Code], td = MAX([Transaction Date])
    FROM [1 SQL Table]
    GROUP BY [Stock Code]
    ) b
    ON a.[Transaction Date] = b.td

    Please name your columns correctly! Spaces are not very acceptable
    characters in object names...

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Robbie" <> wrote in message
    news:...
    > Hi All
    >
    > This is a belter that my little brain can't handle.
    >
    > Basically I have 1 SQL table that contains the following fields:
    >
    > Stock Code
    > Stock Desc
    > Reference
    > Transaction Date
    > Qty
    > Cost Price
    >
    > Basically this table stores all the transaction lines of when a user
    > books stock items into stock so that they can look at a journal of
    > this goods in as and when they please.
    >
    > My task is that the user wants a list of all the stock items with the
    > last cost price that they were booked in at.
    >
    > So I think I have to find the last transaction date used for each
    > stock code and then bring this back as 1 row per stock code with the
    > above fields of data.
    >
    > How the whats-its can I do this? Is it acutally possible?
    >
    > Any help you can give is much appreciated.
    >
    > Rgds
    >
    > Laphan
     
    Aaron Bertrand - MVP, Dec 8, 2003
    #2
    1. Advertising

  3. Note that this might return multiple rows for a single [Stock Code] if it
    had multiple transactions with an identical [Transaction Date]. You weren't
    clear on the precision of [Transaction Date] column (e.g. is time stored, is
    it a smalldatetime (so only to the minute), is it rounded to the hour...),
    so I'm not sure how likely that would be.

    In the future, please be sure to include the database platform and version
    you are using... this can sometimes change the approach significantly.

    And PLEASE post to one, on-topic group, instead of posting the same message
    independently to every ASP- or SQL-related newsgroup you can find. To see
    why this is so annoying, please read http://www.aspfaq.com/5003

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Aaron Bertrand - MVP" <> wrote in message
    news:...
    > Assuming SQL Server, you can use a correlated subquery.
    >
    > SELECT
    > a.[Stock Code],
    > ... a.other columns ...
    > FROM
    > [1 SQL Table] a
    > INNER JOIN
    > (
    > SELECT [Stock Code], td = MAX([Transaction Date])
    > FROM [1 SQL Table]
    > GROUP BY [Stock Code]
    > ) b
    > ON a.[Transaction Date] = b.td
    >
    > Please name your columns correctly! Spaces are not very acceptable
    > characters in object names...
    >
    > --
    > Aaron Bertrand
    > SQL Server MVP
    > http://www.aspfaq.com/
    >
    >
    >
    >
    > "Robbie" <> wrote in message
    > news:...
    > > Hi All
    > >
    > > This is a belter that my little brain can't handle.
    > >
    > > Basically I have 1 SQL table that contains the following fields:
    > >
    > > Stock Code
    > > Stock Desc
    > > Reference
    > > Transaction Date
    > > Qty
    > > Cost Price
    > >
    > > Basically this table stores all the transaction lines of when a user
    > > books stock items into stock so that they can look at a journal of
    > > this goods in as and when they please.
    > >
    > > My task is that the user wants a list of all the stock items with the
    > > last cost price that they were booked in at.
    > >
    > > So I think I have to find the last transaction date used for each
    > > stock code and then bring this back as 1 row per stock code with the
    > > above fields of data.
    > >
    > > How the whats-its can I do this? Is it acutally possible?
    > >
    > > Any help you can give is much appreciated.
    > >
    > > Rgds
    > >
    > > Laphan

    >
    >
     
    Aaron Bertrand - MVP, Dec 8, 2003
    #3
  4. Robbie

    Bob Barrows Guest

    Robbie wrote:
    > Hi All
    >
    > This is a belter that my little brain can't handle.
    >
    > Basically I have 1 SQL table that contains the following fields:
    >
    > Stock Code
    > Stock Desc
    > Reference
    > Transaction Date
    > Qty
    > Cost Price
    >
    > Basically this table stores all the transaction lines of when a user
    > books stock items into stock so that they can look at a journal of
    > this goods in as and when they please.
    >
    > My task is that the user wants a list of all the stock items with the
    > last cost price that they were booked in at.
    >
    > So I think I have to find the last transaction date used for each
    > stock code and then bring this back as 1 row per stock code with the
    > above fields of data.
    >
    > How the whats-its can I do this? Is it acutally possible?
    >
    > Any help you can give is much appreciated.
    >
    > Rgds
    >
    > Laphan


    This query gives you a list of all the Stock Codes and their max transaction
    dates:

    Select [Stock Code], max([Transaction Date]) AS LastTransactionDate
    FROM table
    GROUP BY [Stock Code]


    You can use this query to create a virtual table to which you can join the
    original table, like this:

    SELECT
    [Stock Code],
    [Stock Desc],
    Reference,
    [Transaction Date],
    Qty,
    [Cost Price]
    FROM Table t INNER JOIN
    (Select [Stock Code], max([Transaction Date]) AS LastTransactionDate
    FROM table
    GROUP BY [Stock Code]) qLast
    ON t.[Stock Code] = qLast.[Stock Code]
    AND t.[Transaction Date] = qLast.LastTransactionDate
    ORDER BY [Stock Code]

    The only potential problem is if there are ties: multiple records for a
    Stock Code with exactly the same Transaction Date. Let us know if this is a
    possible problem.


    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 8, 2003
    #4
  5. Yup, Bob nailed the part I missed:

    > ON a.[Transaction Date] = b.td

    AND a.[Stock Code] = b.[Stock Code]
     
    Aaron Bertrand - MVP, Dec 8, 2003
    #5
  6. Robbie

    Bob Barrows Guest

    Bob Barrows wrote:
    > SELECT
    > [Stock Code],
    > [Stock Desc],
    > Reference,
    > [Transaction Date],
    > Qty,
    > [Cost Price]
    > FROM Table t INNER JOIN
    > (Select [Stock Code], max([Transaction Date]) AS LastTransactionDate
    > FROM table
    > GROUP BY [Stock Code]) qLast
    > ON t.[Stock Code] = qLast.[Stock Code]
    > AND t.[Transaction Date] = qLast.LastTransactionDate
    > ORDER BY [Stock Code]
    >

    Darn! I forgot to qualify the [Stock Code] column references. The query
    should look like this:

    SELECT
    t.[Stock Code],
    [Stock Desc],
    Reference,
    [Transaction Date],
    Qty,
    [Cost Price]
    FROM Table t INNER JOIN
    (Select [Stock Code], max([Transaction Date]) AS LastTransactionDate
    FROM table
    GROUP BY [Stock Code]) qLast
    ON t.[Stock Code] = qLast.[Stock Code]
    AND t.[Transaction Date] = qLast.LastTransactionDate
    ORDER BY t.[Stock Code]

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 8, 2003
    #6
  7. > I would then need to put in a where clause to only show the transactions
    > where the TRANSACTIONTYPE = 3 - does this complicate the procedure??


    You would probably only need to do this as a WHERE clause on the subquery.
    Did you try it?

    And "complicate" is a pretty subjective term. Do you mean add more code?
    Certainly, though negligible. Does it make it less efficient? Depends on
    too many things... how selective is the transactiontype column (e.g. what
    percentage has a value of 3), what is its datatype, is there an index on
    that column, etc.)

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand - MVP, Dec 8, 2003
    #7
  8. Robbie

    Bob Barrows Guest

    Laphan wrote:
    > Dear Aaron/Bob
    >
    > Many, many thanks for your assistance.
    >
    > Sorry for the panic post.
    >
    > JFYR, a typical couple of data rows in this SQL DB would be as
    > follows:
    >
    >
    > I would then need to put in a where clause to only show the
    > transactions where the TRANSACTIONTYPE = 3 - does this complicate the
    > procedure??
    >

    No. Simply add the WHERE clause to the query. I would add it to the subquery
    so the number of records that need to be grouped will be limited, thereby
    improving the performance. It does not need to be added to the outer query.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 8, 2003
    #8
  9. Robbie

    Robbie Guest

    Many thanks for the help guys. The below script works perfectly apart
    from the fact that I need to add a cost price field from another
    table:

    SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
    Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
    FROM STOCKTRANSACTIONS AS S1
    JOIN
    (SELECT STOCKID, MAX(TRANSACTIONDATE)
    FROM STOCKTRANSACTIONS
    WHERE TRANSACTIONTYPE = 3
    GROUP BY STOCKID)
    AS S2(STOCKID, TRANSACTIONDATE)
    ON S1.STOCKID = S2.STOCKID
    AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
    GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

    Could you please let me know how I can add an additional COSTPRICE
    column from a STOCKPRICES table to this script.

    FYR, if I was to perform a straightforward query to join the
    STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
    would look to get the required data, but this wouldn't contain the new
    fangled 'find last date' thing that you sent me:

    SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
    STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
    STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
    FROM STOCKPRICES, STOCKTRANSACTIONS
    WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
    STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
    ((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
    (STOCKPRICES.PRICELEVELID='1'))

    Any ideas on how to sync these 2 queries??

    Rgds

    Laphan
     
    Robbie, Dec 9, 2003
    #9
  10. Robbie

    Bob Barrows Guest

    Robbie wrote:
    > Many thanks for the help guys. The below script works perfectly apart
    > from the fact that I need to add a cost price field from another
    > table:
    >
    > SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
    > Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
    > FROM STOCKTRANSACTIONS AS S1
    > JOIN
    > (SELECT STOCKID, MAX(TRANSACTIONDATE)
    > FROM STOCKTRANSACTIONS
    > WHERE TRANSACTIONTYPE = 3
    > GROUP BY STOCKID)
    > AS S2(STOCKID, TRANSACTIONDATE)
    > ON S1.STOCKID = S2.STOCKID
    > AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
    > GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE
    >
    > Could you please let me know how I can add an additional COSTPRICE
    > column from a STOCKPRICES table to this script.
    >
    > FYR, if I was to perform a straightforward query to join the
    > STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
    > would look to get the required data, but this wouldn't contain the new
    > fangled 'find last date' thing that you sent me:
    >
    > SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
    > STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
    > STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
    > FROM STOCKPRICES, STOCKTRANSACTIONS
    > WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
    > STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
    > ((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
    > (STOCKPRICES.PRICELEVELID='1'))
    >

    Aargh! How can you stand to even look at that thing? :)
    Use table aliases so you don't have to repeat "STOCKTRANSACTIONS"
    everywhere.

    And you should really learn how to use ANSI-style joins.

    Anyways, simply add a join to STOCKPRICES in the first query, like this:

    SELECT
    S1.STOCKID AS [Stock Code],
    S1.TRANSACTIONDATE AS [Transaction Date],
    S1.QUANTITY AS Quantity,
    S1.COSTPRICE AS [Cost Price],
    S3.COSTPRICE As [Standard Cost Price?]
    FROM
    STOCKTRANSACTIONS AS S1
    JOIN
    (SELECT STOCKID, MAX(TRANSACTIONDATE) AS LastTransactionDate
    FROM STOCKTRANSACTIONS
    WHERE TRANSACTIONTYPE = 3
    GROUP BY STOCKID) AS S2
    ON S1.STOCKID = S2.STOCKID
    AND S1.TRANSACTIONDATE = S2.LastTransactionDate
    JOIN
    STOCKPRICES S3
    ON S1.CURRENCYID = S3.CURRENCYID AND
    S1.STOCKID = S3.STOCKID
    WHERE S3.PRICELEVELID='1'



    GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

    Why are you grouping? I don't see any aggregate functions that require a
    GROUP BY clause ...

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 9, 2003
    #10
  11. > Why are you grouping? I don't see any aggregate functions that require a
    > GROUP BY clause ...


    I'll take a guess... no primary key, and this is the hack to avoid
    duplicates. :)

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/
     
    Aaron Bertrand - MVP, Dec 9, 2003
    #11
  12. Robbie

    Bob Barrows Guest

    I already replied. Here it is again:
    Robbie wrote:
    > Many thanks for the help guys. The below script works perfectly apart
    > from the fact that I need to add a cost price field from another
    > table:
    >
    > SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
    > Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
    > FROM STOCKTRANSACTIONS AS S1
    > JOIN
    > (SELECT STOCKID, MAX(TRANSACTIONDATE)
    > FROM STOCKTRANSACTIONS
    > WHERE TRANSACTIONTYPE = 3
    > GROUP BY STOCKID)
    > AS S2(STOCKID, TRANSACTIONDATE)
    > ON S1.STOCKID = S2.STOCKID
    > AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
    > GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE
    >
    > Could you please let me know how I can add an additional COSTPRICE
    > column from a STOCKPRICES table to this script.
    >
    > FYR, if I was to perform a straightforward query to join the
    > STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
    > would look to get the required data, but this wouldn't contain the new
    > fangled 'find last date' thing that you sent me:
    >
    > SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
    > STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
    > STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
    > FROM STOCKPRICES, STOCKTRANSACTIONS
    > WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
    > STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
    > ((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
    > (STOCKPRICES.PRICELEVELID='1'))
    >

    Aargh! How can you stand to even look at that thing?
    Use table aliases so you don't have to repeat "STOCKTRANSACTIONS"
    everywhere.

    And you should really learn how to use ANSI-style joins.

    Anyways, simply add a join to STOCKPRICES in the first query, like this:

    SELECT
    S1.STOCKID AS [Stock Code],
    S1.TRANSACTIONDATE AS [Transaction Date],
    S1.QUANTITY AS Quantity,
    S1.COSTPRICE AS [Cost Price],
    S3.COSTPRICE As [Standard Cost Price?]
    FROM
    STOCKTRANSACTIONS AS S1
    JOIN
    (SELECT STOCKID, MAX(TRANSACTIONDATE) AS LastTransactionDate
    FROM STOCKTRANSACTIONS
    WHERE TRANSACTIONTYPE = 3
    GROUP BY STOCKID) AS S2
    ON S1.STOCKID = S2.STOCKID
    AND S1.TRANSACTIONDATE = S2.LastTransactionDate
    JOIN
    STOCKPRICES S3
    ON S1.CURRENCYID = S3.CURRENCYID AND
    S1.STOCKID = S3.STOCKID
    WHERE S3.PRICELEVELID='1'



    GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

    Why are you grouping? I don't see any aggregate functions that require a
    GROUP BY clause ...

    HTH,
    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Dec 9, 2003
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. abhi
    Replies:
    0
    Views:
    240
  2. Frankfurt Rank

    Aaron Bertrand - MVP Vs. Ray at <%=sLocation%>

    Frankfurt Rank, Oct 29, 2003, in forum: ASP General
    Replies:
    2
    Views:
    125
    Jeff Cochran
    Oct 30, 2003
  3. middletree

    Ping Aaron: Site down?

    middletree, Dec 23, 2003, in forum: ASP General
    Replies:
    3
    Views:
    124
    Roland Hall
    Dec 24, 2003
  4. Steven Burn

    Ping Aaron - [Rel: Spiders]

    Steven Burn, Jun 7, 2004, in forum: ASP General
    Replies:
    2
    Views:
    124
    Steven Burn
    Jun 7, 2004
  5. Dan
    Replies:
    4
    Views:
    210
    Aaron [SQL Server MVP]
    Jul 2, 2004
Loading...

Share This Page