conditional incremensum (condition is on the sum value of the previous row)

518921

Hi,
i know is a little bit tricky but i need to know the credit of a client after each invoice.
so if sum(invoice_val-previous_credit) >0 then the credit is 0 else credit is previous_credit-invoice_val.
As you can see to dermine the credit value of the current row it is needed to check the same value of the previous row.Is this possible in some way?

EX.
so the situation in the table is the following:

ID_CLIENT	ID_invoice	invoice_VAL
8.789	220.227	120,47
8.789	238.342	109,76
8.789	246.388	121,69
8.789	258.163	137,45
8.789	268.969	138,67
8.789	295.455	145,16
8.789	311.395	138,92
8.789	327.104	138,96
8.789	340.793	-335,18
8.789	375.451	129,14
8.789	386.650	125,57
8.789	398.606	124,18
8.789	428.166	31,66
8.789	435.844	25,93
8.789	447.639	34,32
8.789	462.137	-43,64
8.789	475.613	-110,39
8.789	485.022	92,29
8.789	495.807	91,67

i need something like this:

ID_CLIENT	ID_invoice	invoice_VAL credit
8.789	220.227	120,47	0,00
8.789	238.342	109,76	0,00
8.789	246.388	121,69	0,00
8.789	258.163	137,45	0,00
8.789	268.969	138,67	0,00
8.789	295.455	145,16	0,00
8.789	311.395	138,92	0,00
8.789	327.104	138,96	0,00
8.789	340.793	-335,18	335,18
8.789	375.451	129,14	206,04
8.789	386.650	125,57	80,47
8.789	398.606	124,18	0,00
8.789	428.166	31,66	0,00
8.789	435.844	25,93	0,00
8.789	447.639	34,32	0,00
8.789	462.137	-43,64	43,64
8.789	475.613	-110,39	154,03
8.789	485.022	92,29	61,74
8.789	495.807	91,67	0,00

thanks very much

Edited by: 4ndr34 on Jan 28, 2010 1:38 PM

Frank Kulash

Hi,

4ndr34 wrote:
Thanks but model is not available in oracle9

Did you mention that before?
It's a good idea to include your version number whenever you ask a question, especially if the version is as old as Oracle 9.

Here's an analytic solution that should work in Oracle 9 (and up):

WITH	e	AS
(
	SELECT	hiredate
	,	CASE
			WHEN  job	IN ('PRESIDENT', 'SALESMAN')	
			THEN  -sal
			ELSE  sal
		END	AS sal2
	,	ROW_NUMBER () OVER ( ORDER BY  hiredate
			      	     ,	       empno
				   ) AS rnum
	FROM	scott.emp
)
,	got_grp_start	AS
(
	SELECT	h.*
	,	CASE
			WHEN  sal2 >= 0
			THEN  0
			WHEN  EXISTS ( SELECT    NULL
			      	       FROM      e   l
				       JOIN      e   m	ON	m.rnum	>= l.rnum
				       WHERE     l.rnum	< h.rnum + 0	-- See note below
				       AND	 m.rnum	< h.rnum + 0	-- See note below
				       GROUP BY	 l.rnum
				       HAVING	 SUM (m.sal2) < 0
				     )
			THEN  0
			ELSE  1
		END		AS grp_start
	FROM	e	h
)
,	got_grp		AS
(
	SELECT	got_grp_start.*
	,	SUM (grp_start) OVER (ORDER BY rnum)	AS grp
	FROM	got_grp_start
)
SELECT	hiredate
,	sal2
,	GREATEST ( SUM (-sal2) OVER ( PARTITION BY  grp
		       	       	      ORDER BY	    rnum
				    )
		 , 0
		 )	AS credit
FROM	got_grp
ORDER BY	rnum
;

The EXISTS sub-query in got_grp_start doesn't work correctly (at least in Oracle 11.1.0.6.0 or 10.2.0.3.0) if it references "h.rnum", but it does if it references "h.rnum+0" or "h.rnum-0".

