Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Analytical funtion

Analytical funtion

From: hardnut <tenorense_at_yahoo.com>
Date: 16 Jun 2006 09:30:25 -0700
Message-ID: <1150475425.346708.53610@f6g2000cwb.googlegroups.com> 





Hi All,


Please help with this query.


I am trying to calculate the cummulative inventory by subtracting
sales from receipt. If the inventory is nagative, I want to make it
zero and start again from the next reciept value. Otherwise, I add the
previous inventory value to the current receipt and subtract the
current sales.



Your help will be invaluable.



Thanks.



DROP TABLE inventory;



CREATE TABLE INVENTORY



(


  BOOK#          NUMBER,



  BORROWED_DATE  DATE,


  SALES          NUMBER,
  RECEIPT        NUMBER




);



INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '09/27/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '09/28/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '09/29/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '09/30/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/01/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 144);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/02/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/03/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/04/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 576);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/05/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/06/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 576);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/07/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
93, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/08/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
270, 162);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/09/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
279, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/10/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
272, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/11/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
103, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/12/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
56, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/13/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
61, 72);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/14/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
73, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/15/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
199, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/16/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
202, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/17/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
38, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/18/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
41, 612);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/19/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
28, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/20/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
44, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/21/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
80, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/22/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
239, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/23/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
285, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/24/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
39, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/25/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
40, 294);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/26/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
45, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/27/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
38, 42);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/28/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
51, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/29/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
138, 42);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/30/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
177, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '10/31/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
1, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '11/01/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
0, 0);


INSERT INTO INVENTORY ( BOOK#, BORROWED_DATE, SALES, RECEIPT ) VALUES (


27075,  TO_Date( '11/02/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
1, 0);


COMMIT;




---QUERY



SELECT book#,borrowed_date,sales,receipt,


              CASE WHEN SIGN( SUM(receipt-sales) over(partition by book#,grp
ORDER by borrowed_date asc )) = -1 THEN 0


                       ELSE SUM(receipt-sales) over(partition by book#,grp ORDER by
borrowed_date asc ) END  ending_inventory

	  FROM (
           SELECT book#,borrowed_date,sales,receipt,
           	      MAX(rn) 	over(partition by book# ORDER by
borrowed_date asc ) grp
           FROM(
           	SELECT book#,borrowed_date,sales,receipt,
           			 CASE WHEN 	 SIGN(receipt) = 1
           			      AND  SIGN(LAG(ending_inventory)over(partition by
book# ORDER by borrowed_date asc )) = -1
           				  AND  SIGN(LEAD(receipt- sales)over(partition by book#
ORDER by borrowed_date asc ))  = -1
           			 THEN  row_number() over(partition by  book# order by
borrowed_date asc) ELSE NULL END rn
               FROM (
           		SELECT book#,borrowed_date,sales,receipt,
           		       sum(receipt) over(partition by book# ORDER by
borrowed_date asc ) -
           			   sum(sales) over(partition by book# ORDER by
borrowed_date asc ) ending_inventory
           		FROM inventory
           	  )
           	)
		)
	ORDER BY borrowed_date asc

-------------my output
BOOK#	BORROWED_DATE	SALES	RECEIPT	  ENDING_INVENTORY
27075	9/27/2005		0	0	0
27075	9/28/2005		0	0	0
27075	9/29/2005		0	0	0
27075	9/30/2005		0	0	0
27075	10/1/2005		0	144	144
27075	10/2/2005		0	0	144
27075	10/3/2005		0	0	144
27075	10/4/2005		0	576	720
27075	10/5/2005		0	0	720
27075	10/6/2005		0	576	1296
27075	10/7/2005		93	0	1203
27075	10/8/2005		270	162	1095
27075	10/9/2005		279	0	816
27075	10/10/2005		272	0	544
27075	10/11/2005		103	0	441
27075	10/12/2005		56	0	385
27075	10/13/2005		61	72	396
27075	10/14/2005		73	0	323
27075	10/15/2005		199	0	124
27075	10/16/2005		202	0	0
27075	10/17/2005		38	0	0
27075	10/18/2005		41	612	571
27075	10/19/2005		28	0	543
27075	10/20/2005		44	0	499
27075	10/21/2005		80	0	419
27075	10/22/2005		239	0	180
27075	10/23/2005		285	0	0
27075	10/24/2005		39	0	0
27075	10/25/2005		40	294	254
27075	10/26/2005		45	0	209
27075	10/27/2005		38	42	4        <<<<====== wrong
27075	10/28/2005		51	0	0        <<<<====== wrong
27075	10/29/2005		138	42	0        <<<<====== wrong
27075	10/30/2005		177	0	0
27075	10/31/2005		1	0	0
27075	11/1/2005		0	0	0
27075	11/2/2005		1	0	0






EXPECTED OUTPUT





BOOK#	BORROWED_DATE	SALES	RECEIPT	  ENDING_INVENTORY
27075	9/27/2005		0	0	0
27075	9/28/2005		0	0	0
27075	9/29/2005		0	0	0
27075	9/30/2005		0	0	0
27075	10/1/2005		0	144	144
27075	10/2/2005		0	0	144
27075	10/3/2005		0	0	144
27075	10/4/2005		0	576	720
27075	10/5/2005		0	0	720
27075	10/6/2005		0	576	1296
27075	10/7/2005		93	0	1203
27075	10/8/2005		270	162	1095
27075	10/9/2005		279	0	816
27075	10/10/2005		272	0	544
27075	10/11/2005		103	0	441
27075	10/12/2005		56	0	385
27075	10/13/2005		61	72	396
27075	10/14/2005		73	0	323
27075	10/15/2005		199	0	124
27075	10/16/2005		202	0	0
27075	10/17/2005		38	0	0
27075	10/18/2005		41	612	571
27075	10/19/2005		28	0	543
27075	10/20/2005		44	0	499
27075	10/21/2005		80	0	419
27075	10/22/2005		239	0	180
27075	10/23/2005		285	0	0
27075	10/24/2005		39	0	0
27075	10/25/2005		40	294	254
27075	10/26/2005		45	0	209
27075	10/27/2005		38	42	213
27075	10/28/2005		51	0	162
27075	10/29/2005		138	42	66
27075	10/30/2005		177	0	0
27075	10/31/2005		1	0	0
27075	11/1/2005		               0	0	0
27075	11/2/2005		               1	0	0


Received on Fri Jun 16 2006 - 11:30:25 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US