Forum Stats

  • 3,840,087 Users
  • 2,262,565 Discussions
  • 7,901,149 Comments

Discussions

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

518921
518921 Member Posts: 12
edited Feb 2, 2010 5:18PM in SQL & PL/SQL
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

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,267 Red Diamond
    edited Jan 29, 2010 11:34AM Answer ✓
    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.
«1

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    To get the previous value you can try using LAG analytical function.
  • 518921
    518921 Member Posts: 12
    it is not so simple....
    if you could read carefully, you'll understand!!!!
  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    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
    518921 Member Posts: 12
    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
    Frank Kulash Member, Moderator Posts: 42,267 Red Diamond
    edited Jan 28, 2010 10:36AM
    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
    Frank Kulash
  • 518921
    518921 Member Posts: 12
    edited Jan 28, 2010 11:43AM
    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
    Frank Kulash Member, Moderator Posts: 42,267 Red Diamond
    edited Jan 29, 2010 11:34AM Answer ✓
    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
    518921 Member Posts: 12
    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
    Boneist Member Posts: 4,983 Gold Trophy
    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
    518921 Member Posts: 12
    edited Feb 1, 2010 3:32AM
    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;
    /
This discussion has been closed.