The only purpose of sub-query e is to produce a useful set of raw data, including a simple expression (rnum) that can be used in sorting. You probably don't need anything like e, depending on your actual table.

If you need a separate calculation for, say, every department, then add PARTITION BY to the analytic clauses.

Karthick2003

To get the previous value you can try using LAG analytical function.

518921

it is not so simple....
if you could read carefully, you'll understand!!!!

Anurag Tibrewal

Hi,

You meant

select 	ID_CLIENT	, ID_invoice
	, invoice_VAL	, decode(sign(c_credit),1,0,-c_credit) credit 
from(
	select 	ID_CLIENT, ID_invoice
		, invoice_VAL, sum(invoice_VAL) over (partition by ID_CLIENT order by ID_invoice) c_credit 
	from t1
);

Regards
Anurag

518921

i have tried something similar , but i have tried also copying your query.
i always get 0 as credit because the check is on the credit obtained considering all the (previous) invoices, instead i do not have to consider the invoices >0 if i have credit =0.
so if i had
100
100
-200
100
150

i should get
100->0
100->0
-200->200
100->100 (200 as previous credit - current invoice)
150->0 (100 as previous credit - current invoice, but i don't want to track the debit of the customers, so ->zero)

i hope i have been clear...

Frank Kulash

Hi,

I think MODEL is the best way to do this.

Since you didn't post CREATE TABLE and INSERT statements, I'll use the scott.emp table to illustrate.
Say we want to get these results from the scott.emp table:

HIREDATE        SAL2     CREDIT
--------- ---------- ----------
17-DEC-80        800          0
20-FEB-81      -1600       1600
22-FEB-81      -1250       2850
02-APR-81       2975          0
01-MAY-81       2850          0
09-JUN-81       2450          0
08-SEP-81      -1500       1500
28-SEP-81      -1250       2750
17-NOV-81      -5000       7750
03-DEC-81        950       6800
03-DEC-81       3000       3800
23-JAN-82       1300       2500
19-APR-87       3000          0
23-MAY-87       1100          0

Where the results are in order by hirdate (then empno, if there is a tie).
Sal2 is sal or -sal, depending on each employee's job.
Credit is the previous row's credit, minus sal2, but never less than 0.

Here's one way to compute credit using MODEL:

WITH	e	AS
(
	SELECT	hiredate
	,	CASE
			WHEN  job	IN ('PRESIDENT', 'SALESMAN')	
			THEN  -sal
			ELSE  sal
		END	AS sal2
	,	ROW_NUMBER () OVER ( ORDER BY  hiredate
			      	     ,	       empno
				   ) AS rnum
	FROM	scott.emp
)
SELECT	hiredate
,	sal2
,	credit
FROM	e
MODEL	DIMENSION BY	(rnum)
	MEASURES  ( hiredate
		  , sal2
		  , 0 credit
		  )
	RULES 
	      ( credit [ANY] ORDER BY rnum
	      	       	     	 = NVL ( GREATEST ( credit [CV () - 1] - sal2 [CV ()]
	      	  	 	   	    , 0
					    )
				       , GREATEST (-sal2 [CV()], 0)
				       )
	      )
ORDER BY  hiredate
;

To experiment with this, you can change the jobs that result in negative sal2s. For example, include 'CLERK' to see what happens when the first row has a credit.

You can get the same results using the analytic SUM function, but you have to PARTITION BY a group that starts with a negative sal2, and continues until credit = 0. Computing those groups requires other analytic functions, and it's much more complicated than the MODEL solution.

You can also get these results using CONNECT BY.

Using either of these last two techniques might be a good, challenging exercise for someone interested in analytic functions or CONNECT BY.

Edited by: Frank Kulash on Jan 28, 2010 10:32 AM

518921

Thanks but model is not available in oracle9

may i have an aid in the alternative ways you mentioned?

Edited by: 4ndr34 on Jan 28, 2010 5:42 PM

Frank Kulash

Hi,

4ndr34 wrote:
Thanks but model is not available in oracle9

Did you mention that before?
It's a good idea to include your version number whenever you ask a question, especially if the version is as old as Oracle 9.

Here's an analytic solution that should work in Oracle 9 (and up):

WITH	e	AS
(
	SELECT	hiredate
	,	CASE
			WHEN  job	IN ('PRESIDENT', 'SALESMAN')	
			THEN  -sal
			ELSE  sal
		END	AS sal2
	,	ROW_NUMBER () OVER ( ORDER BY  hiredate
			      	     ,	       empno
				   ) AS rnum
	FROM	scott.emp
)
,	got_grp_start	AS
(
	SELECT	h.*
	,	CASE
			WHEN  sal2 >= 0
			THEN  0
			WHEN  EXISTS ( SELECT    NULL
			      	       FROM      e   l
				       JOIN      e   m	ON	m.rnum	>= l.rnum
				       WHERE     l.rnum	< h.rnum + 0	-- See note below
				       AND	 m.rnum	< h.rnum + 0	-- See note below
				       GROUP BY	 l.rnum
				       HAVING	 SUM (m.sal2) < 0
				     )
			THEN  0
			ELSE  1
		END		AS grp_start
	FROM	e	h
)
,	got_grp		AS
(
	SELECT	got_grp_start.*
	,	SUM (grp_start) OVER (ORDER BY rnum)	AS grp
	FROM	got_grp_start
)
SELECT	hiredate
,	sal2
,	GREATEST ( SUM (-sal2) OVER ( PARTITION BY  grp
		       	       	      ORDER BY	    rnum
				    )
		 , 0
		 )	AS credit
FROM	got_grp
ORDER BY	rnum
;

The EXISTS sub-query in got_grp_start doesn't work correctly (at least in Oracle 11.1.0.6.0 or 10.2.0.3.0) if it references "h.rnum", but it does if it references "h.rnum+0" or "h.rnum-0".

The only purpose of sub-query e is to produce a useful set of raw data, including a simple expression (rnum) that can be used in sorting. You probably don't need anything like e, depending on your actual table.

If you need a separate calculation for, say, every department, then add PARTITION BY to the analytic clauses.

518921

thanks you are a monster:D

Eventually, i decided to solve with a function that return the credit.

however, thanks very much for your time

Boneist

4ndr34 wrote:
thanks you are a monster:D

Eventually, i decided to solve with a function that return the credit.

however, thanks very much for your time

Out of curiosity, how is your function coded?

I don't think that doing it that way would be more efficient than including the calculations in one SQL statement, such as the ones Frank came up with! I'd be interested to hear how the SQL solution and the function solution compare performance wise when run against proper levels of data.

518921

hi!

i did it with a function before Frank's second answer, so, not for choice but just because it was the only way i could do it.
probably (surely), Frank's way is the best one if i needed the complete result (credit after each invoice, for each client) , but i need (every time i launch the query) only the balance (credit) after the last invoice for each client ( i have a list i can launch the query against).
the function is as follow:

CREATE OR REPLACE Function credito
   ( id_fattura_in IN number ) --id invoice
   RETURN number
IS
    saldo number; -- credit
    VAL_FATTURA number; --invoice value
    id_sito_p number; -- id contract/client
    
    
    
    cursor c1 (id_sito_in in number) is
    SELECT VAL_TOT_FATTURA FROM T016_FATTURE_TESTATE 
        WHERE ID_sito= id_sito_in AND id_fattura< id_fattura_IN  and cod_tipo_fattura='NOR' ORDER BY id_FATTURA;

BEGIN
select id_sito into id_sito_p from t016_fatture_testate where id_fattura=id_fattura_in;

SALDO:= 0;

open c1(id_sito_p);

LOOP
      FETCH C1 INTO VAL_FATTURA;
      EXIT WHEN C1%NOTFOUND;
      
      SALDO:=LEAST(SALDO+VAL_FATTURA,0);
      
      
   END LOOP;

close c1;

RETURN saldo;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'Errore:  - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/