Home » SQL & PL/SQL » SQL & PL/SQL » Year Wise, Month Wise Sum of Qty Report !!
Year Wise, Month Wise Sum of Qty Report !! [message #220130] Mon, 19 February 2007 01:09 Go to next message
devkakkar
Messages: 5
Registered: February 2007
Location: DUBAI
Junior Member
Hi Everyone,

Greetings from DK !!

I have large database of Products range with fast moving and slow moving products. for this products range, I need to generate one report/Query which should return me the data in the following manner:
----------Total Quantity --------------
JAN2006 FEB2006 MAR2006....DEC2006
---------------------------------------
Sr.No. ItemCode
1 F0015366 200 300 400 900
2 F0015366 300 400 500 900
-------------------------------------------------------------

Please help

Regards,

Devendra Kakkar
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220164 is a reply to message #220130] Mon, 19 February 2007 05:14 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
please Send me table structure and required output
Thanks
Srinivas
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220222 is a reply to message #220164] Mon, 19 February 2007 11:14 Go to previous messageGo to next message
devkakkar
Messages: 5
Registered: February 2007
Location: DUBAI
Junior Member
Thanks Sri,

attached excel sheet and table structure for your ref.

Regards,

DK

=====================

INVOICE_HEAD
(
INH_ID NUMBER(12) NOT NULL,
COMP_CODE VARCHAR2(12 BYTE) NOT NULL,
INH_NO NUMBER(10) NOT NULL,
INH_DT DATE NOT NULL,
)


INVOICE_ITEM
(
INI_ID NUMBER(12) NOT NULL,
INI_INH_ID NUMBER(12) NOT NULL,
INI_ITEM_CODE VARCHAR2(12 BYTE),
INI_ITEM_NAME VARCHAR2(2000 BYTE),
INI_UOM_CODE VARCHAR2(12 BYTE) NOT NULL,
INI_QTY NUMBER NOT NULL,
)


ITEM
(
ITEM_CODE VARCHAR2(12 BYTE) NOT NULL,
ITEM_NAME VARCHAR2(2000 BYTE),
ITEM_UOM_CODE VARCHAR2(12 BYTE),
)
  • Attachment: SAMPLE.xls
    (Size: 13.50KB, Downloaded 288 times)

[Updated on: Tue, 20 February 2007 00:55]

Report message to a moderator

Re: Year Wise, Month Wise Sum of Qty Report !! [message #220294 is a reply to message #220222] Tue, 20 February 2007 02:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'll find that you get a better response to your question if you don't stick all the data in attachments, particularly attachments in a proprietory format that has a bad reputation for macro viruses.
If you feel that you have to use an attachment, whats wrong with .txt?
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220326 is a reply to message #220294] Tue, 20 February 2007 05:18 Go to previous messageGo to next message
devkakkar
Messages: 5
Registered: February 2007
Location: DUBAI
Junior Member
Thanks for your suggestion!!

Regards,

DK
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220399 is a reply to message #220130] Tue, 20 February 2007 09:49 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Hi

sql server is not near me but even then i can give you the idea.


Select b.ini_item_code,
sum(decode(to_char(inh_dt, 'mmyyyy'), '012006', ini_qty, 0)) "Jan 06",
sum(decode(to_char(inh_dt, 'mmyyyy'), '022006', ini_qty, 0)) "Feb 06",
sum(decode(to_char(inh_dt, 'mmyyyy'), '032006', ini_qty, 0)) "March 06",
sum(decode(to_char(inh_dt, 'mmyyyy'), '042006', ini_qty, 0)) "April 06",
sum(decode(to_char(inh_dt, 'yyyy'), '2006', ini_qty, 0)) "Year 06"
from invoice_head a, invoice_item b
where a.inh_id=b.ini_ud
group by b.ini_item_code;


i hope you can learn fom the above query and can extract information for remaining 8 months.

N.B.
There is room for improvement in your table structures.

-Dude
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220858 is a reply to message #220399] Thu, 22 February 2007 07:11 Go to previous messageGo to next message
devkakkar
Messages: 5
Registered: February 2007
Location: DUBAI
Junior Member
Thanks Dear

Regards,

DK
Re: Year Wise, Month Wise Sum of Qty Report !! [message #220965 is a reply to message #220130] Thu, 22 February 2007 22:42 Go to previous message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
My pleasure.

By the way you can achieve your objecttives very easily by using Matrix report in Oracle report writer.

Try following SQL while defining matrix report.

Select b.ini_item_code,
to_char(inh_dt, 'mmyyyy'),
sum(ini_qty)
from invoice_head a, invoice_item b
where a.inh_id=b.ini_ud
group by b.ini_item_code, to_char(inh_dt, 'mmyyyy');


Good Luck!
Previous Topic: Get weekday name in PL/SQL
Next Topic: Please Solve query
Goto Forum:
  


Current Time: Fri Dec 09 11:46:26 CST 2016

Total time taken to generate the page: 0.06911 seconds