Getting the latest row from a batch - ping challenge Aaron

[Robbie]

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]

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 - MVP]

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/

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/

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

 

[Bob Barrows]

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

 

[Aaron Bertrand - MVP]

Yup, Bob nailed the part I missed:

ON a.[Transaction Date] = b.td

AND a.[Stock Code] = b.[Stock Code]

 

[Bob Barrows]

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

 

[Aaron Bertrand - MVP]

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.)

 

[Bob Barrows]

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

 

[Robbie]

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

 

[Bob Barrows]

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

 

[Aaron Bertrand - MVP]

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.

 

[Bob Barrows]

I already replied. Here it is again:

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