473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Max qty with date?

hi all,

I am really do not know how to get this. Tried all types of queries.
The requirement is as below

itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004
002 2 5/10/2004

The actual table has all transaction data as below.
itemid qty date
001 5 5/1/2004
001 3 5/2/2004
001 3 5/3/2004
001 2 5/4/2004
002 0 5/1/2004
......
002 2 5/10/2004

one query is
select itemid,max(qty) from mTran

with the above require which date it occurred.

Please help

Cap2010
Nov 13 '05 #1
2 1284
Try this.....theres a good chance its horribly wrong though :)

select itemId, dateStamp, nz(select max(qty) from mtrans as m1 where
m1.itemId=m2.itemId group by itemId),0)
from mtrans as m2

John
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
hi all,

I am really do not know how to get this. Tried all types of queries.
The requirement is as below

itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004
002 2 5/10/2004

The actual table has all transaction data as below.
itemid qty date
001 5 5/1/2004
001 3 5/2/2004
001 3 5/3/2004
001 2 5/4/2004
002 0 5/1/2004
.....
002 2 5/10/2004

one query is
select itemid,max(qty) from mTran

with the above require which date it occurred.

Please help

Cap2010

Nov 13 '05 #2
ca**********@yahoo.com (captain) wrote in message news:<27**************************@posting.google. com>...
hi all,

I am really do not know how to get this. Tried all types of queries.
The requirement is as below

itemid maxqty Mdate (date is when the max qty went out)
001 5 5/1/2004
002 2 5/10/2004

The actual table has all transaction data as below.
itemid qty date
001 5 5/1/2004
001 3 5/2/2004
001 3 5/3/2004
001 2 5/4/2004
002 0 5/1/2004
.....
002 2 5/10/2004

one query is
select itemid,max(qty) from mTran

with the above require which date it occurred.

Please help

Cap2010


Try:

SELECT itemid, qty AS maxqty, DateStamp AS Mdate FROM mtrans WHERE qty
IN (SELECT Max(qty) AS MaxQty from mtrans AS mt where mt.itemid =
mtrans.itemid);

Of course, only use this query if the other poster's answer is
horribly wrong :-).

James A. Fortune
Nov 13 '05 #3

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

Similar topics

2
by: androtech | last post by:
Hello, I'm looking for a function that returns a date range for a specified week number of the year. I'm not able to find functions like this anywhere. Any pointers/help would be much...
4
by: Richard Hollenbeck | last post by:
I'm trying to write some code that will convert any of the most popular standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan 1908) and compare the first with the second and...
2
by: Tjerk | last post by:
Hello all, I have the script below to change an image depending on the date upto january it worked fine but then it just stopped working does anybody have an idea how I can make it work again or...
9
by: Thomas R. Hummel | last post by:
Hello, I am importing data that lists rates for particular coverages for a particular period of time. Unfortunately, the data source isn't very clean. I've come up with some rules that I think...
30
by: Dr John Stockton | last post by:
It has appeared that ancient sources give a method for Numeric Date Validation that involves numerous tests to determine month length; versions are often posted by incomers here. That sort of code...
3
by: captain | last post by:
Below is the sql for data with same date need to extract + or - 5 days data of same date also. How to also get data of + and - days related to same date. SELECT IM.Area, IM.Location,...
1
by: Liz Malcolm | last post by:
Hello and TIA. I have a DE form with an option group that if daily is selected todays date is used for start and end date, if weekly is selected Monday - Friday is used. I am trying to add a...
7
by: James P. | last post by:
Hello there, In my asp.net page using VB, I have a date text field in mm/dd/yyyy format. When a date is entered, I'd like to validate it to make sure the date is greater than or equal to the...
12
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as...
3
by: JJ | last post by:
Here's the code. $link="http://xbox360cheat.org"; $close_date=$_POST; #last content change check if ($close_date == 0) $close_date = date("Y-m-d H:m:s", mktime(12, 0, 0, date("m"), date...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.