Home » SQL & PL/SQL » SQL & PL/SQL » breaking column opening and production  () 1 Vote
breaking column opening and production [message #289459] Fri, 21 December 2007 21:55 Go to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear all,
i have a table stk in which i have item,price,opening and production column like
item       price  opening  production
doll        500      100          300


now i have an another table sale in which i have
bill_no,item,price,qty.since we give discount, so we have different price against same item like
bill_no item price qty
1       doll 300   75
2       doll 400   125
3       doll 300   50  
4       doll 400   75  
5       doll 300   25
6       doll 500   25

Now i want a query which has result like this to show opening and production against all prices and remaining opening and production must show always against stk price.
item price opening production sale closing
doll 500    25        25      25    25
doll 400    0         200     200   0 
doll 300    75        75      150   0

      total 100       300     375   25 

regards,
kaushal

[EDITED by LF: added [code] tags to improve readability]

[Updated on: Sat, 22 December 2007 11:30] by Moderator

Report message to a moderator

Re: breaking column opening and production [message #289463 is a reply to message #289459] Fri, 21 December 2007 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read the OraFAQ Forum Guide (above) before posting.
Re: breaking column opening and production [message #289467 is a reply to message #289459] Sat, 22 December 2007 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To precisely Ana's answer:
1/ Format your post, use code tags and preview button
2/ Give your Oracle version
3/ Post a test case (create table and insert statements)
4/ Give the result for your test case
5/ Explain why this result that is the algorithm/formula to get the result from the data. What is the meaning of "opening", "production", "sale", "closing"?

Regards
Michel
Re: breaking column opening and production [message #289500 is a reply to message #289467] Sat, 22 December 2007 13:55 Go to previous messageGo to next message
0551373
Messages: 6
Registered: December 2007
Junior Member
were are the fields closing and sales coming from? are they computational values, extracted from tables and existing data?
can you clarify these small issues?.
Shocked .

Re: breaking column opening and production [message #289507 is a reply to message #289459] Sun, 23 December 2007 00:03 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
item       price  opening  production
doll        500      100          300


item price opening production sale closing
doll 500    25        25      25    25
doll 400    0         200     200   0 
doll 300    75        75      150   0

      total 100       300     375   25 


Hi
How you have splitted the opening stock?

In the begining, you mentioned 100 doll at the price of 500
and later on you mentioned 25 dolls at doll priced at 500 and 75 dolls priced at 300 (for opening stock). This bit confusing for me.

Further you forgot to mention aboput production table.

Present your table structure for further clearification.
Re: breaking column opening and production [message #289539 is a reply to message #289459] Sun, 23 December 2007 21:49 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear all,

first of all thanks for your response.

As you all are inconvient against algo i have some more clarification.

1.closing means previous month remaining stock and is equal to opening+production-sale.
2.opening means pevious month closing.
3.productuion means this month production.

since in stk table there is only one price while selling, this particular item comes many times in sale table with dufference prices as we give discounts.

simply joining stk and sale will give opening and production against only one unit price 500 because it is only in stk table.but it causes to look report wrong because in other prices there is also sale quantity but there is no opening and production.

now i want to show opening and production in all row by splitting stk opening and production.for this we have to put sale qty in production or opening or in both depending upon stk quantity means if there is enough quantity in production then their is no need to split opening or if there is enough quantity in opening then their is no need to split production other wise you have to split both.remaining stk quantity must always comes
with 500 price.

reply if you need more aurgments.

regards,
kaushal
Re: breaking column opening and production [message #289556 is a reply to message #289539] Mon, 24 December 2007 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

closing means previous month remaining stock and is equal to opening+production-sale.

Month? There is no date in your data.

Quote:

now i want to show opening and production in all row by splitting stk opening and production

What is exactly the rule?
Why, in your example opening is split 25/0/75 and production 25/200/75?
How closing can be 25/0/0? Does not closing independent of price?

Still have neither Oracle version nor a test case.

Regards
Michel
Re: breaking column opening and production [message #289581 is a reply to message #289459] Mon, 24 December 2007 03:00 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear Michel,

please devlop query to split column using attached data structure.

Thanks and regards,
Kaushal
Re: breaking column opening and production [message #289585 is a reply to message #289581] Mon, 24 December 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't read doc file, this is explained in OraFAQ Forum Guide.
It is worth to read it.
Repost with txt file.

Regards
Michel
Re: breaking column opening and production [message #289588 is a reply to message #289459] Mon, 24 December 2007 03:16 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
please find the text file attachmnet.

regards,
kaushal
Re: breaking column opening and production [message #289649 is a reply to message #289588] Mon, 24 December 2007 16:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Your exact requirements are a bit unclear and some of your examples seem to contradict what you say, such as the opening not matching the preceding closing. Below I have provided various things, including the create and insert statements that you should have provided, a typical accounting method by date, something similar by price, and lastly the closest I could get to my best guess as to what you want, splitting production by allocating only the minimum required to match the sales.


-- create table and insert statements that you should have provided as per the forum guide:
SCOTT@orcl_11g> CREATE TABLE stk
  2    (item	    VARCHAR2 (20),
  3  	price	    NUMBER   (10, 2),
  4  	month	    NUMBER   ( 2),
  5  	year	    NUMBER   ( 4),
  6  	opening     NUMBER   (10),
  7  	production  NUMBER   (10),
  8  	sale	    NUMBER   (20))
  9  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO stk VALUES ('Doll', 500, 12, 2007, 100, 300, 375)
  3  INTO stk VALUES ('Doll', 500, 11, 2007,   0, 100,	 0)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE TABLE sale
  2    (bill_no     NUMBER   ( 5),
  3  	bill_date   DATE,
  4  	item	    VARCHAR2 (20),
  5  	price	    NUMBER   (10),
  6  	qty	    NUMBER   (10))
  7  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO sale VALUES (1, TO_DATE ('10-DEC-2007', 'DD-MON-YYYY'), 'Doll', 300, 75)
  3  INTO sale VALUES (2, TO_DATE ('15-DEC-2007', 'DD-MON-YYYY'), 'Doll', 400, 125)
  4  INTO sale VALUES (3, TO_DATE ('17-DEC-2007', 'DD-MON-YYYY'), 'Doll', 300, 50)
  5  INTO sale VALUES (4, TO_DATE ('18-DEC-2007', 'DD-MON-YYYY'), 'Doll', 400, 75)
  6  INTO sale VALUES (5, TO_DATE ('18-DEC-2007', 'DD-MON-YYYY'), 'Doll', 300, 25)
  7  INTO sale VALUES (6, TO_DATE ('20-DEC-2007', 'DD-MON-YYYY'), 'Doll', 500, 25)
  8  SELECT * FROM DUAL
  9  /

6 rows created.


-- data in tables displayed:
SCOTT@orcl_11g> SELECT * FROM stk
  2  /

ITEM                      PRICE      MONTH       YEAR    OPENING PRODUCTION       SALE
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
Doll                        500         12       2007        100        300        375
Doll                        500         11       2007          0        100          0

SCOTT@orcl_11g> SELECT * FROM sale
  2  /

   BILL_NO BILL_DATE ITEM                      PRICE        QTY
---------- --------- -------------------- ---------- ----------
         1 10-DEC-07 Doll                        300         75
         2 15-DEC-07 Doll                        400        125
         3 17-DEC-07 Doll                        300         50
         4 18-DEC-07 Doll                        400         75
         5 18-DEC-07 Doll                        300         25
         6 20-DEC-07 Doll                        500         25

6 rows selected.


-- typical accounting method:
SCOTT@orcl_11g> SELECT item, bill_date, price,
  2  	    NVL (LAG (closing) OVER (PARTITION BY item ORDER BY bill_date, ROWNUM), opening) AS opening,
  3  	    production, sale, closing
  4  FROM   (SELECT item, bill_date, price, opening, production, sale,
  5  		    SUM (opening + production - sale) OVER (PARTITION BY item ORDER BY bill_date, ROWNUM) AS closing
  6  	     FROM   (SELECT item, TO_DATE ('01'||month||year, 'ddmmyyyy') AS bill_date, price,
  7  			    opening, production, TO_NUMBER (0) AS sale
  8  		     FROM   stk
  9  		     WHERE  month = 12
 10  		     AND    year  = 2007
 11  		     UNION ALL
 12  		     SELECT item, bill_date, price,
 13  			    TO_NUMBER (0) AS opening, TO_NUMBER (0) AS production, qty AS sale
 14  		     FROM   sale
 15  		     WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 16  		     AND    bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')))
 17  ORDER  BY item, bill_date
 18  /

ITEM                 BILL_DATE      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- --------- ---------- ---------- ---------- ---------- ----------
Doll                 01-DEC-07        500        100        300          0        400
Doll                 10-DEC-07        300        400          0         75        325
Doll                 15-DEC-07        400        325          0        125        200
Doll                 17-DEC-07        300        200          0         50        150
Doll                 18-DEC-07        400        150          0         75         75
Doll                 18-DEC-07        300         75          0         25         50
Doll                 20-DEC-07        500         50          0         25         25

7 rows selected.


-- grouping and ordering by price instead of date:
SCOTT@orcl_11g> SELECT item, price,
  2  	    NVL (LAG (closing) OVER (PARTITION BY item ORDER BY orderby), opening) AS opening,
  3  	    production, sale, closing
  4  FROM   (SELECT item, price, opening, production, sale, orderby,
  5  		    SUM (opening + production - sale) OVER (PARTITION BY item ORDER BY orderby) AS closing
  6  	     FROM   (SELECT item, price, opening, production, TO_NUMBER (0) AS sale, 0 AS orderby
  7  		     FROM   stk
  8  		     WHERE  month = 12
  9  		     AND    year  = 2007
 10  		     UNION ALL
 11  		     SELECT item, price,
 12  			    TO_NUMBER (0) AS opening, TO_NUMBER (0) AS production,
 13  			    SUM (qty) AS sale,
 14  			    RANK () OVER (PARTITION BY item ORDER BY price) AS orderby
 15  		     FROM   sale
 16  		     WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 17  		     AND    bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')
 18  		     GROUP  BY item, price))
 19  ORDER  BY item, orderby
 20  /

ITEM                      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Doll                        500        100        300          0        400
Doll                        300        400          0        150        250
Doll                        400        250          0        200         50
Doll                        500         50          0         25         25



-- the closest I can come to my best guess at what you want, splitting production:
SCOTT@orcl_11g> CLEAR COMPUTES
SCOTT@orcl_11g> CLEAR BREAKS
SCOTT@orcl_11g> BREAK ON item
SCOTT@orcl_11g> COMPUTE SUM LABEL total OF opening ON item
SCOTT@orcl_11g> COMPUTE SUM OF production ON item
SCOTT@orcl_11g> COMPUTE SUM OF sale ON item
SCOTT@orcl_11g> COMPUTE SUM OF closing ON item
SCOTT@orcl_11g> SELECT item, price, opening, production, sale, closing
  2  FROM (SELECT item, price,
  3  		  NVL (LAG (closing) OVER (PARTITION BY item ORDER BY orderby), opening) AS opening,
  4  		  production, sale, closing, orderby
  5  	   FROM   (SELECT item, price, opening, production, sale, orderby,
  6  			  SUM (opening + production - sale) OVER (PARTITION BY item ORDER BY orderby) AS closing
  7  		   FROM   (SELECT item, price, opening, sale, orderby,
  8  				  CASE WHEN rev_order = 1
  9  				       THEN
 10  					 SUM (production) OVER (PARTITION BY item ORDER BY price)
 11  					 + SUM (opening) OVER (PARTITION BY item ORDER BY price)
 12  					 - SUM (sale) OVER (PARTITION BY item ORDER BY price) + sale
 13  				       ELSE
 14  					 GREATEST (sale - NVL (LAG (opening) OVER (PARTITION BY item ORDER BY orderby), 0), 0)
 15  				  END AS production
 16  			   FROM   (SELECT item, price, opening, production, TO_NUMBER (0) AS sale, 0 AS orderby, 0 AS rev_order
 17  				   FROM   stk
 18  				   WHERE  month = 12
 19  				   AND	  year	= 2007
 20  				   UNION ALL
 21  				   SELECT item, price,
 22  					  TO_NUMBER (0) AS opening, TO_NUMBER (0) AS production,
 23  					  SUM (qty) AS sale,
 24  					  RANK () OVER (PARTITION BY item ORDER BY price) AS orderby,
 25  					  RANK () OVER (PARTITION BY item ORDER BY price DESC) AS rev_order
 26  				   FROM   sale
 27  				   WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 28  				   AND	  bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')
 29  				   GROUP  BY item, price))))
 30  WHERE  orderby > 0
 31  ORDER  BY item, price
 32  /

ITEM                      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Doll                        300        100         50        150          0
                            400          0        200        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  100        300        375         25

SCOTT@orcl_11g>

Re: breaking column opening and production [message #289653 is a reply to message #289459] Mon, 24 December 2007 22:10 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear Barbara,

thanks a lot.you have given ultimate solution and cleared the issue which i was finding myself unable to explain.simply ,you are genius.

Merry christmas.

Regards,
kaushal
Re: breaking column opening and production [message #289661 is a reply to message #289653] Tue, 25 December 2007 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe I'm totally dumb this morning following Christmas eve but I don't see how Barbara's query fir your requirement. It does not give the output you asked.

Regards
Michel
Re: breaking column opening and production [message #289916 is a reply to message #289539] Wed, 26 December 2007 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming the following rules:
- taking the sales in the order of "bill_no"
- if "qty" can be taken in "opening" then use "opening"
- else if "qty" can be taken in "production" then use "production"
- else split "qty", in this case all what can be taken in "opening" is taken there, the rest is taken from "production"
Then I get, with the data you gave in the first post:
SQL> select item, bill_no, price, qty, from_opening, from_production, closing
  2  from (select item, bill_no, price, qty,
  3               row_number () over (partition by item order by bill_no) rn,
  4               count(*) over (partition by item) cnt
  5        from sale 
  6        order by item, bill_no)
  7  model
  8    reference stk on 
  9      ( select item, opening month_opening, production month_production from stk )
 10      dimension by (item)
 11      measures (month_opening, month_production)
 12    dimension by (item, rn)
 13    measures (bill_no, price, qty,
 14              0 as from_opening, 0 as from_production, 0 as closing)
 15    rules update sequential order 
 16    ( -- calculating which "qty" is taken from "opening" for this sale
 17      from_opening[ANY,ANY] = 
 18        case 
 19          when qty[cv(),cv()] <= 
 20                 month_opening[cv(item)]
 21                 - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0)
 22            then qty[cv(),cv()]
 23          when qty[cv(),cv()] > 
 24                 month_production[cv(item)]
 25                 - nvl(sum(from_production)[cv(),rn between 1 and cv(rn)-1],0)
 26          then month_opening[cv(item)]
 27               - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0) 
 28          else 0
 29        end,
 30      -- calculating which "qty" is taken from "production" for this sale
 31      from_production[ANY,ANY] = 
 32        case 
 33          when qty[cv(),cv()] > 
 34                month_opening[cv(item)]
 35                - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0)
 36            then qty[cv(),cv()] - from_opening[cv(),cv()]
 37          else 0
 38        end,
 39      -- calculating "closing"
 40      closing[ANY,ANY] = 
 41        month_opening[cv(item)] + month_production[cv(item)]
 42        - sum(qty)[cv(),rn between 1 and cv(rn)]
 43      )
 44  /
ITEM          BILL_NO      PRICE        QTY FROM_OPENING FROM_PRODUCTION    CLOSING
---------- ---------- ---------- ---------- ------------ --------------- ----------
doll                1        300         75           75               0        325
doll                2        400        125            0             125        200
doll                3        300         50            0              50        150
doll                4        400         75            0              75         75
doll                5        300         25           25               0         50
doll                6        500         25            0              25         25

6 rows selected.

Now we will have to group this by item and price.
In the end, "closing" is associated to the last sale (in order of "bill_no") (assumption I made).
I modify the previous query to keep only "closing" in the last sale (see decode) and put it in a "with" clause.
SQL> break on item
SQL> compute sum of sale on item
SQL> compute sum of from_opening on item
SQL> compute sum of from_production on item
SQL> compute sum of closing on item
SQL> with 
  2    data as (
  3  select item, bill_no, price, qty, from_opening, from_production, 
  4         decode(rn,cnt,closing,0) closing
  5  from (select item, bill_no, price, qty,
  6               row_number () over (partition by item order by bill_no) rn,
  7               count(*) over (partition by item) cnt
  8        from sale 
  9        order by item, bill_no)
 10  model
 11    reference stk on 
 12      ( select item, opening month_opening, production month_production from stk )
 13      dimension by (item)
 14      measures (month_opening, month_production)
 15    dimension by (item, rn)
 16    measures (bill_no, price, qty, cnt,
 17              0 as from_opening, 0 as from_production, 0 as closing)
 18    rules update sequential order 
 19    ( -- calculating which "qty" is taken from "opening" for this sale
 20      from_opening[ANY,ANY] = 
 21        case 
 22          when qty[cv(),cv()] <= 
 23                 month_opening[cv(item)]
 24                 - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0)
 25            then qty[cv(),cv()]
 26          when qty[cv(),cv()] > 
 27                 month_production[cv(item)]
 28                 - nvl(sum(from_production)[cv(),rn between 1 and cv(rn)-1],0)
 29          then month_opening[cv(item)]
 30               - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0) 
 31          else 0
 32        end,
 33      -- calculating which "qty" is taken from "production" for this sale
 34      from_production[ANY,ANY] = 
 35        case 
 36          when qty[cv(),cv()] > 
 37                month_opening[cv(item)]
 38                - nvl(sum(from_opening)[cv(),rn between 1 and cv(rn)-1],0)
 39            then qty[cv(),cv()] - from_opening[cv(),cv()]
 40          else 0
 41        end,
 42      -- calculating "closing"
 43      closing[ANY,ANY] = 
 44        month_opening[cv(item)] + month_production[cv(item)]
 45        - sum(qty)[cv(),rn between 1 and cv(rn)]
 46      )
 47    )
 48  select item, price,
 49         sum(qty) sale,
 50         sum(from_opening) from_opening,
 51         sum(from_production) from_production,
 52         sum(closing) closing
 53  from data
 54  group by item, price
 55  order by item, price desc
 56  /
ITEM            PRICE       SALE FROM_OPENING FROM_PRODUCTION    CLOSING
---------- ---------- ---------- ------------ --------------- ----------
doll              500         25            0              25         25
                  400        200            0             200          0
                  300        150          100              50          0
**********            ---------- ------------ --------------- ----------
sum                          375          100             275         25

3 rows selected.

I don't get the same output as you gave, maybe some of my assumptions are not true (in the order the sales are handled for instance) or maybe your output is wrong.
Also the query does not handle the case you sale more items than you have.
Regards
Michel

Re: breaking column opening and production [message #289958 is a reply to message #289459] Wed, 26 December 2007 22:35 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear michel,

your are right as barbara's query is true for a particular case only.
I have updated the tables and my requirement with all possible conditions.
It will definitely clear all your doubts.

also your query is giving an error message
SQL> /
reference stk on
*
ERROR at line 11:
ORA-00907: missing right parenthesis

I am running it on oracle 9i wih window 2000 server.

SQL> SELECT * FROM SALE;

BILL_NO BILL_DATE ITEM PRICE QTY
---------- --------- -------------------- ---------- ----------
1 10-DEC-07 Doll 300 75
2 15-DEC-07 Doll 400 125
3 17-DEC-07 Doll 300 50
4 18-DEC-07 Doll 400 75
5 18-DEC-07 Doll 300 25
6 20-DEC-07 Doll 500 25
1 10-DEC-07 Toy 300 75
2 15-DEC-07 Toy 400 125
3 17-DEC-07 Toy 300 50
4 18-DEC-07 Toy 400 75
5 18-DEC-07 Toy 300 25
6 20-DEC-07 Toy 500 25
1 10-DEC-07 Bag 300 75
2 15-DEC-07 Bag 400 125
3 17-DEC-07 Bag 300 50
4 18-DEC-07 Bag 400 75
5 18-DEC-07 Bag 300 25
6 20-DEC-07 Bag 500 25
1 10-DEC-07 Torch 300 75
2 15-DEC-07 Torch 400 125
3 17-DEC-07 Torch 300 50
4 18-DEC-07 Torch 400 75
5 18-DEC-07 Torch 300 25
6 20-DEC-07 Torch 500 25
1 10-DEC-07 Lamp 300 75
2 15-DEC-07 Lamp 400 125
3 17-DEC-07 Lamp 300 50
4 18-DEC-07 Lamp 400 75
5 18-DEC-07 Lamp 300 25
6 20-DEC-07 Lamp 500 25

30 rows selected.

SQL> SELECT * FROM STK;

ITEM PRICE MONTH YEAR OPENING PRODUCTION SALE
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
Doll 500 12 2007 100 300 375
Toy 500 12 2007 200 200 375
Bag 500 12 2007 300 100 375
Lamp 500 12 2007 400 0 375
Torch 500 12 2007 0 400 375




I need result like following--------

ITEM PRICE OPENING PRODUCTION SALE CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Bag 300 150 0 150 0
400 125 75 200 0
500 25 25 25 25
******************** ---------- ---------- ---------- ----------
total 300 100 375 25

Doll 300 100 50 150 0
400 0 200 200 0
500 0 50 25 25
******************** ---------- ---------- ---------- ----------
total 100 300 375 25

Lamp 300 150 0 150 0
400 200 200 200 0
500 50 0 25 25
******************** ---------- ---------- ---------- ----------
total 400 0 375 25

Torch 300 0 150 150 0
400 0 200 200 0
500 0 50 25 25
******************** ---------- ---------- ---------- ----------
total 0 400 375 25

Toy 300 150 0 150 0
400 25 175 200 0
500 25 25 25 25
******************** ---------- ---------- ---------- ----------
total 200 200 375 25
Re: breaking column opening and production [message #289961 is a reply to message #289459] Wed, 26 December 2007 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Since you choose to not format your posts, I choose to not answer with a better response.

Your On Your Own (YOYO)!
Re: breaking column opening and production [message #289970 is a reply to message #289459] Wed, 26 December 2007 23:07 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear anacedant,

Forwarding it again in desire format.

Sorry Sad

Table sale

 

 BILL_NO BILL_DATE ITEM                      PRICE        QTY
---------- --------- -------------------- ---------- ----------
         1 10-DEC-07 Doll                        300         75
         2 15-DEC-07 Doll                        400        125
         3 17-DEC-07 Doll                        300         50
         4 18-DEC-07 Doll                        400         75
         5 18-DEC-07 Doll                        300         25
         6 20-DEC-07 Doll                        500         25
         1 10-DEC-07 Toy                         300         75
         2 15-DEC-07 Toy                         400        125
         3 17-DEC-07 Toy                         300         50
         4 18-DEC-07 Toy                         400         75
         5 18-DEC-07 Toy                         300         25
         6 20-DEC-07 Toy                         500         25
         1 10-DEC-07 Bag                         300         75
         2 15-DEC-07 Bag                         400        125
         3 17-DEC-07 Bag                         300         50
         4 18-DEC-07 Bag                         400         75
         5 18-DEC-07 Bag                         300         25
         6 20-DEC-07 Bag                         500         25
         1 10-DEC-07 Torch                       300         75
         2 15-DEC-07 Torch                       400        125
         3 17-DEC-07 Torch                       300         50
         4 18-DEC-07 Torch                       400         75
         5 18-DEC-07 Torch                       300         25
         6 20-DEC-07 Torch                       500         25
         1 10-DEC-07 Lamp                        300         75
         2 15-DEC-07 Lamp                        400        125
         3 17-DEC-07 Lamp                        300         50
         4 18-DEC-07 Lamp                        400         75
         5 18-DEC-07 Lamp                        300         25
         6 20-DEC-07 Lamp                        500         25
 


Table stk

ITEM                      PRICE      MONTH       YEAR    OPENING PRODUCTION       SALE
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
Doll                        500         12       2007        100        300        375
Toy                         500         12       2007        200        200        375
Bag                         500         12       2007        300        100        375
Lamp                        500         12       2007        400          0        375
Torch                       500         12       2007          0        400        375


Desire report

ITEM                      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Bag                         300        150         0        150         0
                            400        125        75        200         0
                            500         25        25         25        25
********************            ---------- ---------- ---------- ----------
total                                  300        100        375       25

Doll                        300        100         50        150        0
                            400          0        200        200        0
                            500          0         50         25       25
********************            ---------- ---------- ---------- ----------
total                                  100        300        375       25

Lamp                        300        150          0        150        0
                            400        200        200        200        0
                            500         50          0         25       25
********************            ---------- ---------- ---------- ----------
total                                   400        0        375        25

Torch                       300          0        150        150        0
                            400          0        200        200        0
                            500          0         50         25       25
********************            ---------- ---------- ---------- ----------
total                                    0        400        375       25

Toy                         300        150          0        150        0
                            400         25        175        200        0
                            500         25         25         25       25
********************            ---------- ---------- ---------- ----------
total                                  200        200        375       25



Re: breaking column opening and production [message #289983 is a reply to message #289970] Thu, 27 December 2007 00:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> BREAK  ON item SKIP 1
SCOTT@orcl_11g> COMPUTE SUM LABEL total OF opening ON item
SCOTT@orcl_11g> COMPUTE SUM OF production ON item
SCOTT@orcl_11g> COMPUTE SUM OF sale ON item
SCOTT@orcl_11g> COMPUTE SUM OF closing ON item
SCOTT@orcl_11g> SELECT item, price,
  2  	    SUM (opening) AS opening,
  3  	    SUM (production) AS production,
  4  	    SUM (sale) AS sale,
  5  	    SUM (opening) + SUM (production) - SUM (sale) AS closing
  6  FROM   (SELECT b2.item, b2.bill_date, b2.price, b2.opening,
  7  		    GREATEST
  8  		      (LEAST
  9  			((a2.opening
 10  			  + a2.production - NVL (LAG (b2.run_sale) OVER
 11  						  (PARTITION BY b2.item
 12  						   ORDER BY b2.bill_date, ROWNUM), 0)),
 13  			 b2.sale),
 14  		       0)
 15  		      - b2.opening AS production,
 16  		    b2.sale
 17  	     FROM   stk a2,
 18  		    (SELECT b1.item, b1.bill_date, b1.price,
 19  			    GREATEST
 20  			      (LEAST
 21  				((a1.opening - NVL (LAG (b1.run_sale) OVER
 22  						      (PARTITION BY b1.item
 23  						       ORDER BY b1.bill_date, ROWNUM), 0)),
 24  				 b1.sale),
 25  			       0) AS opening,
 26  			    b1.sale, b1.run_sale
 27  		     FROM   stk a1,
 28  			    (SELECT item, bill_date, price, qty AS sale,
 29  				    SUM (qty) OVER (PARTITION BY item ORDER BY bill_date, ROWNUM) AS run_sale
 30  			     FROM   sale
 31  			     WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 32  			     AND    bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')) b1
 33  		     WHERE  a1.item = b1.item
 34  		     AND    a1.month = 12
 35  		     AND    a1.year = 2007) b2
 36  	     WHERE  a2.item = b2.item
 37  	     AND    a2.month = 12
 38  	     AND    a2.year = 2007
 39  	     UNION ALL
 40  	     SELECT  sale.item, MAX (sale.bill_date) AS bill_date, stk.price,
 41  		     GREATEST ((stk.opening - SUM (sale.qty)), 0) AS opening,
 42  		     ((stk.opening + stk.production)
 43  		       - GREATEST ((stk.opening - SUM (sale.qty)), 0))
 44  		       - SUM (sale.qty) AS production,
 45  		     TO_NUMBER (0) AS saleS
 46  	     FROM    stk, sale
 47  	     WHERE   stk.item = sale.item
 48  	     AND     stk.month = 12
 49  	     AND     stk.year = 2007
 50  	     AND     sale.bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 51  	     AND     sale.bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')
 52  	     GROUP   BY sale.item, stk.price, stk.opening, stk.production)
 53  GROUP  BY item, price
 54  ORDER  BY item, price
 55  /

ITEM                      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Bag                         300        125         25        150          0
                            400        175         25        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  300        100        375         25

Doll                        300         75         75        150          0
                            400         25        175        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  100        300        375         25

Lamp                        300        150          0        150          0
                            400        200          0        200          0
                            500         50          0         25         25
********************            ---------- ---------- ---------- ----------
total                                  400          0        375         25

Torch                       300          0        150        150          0
                            400          0        200        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                    0        400        375         25

Toy                         300         75         75        150          0
                            400        125         75        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  200        200        375         25


15 rows selected.

SCOTT@orcl_11g> 

Re: breaking column opening and production [message #289988 is a reply to message #289970] Thu, 27 December 2007 00:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Here is a slightly more streamlined version than my prior version. I believe the model clause that Michel used requires 10g, but I think you can use the subquery factoring (with) clause in 9i.

SCOTT@orcl_11g> BREAK  ON item SKIP 1
SCOTT@orcl_11g> COMPUTE SUM LABEL total OF opening ON item
SCOTT@orcl_11g> COMPUTE SUM OF production ON item
SCOTT@orcl_11g> COMPUTE SUM OF sale ON item
SCOTT@orcl_11g> COMPUTE SUM OF closing ON item
SCOTT@orcl_11g> WITH   stocks AS
  2  	      (SELECT item, price, opening, production
  3  	       FROM   stk
  4  	       WHERE  month = 12
  5  	       AND    year = 2007),
  6  	    sales AS
  7  	      (SELECT bill_date, item, price, qty
  8  	       FROM   sale
  9  	       WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 10  	       AND    bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy'))
 11  SELECT item, price,
 12  	    SUM (opening) AS opening,
 13  	    SUM (production) AS production,
 14  	    SUM (sale) AS sale,
 15  	    SUM (opening) + SUM (production) - SUM (sale) AS closing
 16  FROM   (SELECT b2.item, b2.price, b2.opening,
 17  		    GREATEST
 18  		      (LEAST
 19  			((a2.opening
 20  			  + a2.production - NVL (LAG (b2.run_sale) OVER
 21  						  (PARTITION BY b2.item
 22  						   ORDER BY b2.bill_date, ROWNUM), 0)),
 23  			 b2.sale),
 24  		       0)
 25  		      - b2.opening AS production,
 26  		    b2.sale
 27  	     FROM   stocks a2,
 28  		    (SELECT b1.item, b1.bill_date, b1.price,
 29  			    GREATEST
 30  			      (LEAST
 31  				((a1.opening - NVL (LAG (b1.run_sale) OVER
 32  						      (PARTITION BY b1.item
 33  						       ORDER BY b1.bill_date, ROWNUM), 0)),
 34  				 b1.sale),
 35  			       0) AS opening,
 36  			    b1.sale, b1.run_sale
 37  		     FROM   stocks a1,
 38  			    (SELECT item, bill_date, price, qty AS sale,
 39  				    SUM (qty) OVER (PARTITION BY item ORDER BY bill_date, ROWNUM) AS run_sale
 40  			     FROM   sale
 41  			     WHERE  bill_date >= TO_DATE ('01-dec-2007', 'dd-mon-yyyy')
 42  			     AND    bill_date <  TO_DATE ('01-jan-2008', 'dd-mon-yyyy')) b1
 43  		     WHERE  a1.item = b1.item) b2
 44  	     WHERE  a2.item = b2.item
 45  	     UNION ALL
 46  	     SELECT  sales.item, stocks.price,
 47  		     GREATEST ((stocks.opening - SUM (sales.qty)), 0) AS opening,
 48  		     ((stocks.opening + stocks.production)
 49  		       - GREATEST ((stocks.opening - SUM (sales.qty)), 0))
 50  		       - SUM (sales.qty) AS production,
 51  		     TO_NUMBER (0) AS saleS
 52  	     FROM    stocks, sales
 53  	     WHERE   stocks.item = sales.item
 54  	     GROUP   BY sales.item, stocks.price, stocks.opening, stocks.production)
 55  GROUP  BY item, price
 56  ORDER  BY item, price
 57  /

ITEM                      PRICE    OPENING PRODUCTION       SALE    CLOSING
-------------------- ---------- ---------- ---------- ---------- ----------
Bag                         300        125         25        150          0
                            400        175         25        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  300        100        375         25

Doll                        300         75         75        150          0
                            400         25        175        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  100        300        375         25

Lamp                        300        150          0        150          0
                            400        200          0        200          0
                            500         50          0         25         25
********************            ---------- ---------- ---------- ----------
total                                  400          0        375         25

Torch                       300          0        150        150          0
                            400          0        200        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                    0        400        375         25

Toy                         300         75         75        150          0
                            400        125         75        200          0
                            500          0         50         25         25
********************            ---------- ---------- ---------- ----------
total                                  200        200        375         25


15 rows selected.

SCOTT@orcl_11g> 

Re: breaking column opening and production [message #289996 is a reply to message #289958] Thu, 27 December 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

SQL> /
reference stk on
*
ERROR at line 11:
ORA-00907: missing right parenthesis

I am running it on oracle 9i wih window 2000 server.

MODEL clause was introduce in 10g, this is why you have ALWAYS post your Oracle version with at least 3 decimals.
You didn't say why my output is different from yours. Was one of my assumptions wrong or was your output wrong?
Also you didn't say how split is done. I chose one way, is this the correct one?
Describe the rules as I did.

Regards
Michel
Re: breaking column opening and production [message #290211 is a reply to message #289459] Fri, 28 December 2007 00:13 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Dear Barbara,

your sent query is too good for oracle 9i.I need same query which ever run on oracle 8i, please.

Regards,
kaushal
Re: breaking column opening and production [message #290219 is a reply to message #290211] Fri, 28 December 2007 00:53 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
8i? 8.1.? I believe anyalytic functions were introduced in 8.1.6. If you have that, then you should be able to use the query without the subquery factoring (with) clause. Also, 8i is no longer supported. You should upgrade. The most current version is 11g. I am not even sure if 9i is even still supported.

[Updated on: Fri, 28 December 2007 00:53]

Report message to a moderator

Previous Topic: Return Table in a Function
Next Topic: A problem with UTL_FILE and cursor
Goto Forum:
  


Current Time: Fri Dec 09 03:47:07 CST 2016

Total time taken to generate the page: 0.14156 seconds