473,839 Members | 1,414 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 2301
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
5162
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
2136
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
1951
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 other UNIX, so I need to ping. Thanks! David Lozzi
2
1298
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 subject. Now, the problem I'm running into is, I'd like to have some sort of spider
2
1848
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. I have a table with information on how an Associate is to be handled under a specific Purchase...
8
10027
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 Internal Server Error'."
3
9478
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 another Exe process with arguments from the same method and that runs just fine too.
13
2365
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 implemented in the base class c1?
3
1554
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
9855
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10586
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7828
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7017
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5682
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5866
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4484
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3134
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.