Home » SQL & PL/SQL » SQL & PL/SQL » Query help with cumulative sum to be broken into sections (Oracle 10.1)
icon4.gif  Query help with cumulative sum to be broken into sections [message #366226] Fri, 12 December 2008 03:26 Go to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
Greetings all!

I've come into a problem with a database query than on surface seems to be easy but is troubling me since yesterday.
I've putted some effort to provide some valid sql to reproduce the starting point and the desired output

drop table test_fatt;
create table test_fatt (
 id VARCHAR2(10),
 month NUMBER(10),
 usd NUMBER(10)) ;


INSERT INTO test_fatt VALUES ('A', 1, 3);
INSERT INTO test_fatt VALUES ('A', 2, 5);
INSERT INTO test_fatt VALUES ('A', 3, 2);
INSERT INTO test_fatt VALUES ('A', 4, 8);
INSERT INTO test_fatt VALUES ('A', 5, 13);
INSERT INTO test_fatt VALUES ('A', 6, 3);
INSERT INTO test_fatt VALUES ('B', 1, 2);
INSERT INTO test_fatt VALUES ('B', 2, 15);
INSERT INTO test_fatt VALUES ('B', 3, 23);
INSERT INTO test_fatt VALUES ('B', 4, 8);
INSERT INTO test_fatt VALUES ('B', 5, 3);
INSERT INTO test_fatt VALUES ('B', 6, 5);
INSERT INTO test_fatt VALUES ('C', 1, 5);
INSERT INTO test_fatt VALUES ('C', 2, 15);
INSERT INTO test_fatt VALUES ('C', 3, 7);
INSERT INTO test_fatt VALUES ('C', 6, 2);
INSERT INTO test_fatt VALUES ('D', 1, 12);
INSERT INTO test_fatt VALUES ('D', 2, 5);
INSERT INTO test_fatt VALUES ('D', 3, 3);
INSERT INTO test_fatt VALUES ('D', 4, 4);
INSERT INTO test_fatt VALUES ('D', 5, 2);
INSERT INTO test_fatt VALUES ('D', 6, 8);
commit;


This reproduce the starting point in which you have an id for a company (A-B-C-D) 6 month of data (you can have no data for a month in this case the row is missing) and a number that represents the usd of sales

The output should be as follow:

drop table test_cum_fatt;
create table test_cum_fatt (
 id VARCHAR2(10), 
 month NUMBER(10),
 usd NUMBER(10),
 cumulative_invoice NUMBER(10),
 invoice_month NUMBER(10)) ;

INSERT INTO test_cum_fatt VALUES ('A', 1, 3, 3, 3);
INSERT INTO test_cum_fatt VALUES ('A', 2, 5, 8, 3);
INSERT INTO test_cum_fatt VALUES ('A', 3, 2, 10, 3);
INSERT INTO test_cum_fatt VALUES ('A', 4, 8, 8,5 );
INSERT INTO test_cum_fatt VALUES ('A', 5, 13, 21,5);
INSERT INTO test_cum_fatt VALUES ('A', 6, 3, 3,'' );
INSERT INTO test_cum_fatt VALUES ('B', 1, 2,2,2);
INSERT INTO test_cum_fatt VALUES ('B', 2, 15,17,2);
INSERT INTO test_cum_fatt VALUES ('B', 3, 23,23,3);
INSERT INTO test_cum_fatt VALUES ('B', 4, 8,8,5);
INSERT INTO test_cum_fatt VALUES ('B', 5, 3,11,5);
INSERT INTO test_cum_fatt VALUES ('B', 6, 5,5,'');
INSERT INTO test_cum_fatt VALUES ('C', 1, 5,5,2);
INSERT INTO test_cum_fatt VALUES ('C', 2, 15,20,2);
INSERT INTO test_cum_fatt VALUES ('C', 3, 7,7,'');
INSERT INTO test_cum_fatt VALUES ('C', 6, 2,9,'');
INSERT INTO test_cum_fatt VALUES ('D', 1, 12,12,1);
INSERT INTO test_cum_fatt VALUES ('D', 2, 5,5,4);
INSERT INTO test_cum_fatt VALUES ('D', 3, 3,8,4);
INSERT INTO test_cum_fatt VALUES ('D', 4, 4,12,4);
INSERT INTO test_cum_fatt VALUES ('D', 5, 2,2,6);
INSERT INTO test_cum_fatt VALUES ('D', 6, 8,10,6);
commit;


In which we have the same structure of the starting point in the first 3 colums, then we have 2 other colums:

4th colums is a cumulative sum of the usd breaken up by the id of the company, this cumulative sum should reset back to 0 when the company change and when the cumulative sum reaches 10 or more

5th colums indicates for each row when the usd cumulative amount has reached at least 10

Lets consider company A for example:
First month was 3, so not reached 10
Second month was 5, cumulative is 8 not reached 10
Third month was 2, cumulative is 10 so the minimum of 10 is reached.
Invoice is sent for the amount of 10 and for each month should bee noted that it was on the third month that amount is reached

Forth month amount is 8, so not reached 10
Fifth is 13, so total is 21, reached at least 10. Invoice is sent and forth-fifth month reached the amount on the fifth month

Sixth month amount is 3 , so not reached 10

I hope this is clear enough!
Any help is appreciated!
Thank you
Fabio

[Updated on: Tue, 16 December 2008 08:53] by Moderator

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #366232 is a reply to message #366226] Fri, 12 December 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks a lot for the test case.
Could you post the output as you repost the test case instead of the ouput in your previous.
The explainations are clear but I want to be sure I missed nothing.

Regards
Michel

[Updated on: Fri, 12 December 2008 03:49]

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #366234 is a reply to message #366232] Fri, 12 December 2008 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's nested analytic functions time!

There's almost certainly a better solution using the MODEL clause.

select id
      ,month
      ,usd
      ,cum_tot
      ,min(inv_month) over 
         (partition by id order by month 
          rows between current row and unbounded following) x
from (select id
            ,month
            ,usd
            ,cum_tot
            ,case when nvl(lag(tens) over (partition by id order by month),tens) 
                         != tens then month end inv_month
      from (select id
                  ,month
                  ,usd
                  ,sum(usd) over (partition by id order by month) cum_tot
                  ,floor(sum(usd) over (partition by id order by month)/10) tens
            from test_fatt
     )     )
order by id,month;


You can (and I'd say should) have a play with this - run the innermost query, and then the inner two together and see what they produce.

The innermost subquery generates the cumulative total for each month..
The next query out spots where the cumulative total goes over a multiple of 10, and flags these up with the month in which it happened.
The outermost query looks forwards from each row to see what the smallest month ahead in which the invoice month changed is, and back fills the current row with that value.

Results:
ID              MONTH        USD    CUM_TOT  INV_MONTH
---------- ---------- ---------- ---------- ----------
A                   1          3          3          3
A                   2          5          8          3
A                   3          2         10          3
A                   4          8         18          5
A                   5         13         31          5
A                   6          3         34
B                   1          2          2          2
B                   2         15         17          2
B                   3         23         40          3
B                   4          8         48          5
B                   5          3         51          5
B                   6          5         56
C                   1          5          5          2
C                   2         15         20          2
C                   3          7         27
C                   6          2         29
D                   1         12         12          3
D                   2          5         17          3
D                   3          3         20          3
D                   4          4         24          6
D                   5          2         26          6
D                   6          8         34          6

22 rows selected.

[Updated on: Tue, 16 December 2008 08:52] by Moderator

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #366237 is a reply to message #366234] Fri, 12 December 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure but I think CUM_TOT should be clear after raising or exceeding 10. So 4th line it should be 8, 5th one 21, 6th one 3...
If this is true I don't think you can do it with just analytics.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #366241 is a reply to message #366226] Fri, 12 December 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this what you want?
SQL> break on id dup skip 1
SQL> select id, month, usd, cum_tot, flag
  2  from test_fatt
  3  model
  4    partition by (id)
  5    dimension by (month)
  6    measures (usd, 0 cum_tot, 0 flag)
  7    rules update sequential order (
  8      cum_tot[ANY] order by month = 
  9        case when nvl(cum_tot[cv()-1],0) >= 10
 10          then usd[cv()] 
 11          else nvl(cum_tot[cv()-1],0)+usd[cv()] 
 12        end,
 13      flag[ANY] order by month = 
 14        case when cum_tot[cv()] >= 10 then 1 else 0 end
 15    )
 16  order by 1, 2
 17  /
ID              MONTH        USD    CUM_TOT       FLAG
---------- ---------- ---------- ---------- ----------
A                   1          3          3          0
A                   2          5          8          0
A                   3          2         10          1
A                   4          8          8          0
A                   5         13         21          1
A                   6          3          3          0

B                   1          2          2          0
B                   2         15         17          1
B                   3         23         23          1
B                   4          8          8          0
B                   5          3         11          1
B                   6          5          5          0

C                   1          5          5          0
C                   2         15         20          1
C                   3          7          7          0
C                   6          2          2          0

D                   1         12         12          1
D                   2          5          5          0
D                   3          3          8          0
D                   4          4         12          1
D                   5          2          2          0
D                   6          8         10          1


22 rows selected.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #366243 is a reply to message #366226] Fri, 12 December 2008 04:43 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
First of all thank you!
I never expected such a great and fast support !

@JRowbottom

Your solution works very well, its not exactly as i wanted regarding the com function but thats a minor inconvenience (its used internally), the data i wanted the most was the correct invoice_month and your sql does the job perfectly.
I also already adapted the code to the real case i had and it works well ! Very Happy
I'll spend some time understanding your code now Smile i'm not good in the analytic department in oracle Razz

@Michel Cadot

Thank you! The comulated_total is exactly as i wanted it, not really needed atm but it may come in handy in the future!
However the flag idea is a good one to indicate when the amount is actually reached (good thinking there) but its not the thing i was looking for, i needed to flag all the months that contributed in reaching the amount flagged with the month in which it was actually met.
I'll most certainly study your code aswell, you did use some syntax i never used nor seen before Smile
Re: Query help with cumulative sum to be broken into sections [message #366246 is a reply to message #366243] Fri, 12 December 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Regrouping each month involve in an invoice number:
SQL> select id, month, usd, cum_tot, invoice_nb, flag
  2  from test_fatt
  3  model
  4    partition by (id)
  5    dimension by (month)
  6    measures (usd, 0 cum_tot, 0 invoice_nb, 0 flag)
  7    rules update sequential order (
  8      cum_tot[ANY] order by month = 
  9        case when nvl(cum_tot[cv()-1],0) >= 10
 10          then usd[cv()] 
 11          else nvl(cum_tot[cv()-1],0)+usd[cv()] 
 12        end,
 13      invoice_nb[ANY] order by month =
 14        nvl(invoice_nb[cv()-1],1) + 
 15        case when nvl(cum_tot[cv()-1],0) >= 10
 16          then 1 
 17          else 0
 18        end,
 19      flag[ANY] order by month = 
 20        case when cum_tot[cv()] >= 10 then 1 else 0 end
 21    )
 22  order by 1, 2
 23  /
ID              MONTH        USD    CUM_TOT INVOICE_NB       FLAG
---------- ---------- ---------- ---------- ---------- ----------
A                   1          3          3          1          0
A                   2          5          8          1          0
A                   3          2         10          1          1
A                   4          8          8          2          0
A                   5         13         21          2          1
A                   6          3          3          3          0

B                   1          2          2          1          0
B                   2         15         17          1          1
B                   3         23         23          2          1
B                   4          8          8          3          0
B                   5          3         11          3          1
B                   6          5          5          4          0

C                   1          5          5          1          0
C                   2         15         20          1          1
C                   3          7          7          2          0
C                   6          2          2          1          0

D                   1         12         12          1          1
D                   2          5          5          2          0
D                   3          3          8          2          0
D                   4          4         12          2          1
D                   5          2          2          3          0
D                   6          8         10          3          1


22 rows selected.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #366248 is a reply to message #366246] Fri, 12 December 2008 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! this does not work when some months are missing, here is a fixed one:
SQL> break on id dup skip 1
SQL> select id, month, usd, cum_tot, invoice_nb, flag
  2  from ( select id, month, usd, 
  3                 row_number() over (partition by id order by month) rn
  4         from test_fatt
  5        )
  6  model
  7    partition by (id)
  8    dimension by (rn)
  9    measures (month, usd, 0 cum_tot, 0 invoice_nb, 0 flag)
 10    rules update sequential order (
 11      cum_tot[ANY] order by month = 
 12        case when nvl(cum_tot[cv()-1],0) >= 10
 13          then usd[cv()] 
 14          else nvl(cum_tot[cv()-1],0)+usd[cv()] 
 15        end,
 16      invoice_nb[ANY] order by month =
 17        nvl(invoice_nb[cv()-1],1) + 
 18        case when nvl(cum_tot[cv()-1],0) >= 10
 19          then 1 
 20          else 0
 21        end,
 22      flag[ANY] order by month = 
 23        case when cum_tot[cv()] >= 10 then 1 else 0 end
 24    )
 25  order by 1, 2
 26  /
ID              MONTH        USD    CUM_TOT INVOICE_NB       FLAG
---------- ---------- ---------- ---------- ---------- ----------
A                   1          3          3          1          0
A                   2          5          8          1          0
A                   3          2         10          1          1
A                   4          8          8          2          0
A                   5         13         21          2          1
A                   6          3          3          3          0

B                   1          2          2          1          0
B                   2         15         17          1          1
B                   3         23         23          2          1
B                   4          8          8          3          0
B                   5          3         11          3          1
B                   6          5          5          4          0

C                   1          5          5          1          0
C                   2         15         20          1          1
C                   3          7          7          2          0
C                   6          2          9          2          0

D                   1         12         12          1          1
D                   2          5          5          2          0
D                   3          3          8          2          0
D                   4          4         12          2          1
D                   5          2          2          3          0
D                   6          8         10          3          1


22 rows selected.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #376196 is a reply to message #366248] Tue, 16 December 2008 08:04 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
Thanks again and sorry for the delay

@JRowbottom

Thanks for your code, however i noticed a serious problem.
Lets consider the case in which for company A you have

month - usd
1 18
2 2
3 2
4 2

Correct result would be 18 in month 1 , and for the following months no results (since 2+2+2 is 6 so not over 10)
However with the current code you'll have that the 2nd month reach 20 (20/10 = 2 so the code consider it a valid) and so get invoiced not considering that in the first month you invoiced 18, not 10 !

Not sure if you got what the problem is Razz

@Michel Cadot

Michel thank you, this is the first time i see the MODEL clause in action but i have the following problems:

1) It wont execute in my enviroment, neither with sql+ nor with toad
2) Even if it executed you work perfectly the cumulative total broken out when they reach 10, but the flag part is really not what i was looking for since previous month that contributed need to be considered aswell (with the month that made the sum top 10)


Re: Query help with cumulative sum to be broken into sections [message #376201 is a reply to message #376196] Tue, 16 December 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) "It wont execute in my enviroment, neither with sql+ nor with toad" this does not help us to know why it does not work and help you

2) See "INVOICE_NB" column, I think this is what you are searching. Otherwise explain what you want.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #376207 is a reply to message #376201] Tue, 16 December 2008 08:28 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
2) sorry Michel Smile you're perfectly right

