Your query returns the maximum quantity (and associated date) for a single
ICTRANS entry. If there are multiple entries per day then the logic is a
lot more complex - but certainly achievable. Since we don't know how data
is stored in the table though the query below MAY be valid.
"Chelur, Jayadas
{PBSG}" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
epsi.com> Subject: RE: How to put a TOP 1 in a
select
Sent by:
[EMAIL PROTECTED]
.com
11/06/2003 04:25
Please respond to
ORACLE-L
This query would give you the total quantity sold in the
past two weeks and the date on which maximum number was
sold, for each item ...
SELECT a.item,
a.tras_date AS max_sale_date,
b.tot_qty AS tot_sale_qty
FROM ICTRANS a,
(
SELECT item, SUM(qty) tot_qty, MAX(qty) max_qty
FROM ICTRANS
WHERE company = 2000
AND trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE
AND doc_type = 'IS'
AND reason_code = 'VTCL'
AND SUBSTR(item,2,2) = '57'
GROUP BY item
) b
WHERE a.item = b.item
AND a.qty = b.max_qty
AND trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE;
-----Original Message-----
Sent: Tuesday, June 10, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L
Thanks!!
First let me explain more about the query I have a table ICTRANS that have
all the inventary movements.
I need to made a query that give each item from the guide 57, the
information that I need is how much of each item we sell in the last two
week ( from today), and what was the day that we sell more.
The way I can know if it was a sell is because this condition must be true
:reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that
way the field Quantity have quantity with a negative sign ( ej: -30). If
substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent
medicine), company=2000 means that are from the same company we have one
company per city.
This is what I have on mind in :
select ITEM ,
sum(QUANTITY),
( select top 1 t2.TRANS_DATE
from ICTRANS t2 where t2.ITEM = t.ITEM
where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate
and DOC_TYPE = 'IS'
and REASON_CODE = 'VTCL'
and substr(ITEM, 2,2) = '57'
order by t2.QUANTITY desc
)
from ICTRANS t
where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate
and DOC_TYPE = 'IS'
and REASON_CODE = 'VTCL'
and substr(ITEM, 2,2) = '57'
group by ITEM
Thanks for your help I feel lost using Oracle.
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.
This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chelur, Jayadas {PBSG}
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
