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).