1) I'd like to know why it doesnt work it aswell!
This is what i get in SQL PLUS

SQL*Plus: Release 10.1.0.2.0 - Production on Mar Dic 16 15:26:55 2008

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connesso a:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SP2-0042: comando sconosciuto "partition" - il resto della riga è stato ignorato.


May it be that this particular DB is of older oracle version and doesnt support this commands?
Re: Query help with cumulative sum to be broken into sections [message #376208 is a reply to message #376207] Tue, 16 December 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Connesso a:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

MODEL clause is only available from 10g.

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #376217 is a reply to message #376208] Tue, 16 December 2008 08:47 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
Thanks michel
I'd update the previous threads if i knew how to do it Razz
so MODEL is out of the question
Sorry if i wasted your time, i still hope someone else may benefit from this.

@JRowbottom

I'm providing a new little test case to see the problems

drop table test_fatt;
create table test_fatt (id VARCHAR2(10),month NUMBER,usd NUMBER) ;


INSERT INTO test_fatt VALUES ('A', 1, 3);
INSERT INTO test_fatt VALUES ('A', 2, 5);
INSERT INTO test_fatt VALUES ('A', 3, 2);
INSERT INTO test_fatt VALUES ('A', 4, 8);
INSERT INTO test_fatt VALUES ('A', 5, 13);
INSERT INTO test_fatt VALUES ('A', 6, 3);
INSERT INTO test_fatt VALUES ('B', 1, 2);
INSERT INTO test_fatt VALUES ('B', 2, 15);
INSERT INTO test_fatt VALUES ('B', 3, 23);
INSERT INTO test_fatt VALUES ('B', 4, 8);
INSERT INTO test_fatt VALUES ('B', 5, 3);
INSERT INTO test_fatt VALUES ('B', 6, 5);
INSERT INTO test_fatt VALUES ('C', 1, 5);
INSERT INTO test_fatt VALUES ('C', 2, 15);
INSERT INTO test_fatt VALUES ('C', 3, 7);
INSERT INTO test_fatt VALUES ('C', 6, 2);
INSERT INTO test_fatt VALUES ('D', 1, 12);
INSERT INTO test_fatt VALUES ('D', 2, 5);
INSERT INTO test_fatt VALUES ('D', 3, 3);
INSERT INTO test_fatt VALUES ('D', 4, 4);
INSERT INTO test_fatt VALUES ('D', 5, 2);
INSERT INTO test_fatt VALUES ('D', 6, 8);
INSERT INTO test_fatt VALUES ('E', 1, 38.2);
INSERT INTO test_fatt VALUES ('E', 2, 9.9);
INSERT INTO test_fatt VALUES ('E', 3, 9.2);
INSERT INTO test_fatt VALUES ('E', 4, 7.4);
INSERT INTO test_fatt VALUES ('E', 5, 2.7);
INSERT INTO test_fatt VALUES ('E', 6, 8.6);

INSERT INTO test_fatt VALUES ('F', 1, 8.2);
INSERT INTO test_fatt VALUES ('F', 2, 9.9);
INSERT INTO test_fatt VALUES ('F', 3, 9.2);
INSERT INTO test_fatt VALUES ('F', 4, 7.4);
INSERT INTO test_fatt VALUES ('F', 5, 2.7);
INSERT INTO test_fatt VALUES ('F', 6, 8.6);
commit;


I'm using the code you posted

select id
      ,month
      ,usd
      ,cum_tot
      ,min(inv_month) over 
         (partition by id order by month 
          rows between current row and unbounded following) x
from (select id
            ,month
            ,usd
            ,cum_tot
            ,case when nvl(lag(tens) over (partition by id order by month),tens) 
                != tens then month end inv_month
      from (select id
                  ,month
                  ,usd
                  ,sum(usd) over (partition by id order by month) cum_tot
                  ,floor(sum(usd) over (partition by id order by month)/10) tens
            from test_fatt
     )     )
order by id,month;


And here is the results

ID,MONTH,USD,CUM_TOT,X
A,1,3,3,3
A,2,5,8,3
A,3,2,10,3
A,4,8,18,5
A,5,13,31,5
A,6,3,34,null
B,1,2,2,2
B,2,15,17,2
B,3,23,40,3
B,4,8,48,5
B,5,3,51,5
B,6,5,56,null
C,1,5,5,2
C,2,15,20,2
C,3,7,27,null
C,6,2,29,null
D,1,12,12,3  -> SHOULD BE 1
D,2,5,17,3   -> SHOULD BE 4
D,3,3,20,3   -> SHOULD BE 4 
D,4,4,24,6   -> SHOULD BE 4
D,5,2,26,6
D,6,8,34,6
E,1,38,2,38,2,2 -> SHOULD BE 1
E,2,9,9,48,1,2  -> SHOULD BE 3
E,3,9,2,57,3,3
E,4,7,4,64,7,4  -> SHOULD BE 5
E,5,2,7,67,4,6  -> SHOULD BE 5
E,6,8,6,76,6    -> SHOULD BE NULL
F,1,8,2,8,2,2
F,2,9,9,18,1,2
F,3,9,2,27,3,3  -> SHOULD BE 4
F,4,7,4,34,7,4
F,5,2,7,37,4,6
F,6,8,6,46,6


As you can see it seems there are some problem with decimals and most strange when the very first record for a company alone can surpass the 10 threshold

[Updated on: Tue, 16 December 2008 08:51] by Moderator

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #376218 is a reply to message #376217] Tue, 16 December 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please align the columns in the result.
There it is unreadable.
And keep your lines in 80 characters width.

Regards
Michel

[Updated on: Tue, 16 December 2008 08:50]

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #376638 is a reply to message #376218] Thu, 18 December 2008 02:45 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
Since there is still not a solution (nor we're close to one in this enviroment: oracle 9.1) i still accept any ideas, it is fine also to do something that can be run in a procedure/package or also use temporary tables and do the thing in more than one passage.

@michel

When you told me to look for invoice_nb it does the job of showing what invoice it is but not in what months it has been issued.
For instance for ID A, month 1-2-3 it is invoice_nb 1 but what i need its to be shown as "invoiced in month 3"



Here you go with the Fixed colums expected ideal results
(i have no idea how to paste here with fixed columms, is is fine in the editor but in the preview its screwed up.
i'll upload the results in a txt in case here doesnt work):

What is important is having the "invoice_month" column correct, the cumulative_invoice is not important

ID MONTH USD CUMULATIVE_INVOICE INVOICE_MONTH
A 1 3 3 3
A 2 5 8 3
A 3 2 10 3
A 4 8 8 5
A 5 13 21 5
A 6 3 3 null
B 1 2 2 2
B 2 15 17 2
B 3 23 23 3
B 4 8 8 5
B 5 3 11 5
B 6 5 5 null
C 1 5 5 2
C 2 15 20 2
C 3 7 7 null
C 6 2 9 null
D 1 12 12 1
D 2 5 5 4
D 3 3 8 4
D 4 4 12 4
D 5 2 2 6
D 6 8 10 6
E 1 38,2 38,2 1
E 2 9,9 9,9 3
E 3 9,2 19,1 3
E 4 7,4 7,4 5
E 5 2,7 10,1 5
E 6 8,6 8,6 null
F 1 8,2 8,2 2
F 2 9,9 18,1 2
F 3 9,2 9,2 4
F 4 7,4 16,6 4
F 5 2,7 2,7 6
F 6 8,6 11,3 6

As usual any help is really appreciated
Re: Query help with cumulative sum to be broken into sections [message #376649 is a reply to message #376638] Thu, 18 December 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 16 December 2008 15:49
Please align the columns in the result.
There it is unreadable.
And keep your lines in 80 characters width.

Regards
Michel



Quote:
When you told me to look for invoice_nb it does the job of showing what invoice it is but not in what months it has been issued.

And what FLAG does?

Regards
Michel
Re: Query help with cumulative sum to be broken into sections [message #376654 is a reply to message #376649] Thu, 18 December 2008 03:20 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
According to your schema the flag columm flags the month in which the limit has been surpassed, it does not flag all the months that contributed to surpassing the threshold nor it immediatly shows which month it is.

Now i do agree that having exactly the result you provided (so the use of invoice_nb and the flag columm) with some adjustment can be workable using your result as a starting point for what i really need.

But since the code you kindly provided cant be used in this case i see no point in discussing it further, if you do have ideas to provide the exact same results without using the MODEL clause however i'm all hears but consider the final result i'm looking for is different =)

As usual i thank you for your input and contribution
Re: Query help with cumulative sum to be broken into sections [message #376658 is a reply to message #376638] Thu, 18 December 2008 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should fix the month 1 invoice month problem.
select id
      ,month
      ,usd
      ,cum_tot
      ,min(inv_month) over 
         (partition by id order by month 
          rows between current row and unbounded following) x
from (select id
            ,month
            ,usd
            ,cum_tot
            ,case when nvl(lag(tens) over (partition by id order by month),0) 
                         != tens then month end inv_month
      from (select id
                  ,month
                  ,usd
                  ,sum(usd) over (partition by id order by month) cum_tot
                  ,floor(sum(usd) over (partition by id order by month)/10) tens
            from test_fatt
     )     )
order by id,month;
Re: Query help with cumulative sum to be broken into sections [message #376670 is a reply to message #376658] Thu, 18 December 2008 04:12 Go to previous messageGo to next message
acantoni
Messages: 9
Registered: December 2008
Junior Member
Thank you Jrowbottom! really

It does fix the 1st line problem,

It does not fix the other major problem, i attach the example to show it:

ID	MONTH	USD	CUM_TOT	X
D	1	12	12	1 ok
D	2	5	17	3 no should be 4!	
D	3	3	20	3 no! should be 4 see the problem? 
the cum_tot is 20 and so it get flagged but it shouldnt! 
first invoice was at 12, here we're at 8 (5+3) it shouldnt!                         
	
D	4	4	24	6 should be 4, because here 5+3+4 we surpass the threshold
	
D	5	2	26	6 ok	
D	6	8	34	6 ok

	
E	1	38,2	38,2	1 ok	
E	2	9,9	48,1	2 see? its < 10, it does not surpass 10, but since before we had 38.2
which is way higher than 10 it keeps flagging as surpasses. that should be 3	
E	3	9,2	57,3	3 ok	
E	4	7,4	64,7	4 we have still the same problem 	
E	5	2,7	67,4	6	
E	6	8,6	76	6


Basically when the script divide by 10 it doesnt count that it can be surpassed by a lot by previous results!

[Updated on: Thu, 18 December 2008 04:47]

Report message to a moderator

Re: Query help with cumulative sum to be broken into sections [message #377286 is a reply to message #376670] Mon, 22 December 2008 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The other problem is rather intractable - I don't know if it can be done without either Pl/Sql or the Model clause.

I've managed to get a partial success with this query:
select  id,month,usd,cum_tot,inv_month
from (
select id,month,usd,cum_tot,inv_month
      ,min(case when inv_month = 0 then null else inv_month end) over (partition by id,month) act_inv_month
from (      
select id,month,rw,usd,prev_usd,cum_tot
      ,sum(usd) over (partition by id,rw order by month rows between current row and rw following) x
      ,case when sum(usd) over (partition by id,rw order by month rows between current row and rw following) >= 10 then month+rw else 0 end inv_month
from (select id,month,usd
            ,sum(usd) over (partition by id order by  month) cum_tot
            ,nvl(lag(usd) over (partition by id order by month),0) prev_usd
      from    test_fatt)
     ,(select level-1 rw from dual connect by level <=4)
    ) 
    ) where inv_month = act_inv_month;


This works by taking each value and looking n months into the future and seeing when an invoice will need to be generated.
If you look through the results starting from month one it will tell you then next month that invoices will be generateed in. if you then move to that month+1, you can see the following invoice month, and so on.
I tried to create a Connect By loop version of the query to implement this logic, but it seems to get into some sort of internal loop, and creates me an arbitrarily large Temp tablespace until I decide to kill it - it might work for you.
The query is of this form:
select * 
from (query goes here)
start with month=1
connect by prior inv_month+1 = month and prior id = id;
Re: Query help with cumulative sum to be broken into sections [message #377318 is a reply to message #366226] Mon, 22 December 2008 05:50 Go to previous message
acantoni
Messages: 9
Registered: December 2008
Junior Member
First time i'll be at the office i'll test this, thank you very much!

Model clause can't be used.. however Pl-SQL in a procedure is fine if with normal query cant be done Smile
I thank you again for your time and in case i wont be answering i wish you happy christmas and happy new year!

Previous Topic: help in the count
Next Topic: Executing stored procedure
Goto Forum:
  


Current Time: Thu Nov 07 16:01:08 CST 2024