1 2 Previous Next 25 Replies Latest reply: Aug 3, 2014 1:21 PM by 2720982 RSS

    reset running sum within group

    2720982

      Hello - I need a little help. I have a table of dates and I need to find out which dates fit into a span of 6 months and then I need to reset the span to start at 0 to find the next set of dates that fall into the next 6 months. I was able to find a function to get the date from the previous row and calculate the months between the 2 dates. What I'm having trouble with is totaling the months and resetting it to start the running sum over so that I can find which dates fall into the next 6 month grouping. Once I get that I can mark them and do a summary by group for counts. Any assitance is much appreciated. Thank you

       

      grpstartdatenextdtmnthsmnthsruntotwithin6
      11/29/20102/17/20100.61290322600
      12/17/20102/19/20100.06451612911
      12/19/20104/20/20102.03225806531
      14/20/20104/29/20100.29032258131
      14/29/20105/19/20100.67741935541
      15/19/20105/25/20100.19354838741
      15/25/20106/1/20100.22580645241
      16/1/20109/16/20103.48387096800
      29/16/201011/1/20101.51612903221
      211/1/201012/7/20101.19354838731
      212/7/20101/10/20111.09677419441
      21/10/20111/21/20110.3548387141
      21/21/20112/18/20110.90322580651
      22/18/20113/4/20110.54838709700
      33/4/20114/8/20111.12903225811
      34/8/20114/12/20110.12903225811
      34/12/20116/21/20112.29032258141
      36/21/20117/18/20110.90322580641
      37/18/20117/28/20110.32258064551
      37/28/20118/15/20110.58064516151
      38/15/20118/23/20110.25806451600
      38/23/20119/6/20110.45161290301
      39/6/20119/9/20110.09677419411
        • 1. Re: reset running sum within group
          Frank Kulash

          Hi

           

          Sorry, I can't figure out what you want.

           

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas.  Also post the results you want from that data, and an explanation of how you get those results from that data.

          Explain, using specific examples, how you get those results from that data.  Things that are very clear in your mind might be mysteries to other people.  For example, what you you mean by 'next 6 months"?  Is is based on startdt, or nextdt, or some combination of both?  Assuming it's based on startdt, and that the earliest startdt in January 29, 2010, does the "next 6 month" period start on July 1, 2010, or July 29, 2010, or on the first date after July 28, 2010, or something else?

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: reset running sum within group
            Solomon Yakobson

            You need to define 6 months. Its is 6 month periods starting with first start_date? Or it is calenadr (Jan - June/July - December) 6 month periods? And what if start_date and end_date are in different 6 month periods? And we are not going to type your data in - provide create table and insert data statements. Alos, unless you provide Oracle version, you'll get answer using latest Oracle version features.

             

            SY.

            • 3. Re: reset running sum within group
              Frank Kulash

              Hi,

               

              Just adding a little to this:

              Solomon Yakobson wrote:

               

              ... Alos, unless you provide Oracle version, you'll get answer using latest Oracle version features. ...

               

              Or perhaps OP will get an answer that is needlessly complicated and inefficient, because the person replying makes the opposite assumption, and doesn't use really helpful features available in OP's version.

              Or perhaps there will be no answer at all.

               

              But the case you mentioned is more likely.  People answering questions tend to be up-to-date regarding versions, and look for ways to exploit new features.

              • 4. Re: reset running sum within group
                Etbin

                One possible interpretation might be:

                 

                with

                dates as

                (select to_date('1/29/2010','mm/dd/yyyy') startdate from dual union all

                select to_date('2/17/2010','mm/dd/yyyy') from dual union all

                select to_date('2/19/2010','mm/dd/yyyy') from dual union all

                select to_date('4/20/2010','mm/dd/yyyy') from dual union all

                select to_date('4/29/2010','mm/dd/yyyy') from dual union all

                select to_date('5/19/2010','mm/dd/yyyy') from dual union all

                select to_date('5/25/2010','mm/dd/yyyy') from dual union all

                select to_date('6/1/2010','mm/dd/yyyy') from dual union all

                select to_date('9/16/2010','mm/dd/yyyy') from dual union all

                select to_date('11/1/2010','mm/dd/yyyy') from dual union all

                select to_date('12/7/2010','mm/dd/yyyy') from dual union all

                select to_date('1/10/2011','mm/dd/yyyy') from dual union all

                select to_date('1/21/2011','mm/dd/yyyy') from dual union all

                select to_date('2/18/2011','mm/dd/yyyy') from dual union all

                select to_date('3/4/2011','mm/dd/yyyy') from dual union all

                select to_date('4/8/2011','mm/dd/yyyy') from dual union all

                select to_date('4/12/2011','mm/dd/yyyy') from dual union all

                select to_date('6/21/2011','mm/dd/yyyy') from dual union all

                select to_date('7/18/2011','mm/dd/yyyy') from dual union all

                select to_date('7/28/2011','mm/dd/yyyy') from dual union all

                select to_date('8/15/2011','mm/dd/yyyy') from dual union all

                select to_date('8/23/2011','mm/dd/yyyy') from dual union all

                select to_date('9/6/2011','mm/dd/yyyy') from dual union all

                select to_date('9/9/2011','mm/dd/yyyy') from dual

                )

                select d.startdate,p.period_start,p.period_end

                  from dates d,

                       (select add_months(trunc(min_date,'yyyy'),6 * (level - 1)) period_start,

                               add_months(trunc(min_date,'yyyy'),6 * level) - 1 period_end

                          from (select min(startdate) min_date,max(startdate) max_date

                                  from dates

                               )

                        connect by level <= ceil(months_between(max_date,min_date) / 6)

                       ) p

                where d.startdate between p.period_start and p.period_end

                order by d.startdate

                 

                STARTDATEPERIOD_STARTPERIOD_END
                01/29/201001/01/201006/30/2010
                02/17/201001/01/201006/30/2010
                02/19/201001/01/201006/30/2010
                04/20/201001/01/201006/30/2010
                04/29/201001/01/201006/30/2010
                05/19/201001/01/201006/30/2010
                05/25/201001/01/201006/30/2010
                06/01/201001/01/201006/30/2010
                09/16/201007/01/201012/31/2010
                11/01/201007/01/201012/31/2010
                12/07/201007/01/201012/31/2010
                01/10/201101/01/201106/30/2011
                01/21/201101/01/201106/30/2011
                02/18/201101/01/201106/30/2011
                03/04/201101/01/201106/30/2011
                04/08/201101/01/201106/30/2011
                04/12/201101/01/201106/30/2011
                06/21/201101/01/201106/30/2011
                07/18/201107/01/201112/31/2011
                07/28/201107/01/201112/31/2011
                08/15/201107/01/201112/31/2011
                08/23/201107/01/201112/31/2011
                09/06/201107/01/201112/31/2011
                09/09/201107/01/201112/31/2011

                 

                Regards

                 

                Etbin

                • 5. Re: reset running sum within group
                  2720982

                  Hello everyone - my appologies, this is my first time using a help forum and oracle is rather new to me. I will put together test data as suggested and try to clarify everything a little better and repost to here as soon as I can. Thank you!

                   

                  Linda

                  • 6. Re: reset running sum within group
                    2720982

                    Hello again, I’m trying a different approach so hopefully I can explain the problem a little better this time (with test data). Originally I was trying to stay away from loops and cursors because there can be millions+ rows associated with this project and it was very time slow so I was trying to do a rolling sum on the month (below) until it was <= 6  and resetting to start summing the months again at the next line but I’m not having much luck with that so I’m going back to the beginning. However, if anyone thinks that’s still a possibility just let me know and I will post test data for that as well.

                    Anyway, I have a table of id's and dates. I need to know which dates fall into a 6 month bucket within each id starting from the first date..

                    ·        Each ID is a group and each group can have multiple 6 month buckets

                    ·        First date starting a 6 month bucket is marked as 0 (start)

                    ·        Every other date within 6 month bucket is marked as 1 (in 6 month)

                    ·        Any date that does not fall into a 6 month bucket isn’t marked

                    ·        A 6 month date bucket cannot be re-used.

                    ·        Once a date falls out of the previous 6 month bucket it becomes the start of the next bucket.

                    NOTE: the 6 month end date is 6 months from the srv_dt of the 6 month bucket and was only put there for my reference. In the end I will need to total these by id which I marked them with 1's and 0's. I will need to know which dates are bad and which ones are good (start = good, in 6 month = bad)

                    For example:

                    id

                    srv_dt

                    start

                    in 6 month

                     

                    6 month end date

                    aa

                    6/21/2012

                    0

                     

                     

                    12/20/2012

                    aa

                    10/26/2012

                     

                    1

                     

                     

                    aa

                    5/15/2013

                    0

                     

                     

                    11/14/2013

                    aa

                    11/1/2013

                     

                    1

                     

                     

                    aa

                    11/18/2013

                    0

                     

                     

                    5/17/2014

                    aa

                    11/29/2013

                     

                    1

                     

                     

                    aa

                    12/6/2013

                     

                    1

                     

                     

                    aa

                    12/13/2013

                     

                    1

                     

                     

                    aa

                    2/18/2014

                     

                    1

                     

                     

                    aa

                    3/7/2014

                     

                    1

                     

                     

                    aa

                    3/21/2014

                     

                    1

                     

                     

                    aa

                    4/4/2014

                     

                    1

                     

                     

                    aa

                    4/23/2014

                     

                    1

                     

                     

                    aa

                    5/7/2014

                     

                    1

                     

                     

                    aa

                    5/20/2014

                    0

                     

                     

                    11/19/2014

                    aa

                    5/28/2014

                     

                    1

                     

                     

                    aa

                    5/30/2014

                     

                    1

                     

                     

                    aa

                    6/6/2014

                     

                    1

                     

                     

                    aa

                    6/25/2014

                     

                    1

                     

                     

                     

                    CREATE TABLE F1

                    (ID INT,

                    SRV_DT VARCHAR(2));

                    INSERT INTO F1 (ID,SRV_DT) VALUES ('aa',TO_DATE('10/31/2011','MM/DD/YYYY'));

                    Insert into f1 (ID,SRV_DT) values ('bb',to_date('06/21/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('10/26/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('05/15/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('11/01/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('11/18/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('11/29/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('12/06/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('12/13/2013','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('02/18/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('03/07/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('03/21/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('04/04/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('04/23/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('05/07/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('05/20/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('05/28/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('05/30/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('06/06/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('bb',to_date('06/25/2014','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('01/29/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('02/17/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('02/19/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/20/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/29/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('05/19/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('05/25/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('06/01/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('09/16/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('11/01/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('12/07/2010','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('01/10/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('01/21/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('02/18/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('03/04/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/08/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/12/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('06/21/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('07/18/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('07/28/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('08/15/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('08/23/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('09/06/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('09/09/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('10/21/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('10/26/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('11/02/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('11/18/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('12/02/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('12/09/2011','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('01/20/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('01/30/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('02/06/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('02/17/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('03/05/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('03/20/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('03/26/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/05/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/17/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('04/25/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('05/22/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('05/30/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('06/18/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('10/12/2012','MM/DD/YYYY'));

                    Insert into F1 (ID,SRV_DT) values ('dd',to_date('11/28/2012','MM/DD/YYYY'));

                     

                    thanks in advance for your assistance!

                    Linda

                    • 7. Re: reset running sum within group
                      2720982

                      btw the first id/date (aa) in the test data won't have a result becuase there is only one record and no other dates to compare to, that won't ever be the case, there will always be more than one record whether it falls into the 6 month bucket or not.
                      Thanks again!

                      Linda

                      • 8. Re: reset running sum within group
                        2720982

                        Oh and I forgot to metion we are using oracle 11g

                        • 9. Re: reset running sum within group
                          Frank Kulash

                          Hi, Linda,

                           

                          Thanks for posting the CREATE TABLE and INSERT statements.  I know it can be a lot of work, but it's really important.  If we're going to work together, then we need the same tables so we can get the same results.

                           

                          Now that I have a copy of your table, I promise that I will test any code I write for you, and make sure it works before I post it.  Will you do the same?  When I run the CREATE TABLE ansd INSERT statements you posted, I get errors on all of the INSERT statements.  That's because id is defined as a NUMBER, but all the INSERT statements give string values, such as 'aa', for that column.  Likewise, srv_date is defined as VARCHAR2 (2), but you're trying to put DATEs into that column.

                           

                          Are you sure the results you posted are what you want from the sample data you posted?  Why are there only 19 rows of output, when there are 65 rows of data?  Why do all of the output rows have id='aa', when only 1 of the data rows has that value?  You need to post the exact results that you want from the given data.

                          More data doesn't always mean a better example.  In this case, I'll bet you can show what you want very well with only 20 rows of data, or less.

                           

                          There's no version 11f or 11h, so it's rather silly to say to have 11g.  Always give your exact version, such as 11.2.0.3.0.  There are some functions (and other things) that work in 11.2.0.3.0 that don't work in 11.1.0.6.0.  (They may not matter in this particular problem, however.)

                          • 10. Re: reset running sum within group
                            Frank Kulash

                            Hi,

                            2720982 wrote:

                             

                            btw the first id/date (aa) in the test data won't have a result becuase there is only one record and no other dates to compare to, that won't ever be the case, there will always be more than one record whether it falls into the 6 month bucket or not.
                            Thanks again!

                            Linda

                            Sorry, I don't understand this.  Won't every row fall into some bucket?  That is, if a row doesn't fit into an existing bucket, either because it has a different id or its srv_dt is too late, won't it start a new bucket?

                            • 11. Re: reset running sum within group
                              2720982

                              Hi Frank - yes every row(date) should fall into a bucket, I'm just trying to figure out which 6 month bucket it falls into.

                              • 12. Re: reset running sum within group
                                2720982

                                It has to be ordered by id then date asc. Each ID represents a group of lets say "people" They are only allowed a date every 6 months so I need to find out if the "person" has more than one date every 6 months. Hope that helps

                                 


                                Thanks

                                Linda

                                • 14. Re: reset running sum within group
                                  2720982

                                  I shouldn't have included the aa(group) record in the test data, that was an accident and I didn't not notice it until after I had posted it. for each group their is always more than 1 record.

                                   


                                  1 2 Previous Next