469,306 Members | 1,896 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Getting the latest row from a batch - ping challenge Aaron

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
Jul 19 '05 #1
16 2123
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" <go****@astraaccounts.co.uk> wrote in message
news:f5**************************@posting.google.c om...
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

Jul 19 '05 #2
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" <aa***@TRASHaspfaq.com> wrote in message
news:eH*************@tk2msftngp13.phx.gbl...
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" <go****@astraaccounts.co.uk> wrote in message
news:f5**************************@posting.google.c om...
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


Jul 19 '05 #3
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.
Jul 19 '05 #4
Yup, Bob nailed the part I missed:
ON a.[Transaction Date] = b.td

AND a.[Stock Code] = b.[Stock Code]
Jul 19 '05 #5
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.
Jul 19 '05 #6
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:

STOCKID REFERENCE
TRANSACTIONDATE QUANTITY
COSTPRICE
---------------------------------------- -----------------------------------
----- --------------------------- ------------------------------------------
----------- -----------------------------------------------------
S1 1
2003-12-08 21:59:30.000 1.0
20.0
S1 NULL
2003-12-08 22:01:01.000 1.0
15.0

I would then need to put in a where clause to only show the transactions
where the TRANSACTIONTYPE = 3 - does this complicate the procedure??

Rgds

Laphan
Bob Barrows <re******@NOyahoo.SPAMcom> wrote in message
news:#S*************@TK2MSFTNGP09.phx.gbl...
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.


Jul 19 '05 #7
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.
Jul 19 '05 #8
> 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/
Jul 19 '05 #9
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
Jul 19 '05 #10
Hi Bob

Many thanks for the lightening fast reply, but could you assist me on what
bits go where.

If I'm going to select it shouldn't the 2nd costprice be in the outer
select?

What's the best way to add the additional table ANDs to synchronise it
properly?

Rgds

Robbie
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:#J**************@TK2MSFTNGP11.phx.gbl...
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.

Jul 19 '05 #11
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.
Jul 19 '05 #12
> 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/
Jul 19 '05 #13
Hi Bob

Sorry to be annoying on this, but I'm really stuck on this and would really
appreciate any help you can give me.

As per life, my deadline to get this done is tomorrow morning and I honestly
don't have a clue.

If you (or anybody else who's reading this) have any idea how to finish this
bit off, I promise I won't bug you again.

Rgds

Laphan

AstrA <no*****@noemail.com> wrote in message news:3f********@127.0.0.1...
Hi Bob

Many thanks for the lightening fast reply, but could you assist me on what
bits go where.

If I'm going to select it shouldn't the 2nd costprice be in the outer
select?

What's the best way to add the additional table ANDs to synchronise it
properly?

Rgds

Robbie
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:#J**************@TK2MSFTNGP11.phx.gbl...
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.



Jul 19 '05 #14
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.


Jul 19 '05 #15
Sorry Bob/Aaron

Got lost in the threads there.

Many, many thanks for creating this for me. This does the trick absolutely
to the tee and is very much appreciated.

Please let your close partners know that you have made at least one
Yorkshire lad very, very happy.

Rgds

Laphan
Aaron Bertrand - MVP <aa***@TRASHaspfaq.com> wrote in message
news:#t**************@TK2MSFTNGP10.phx.gbl...
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/


Jul 19 '05 #16
Sorry Bob

Please see 9-12-03 thread above.

Rgds

Laphan

Bob Barrows <re******@NOyahoo.SPAMcom> wrote in message
news:OD**************@tk2msftngp13.phx.gbl...
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.

Jul 19 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Dr. Mercury | last post: by
15 posts views Thread by middletree | last post: by
2 posts views Thread by David Lozzi | last post: by
2 posts views Thread by Steven Burn | last post: by
13 posts views Thread by Tomasz Jastrzebski | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.