473,573 Members | 3,111 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2285
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****@astraac counts.co.uk> wrote in message
news:f5******** *************** ***@posting.goo gle.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

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***@TRASHasp faq.com> wrote in message
news:eH******** *****@tk2msftng p13.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****@astraac counts.co.uk> wrote in message
news:f5******** *************** ***@posting.goo gle.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


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 LastTransaction Date
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 LastTransaction Date
FROM table
GROUP BY [Stock Code]) qLast
ON t.[Stock Code] = qLast.[Stock Code]
AND t.[Transaction Date] = qLast.LastTrans actionDate
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 LastTransaction Date
FROM table
GROUP BY [Stock Code]) qLast
ON t.[Stock Code] = qLast.[Stock Code]
AND t.[Transaction Date] = qLast.LastTrans actionDate
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 LastTransaction Date
FROM table
GROUP BY [Stock Code]) qLast
ON t.[Stock Code] = qLast.[Stock Code]
AND t.[Transaction Date] = qLast.LastTrans actionDate
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******@NOyah oo.SPAMcom> wrote in message
news:#S******** *****@TK2MSFTNG P09.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 LastTransaction Date
FROM table
GROUP BY [Stock Code]) qLast
ON t.[Stock Code] = qLast.[Stock Code]
AND t.[Transaction Date] = qLast.LastTrans actionDate
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 LastTransaction Date
FROM table
GROUP BY [Stock Code]) qLast
ON t.[Stock Code] = qLast.[Stock Code]
AND t.[Transaction Date] = qLast.LastTrans actionDate
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.TRANSACTIOND ATE AS 'Transaction
Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
FROM STOCKTRANSACTIO NS AS S1
JOIN
(SELECT STOCKID, MAX(TRANSACTION DATE)
FROM STOCKTRANSACTIO NS
WHERE TRANSACTIONTYPE = 3
GROUP BY STOCKID)
AS S2(STOCKID, TRANSACTIONDATE )
ON S1.STOCKID = S2.STOCKID
AND S1.TRANSACTIOND ATE = S2.TRANSACTIOND ATE
GROUP BY S1.STOCKID, S1.TRANSACTIOND ATE, 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
STOCKTRANSACTIO NS 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 STOCKTRANSACTIO NS.STOCKID, STOCKTRANSACTIO NS.TRANSACTIOND ATE,
STOCKTRANSACTIO NS.QUANTITY, STOCKTRANSACTIO NS.CURRENCYID,
STOCKTRANSACTIO NS.COSTPRICE, STOCKPRICES.COS TPRICE
FROM STOCKPRICES, STOCKTRANSACTIO NS
WHERE STOCKTRANSACTIO NS.CURRENCYID = STOCKPRICES.CUR RENCYID AND
STOCKTRANSACTIO NS.STOCKID = STOCKPRICES.STO CKID AND
((STOCKTRANSACT IONS.TRANSACTIO NTYPE=3) AND
(STOCKPRICES.PR ICELEVELID='1') )

Any ideas on how to sync these 2 queries??

Rgds

Laphan
Jul 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
5141
by: Dr. Mercury | last post by:
Greetings to all - Someone suggested that Java might be the answer for a little conversion program I need. It's pretty simple, really. Just take a column of numbers: 139 259 433 637
15
2116
by: middletree | last post by:
How do I request the actual page name that I'm on? This is going to go into an include file, and depending on which page I'm on, I'd like to do different things. I'm looking for "pagename.asp"
2
1939
by: David Lozzi | last post by:
I need to determine from ASP if the database server is up and running, and if it isn't ping another database server. I posted this before about a different client, and I was told to request a file from the IIS server, and if I cannot retrieve it, the server is not there. I cannot use this method due to one database box being Solaris and the...
2
1292
by: Steven Burn | last post by:
Aaron, Apologies for the ping but, your the only one I can think of offhand that will know what I'm looking for. Basically, to cut a long story short, I run an sURL service (surl.co.uk) that allows one to convert long URL's to short etc, and am looking to have a search facility on it that will allow one to locate an sURL for a specific...
2
1838
by: John Baker | last post by:
Hi: I have used this group a number of times simply because while the manuals for Access may show the technology, but most are really weak on how to apply it in special situations and the indexes are less that complete!. This is also the first real application iI have developed for Access, although I have done a number in Lotus Approach. ...
8
9995
by: Rod | last post by:
I have been working with ASP.NET 1.1 for quite a while now. For some reason, opening some ASP.NET applications we wrote is producing the following error message: "The Web server reported the following error when attempting to create or open the Web project located at the following URL: 'http://localhost/WebApplication1'. 'HTTP/1.1 500...
3
9426
by: Zeya | last post by:
I have created a very simple batch file and trying to retrieve the standard output but everytime I run the code it returns ExitCode as 1. I have created a batch file as simple as ping localhost or echo hello world and neither have worked. Note: This is from ASP.Net code. Also, the batch file runs just fine from command line. I am running...
13
2351
by: Tomasz Jastrzebski | last post by:
Hello, Below there are two equivalent(?) pieces of C# and VB.Net code. While the C# version compiles with no warning, the VB.Net version does not compile due to the compiler error BC30149: Class 'c2' must implement 'Sub m1()' for interface 'i1'. Does it mean that in VB interface must be implemented, even if it is already non-explicitly...
3
1548
by: patelxxx | last post by:
PERL Code to Ping a server: #!c:/Perl/bin/perl.exe use Net::Ping; use strict; use warnings; my $host = 192.168.0.1; # Real value removed by MODERATOR
0
7687
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8017
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8201
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8063
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6413
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5290
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1039
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.