3 Replies Latest reply: Jan 18, 2013 2:52 AM by Karthick_Arp RSS

    Need a query for grouping

    shahid_ahmed_patel
      Hi,

      I have some data and need to group it based on date. please see the below data

      pkey--------from_date----------to_date------------amount-------------
      1 ---------8-aug-12 ----------31-aug-12---------120
      1 ---------31-aug-12 --------1-sep-12--------- 130
      1 ---------1-sep-12 ----------2-sep-12--------- 150
      1 ---------3-sep-12 ----------4-sep-12--------- 150
      1 ---------5-sep-12 ----------7-sep-12--------- 100
      1 ---------7-sep-12 ----------8-sep-12--------- 200
      1 ---------8-sep-12 ----------20-sep-12---------120
      1 ---------20-sep-12 --------1-oct-12--------- 130
      1 ---------1-oct-12 ----------8-oct-12--------- 150

      and so on.....

      I need to group the data when a month finished.. e.g row 1 from_date is 08-aug-12 row 6 to_date is 08-sep-12 means almost 1 month finished so it should group and show the sum of amount with min(from_date) and max(to_Date) and total amount monhtly basis.

      I am trying to make a query since many hours but no success.. the data is huge with many keys and dates ... please share your idea/tips/feedback.

      Thanks

      Edited by: hard_stone on Jan 18, 2013 11:38 AM
        • 1. Re: Need a query for grouping
          Paul  Horth
          hard_stone wrote:
          Hi,

          I have some data and need to group it based on date. please see the below data

          pkey--------from_date----------to_date------------amount-------------
          1 ---------8-aug-12 ----------31-aug-12---------120
          1 ---------31-aug-12 --------1-sep-12--------- 130
          1 ---------1-sep-12 ----------2-sep-12--------- 150
          1 ---------3-sep-12 ----------4-sep-12--------- 150
          1 ---------5-sep-12 ----------7-sep-12--------- 100
          1 ---------7-sep-12 ----------8-sep-12--------- 200
          1 ---------8-sep-12 ----------20-sep-12---------120
          1 ---------20-sep-12 --------1-oct-12--------- 130
          1 ---------1-oct-12 ----------8-oct-12--------- 150

          and so on.....

          I need to group the data when a month finished.. e.g row 1 from_date is 08-aug-12 row 6 to_date is 08-sep-12 means almost 1 month finished so it should group and show the sum of amount with min(from_date) and max(to_Date) and total amount monhtly basis.

          I am trying to make a query since many hours but no success.. the data is huge with many keys and dates ... please share your idea/tips/feedback.

          Thanks

          Edited by: hard_stone on Jan 18, 2013 11:38 AM
          Please read {message:id=9360002} and follow the advice there (though after 244 posts you should know how to do this).

          In particular provide create table statements, insert statements with example data, the expected output from that sample data
          and a clear explanation of how that output relates to the input. Help us to help you.
          • 2. Re: Need a query for grouping
            avish16
            Plz check if the following helps you in any way -

            with test as (select 1 pkey ,to_date('8-aug-12','dd-mon-rrrr') from_date, to_date('31-aug-12','dd-mon-rrrr') "to_date", 120 amount from dual union all
            select 1 ,to_date('31-aug-12','dd-mon-rrrr'),to_date('1-sep-12','dd-mon-rrrr'), 130 from dual union all
            select 1 ,to_date('1-sep-12','dd-mon-rrrr') ,to_date('2-sep-12','dd-mon-rrrr'), 150 from dual union all
            select 1 ,to_date('3-sep-12' ,'dd-mon-rrrr') ,to_date('4-sep-12','dd-mon-rrrr'), 150 from dual union all
            select 1,to_date('5-sep-12','dd-mon-rrrr') ,to_date('7-sep-12','dd-mon-rrrr'), 100 from dual union all
            select 1 ,to_date('7-sep-12','dd-mon-rrrr') ,to_date('8-sep-12','dd-mon-rrrr'), 200 from dual union all
            select 1 ,to_date('8-sep-12','dd-mon-rrrr') ,to_date('20-sep-12','dd-mon-rrrr'),120 from dual union all
            select 1,to_date('20-sep-12','dd-mon-rrrr'),to_date('1-oct-12','dd-mon-rrrr'),130 from dual union all
            select 1 ,to_date('1-oct-12','dd-mon-rrrr') ,to_date('8-oct-12','dd-mon-rrrr'), 150 from dual )
            select min(from_date),max("to_date"),sum(amount)
            from test group by extract(month from from_date)

            Also please try not using column names like "to_date" which is used as a date function in oracle. Please ignore any semantic errors. :-)
            • 3. Re: Need a query for grouping
              Karthick_Arp
              Try this
              with t
              as
              (
              select 1 pkey, to_date('08-aug-12', 'dd-mon-rr') from_date, to_date('31-aug-12', 'dd-mon-rr') to_date, 120 amount from dual 
              union all
              select 2 pkey, to_date('31-aug-12', 'dd-mon-rr') from_date, to_date('01-sep-12', 'dd-mon-rr') to_date, 130 amount from dual 
              union all
              select 3 pkey, to_date('01-sep-12', 'dd-mon-rr') from_date, to_date('02-sep-12', 'dd-mon-rr') to_date, 150 amount from dual 
              union all
              select 4 pkey, to_date('03-sep-12', 'dd-mon-rr') from_date, to_date('04-sep-12', 'dd-mon-rr') to_date, 150 amount from dual 
              union all
              select 5 pkey, to_date('05-sep-12', 'dd-mon-rr') from_date, to_date('07-sep-12', 'dd-mon-rr') to_date, 100 amount from dual 
              union all
              select 6 pkey, to_date('07-sep-12', 'dd-mon-rr') from_date, to_date('08-sep-12', 'dd-mon-rr') to_date, 200 amount from dual 
              union all
              select 7 pkey, to_date('08-sep-12', 'dd-mon-rr') from_date, to_date('20-sep-12', 'dd-mon-rr') to_date, 120 amount from dual 
              union all
              select 8 pkey, to_date('20-sep-12', 'dd-mon-rr') from_date, to_date('01-oct-12', 'dd-mon-rr') to_date, 130 amount from dual 
              union all
              select 9 pkey, to_date('01-oct-12', 'dd-mon-rr') from_date, to_date('08-oct-12', 'dd-mon-rr') to_date, 150 amount from dual 
              )
              select min(from_date) from_date, max(to_date) to_date, sum(amount) amount
                from (
                      select pkey, from_date, to_date, amount, next_month
                        from t
                       model
                       dimension by (pkey)
                       measures (from_date, to_date, amount, to_date('19000101', 'yyyymmdd') next_month, add_months(from_date, 1) temp_month)
                       rules upsert
                       (
                         next_month[any] = case when next_month[cv(pkey)-1] >= to_date[cv(pkey)] then next_month[cv(pkey)-1]
                                                else temp_month[cv(pkey)] end
                       ) 
                     )
               group by next_month;
              
              FROM_DATE TO_DATE   AMOUNT
              --------- --------- ------
              08-AUG-12 08-SEP-12    850 
              08-SEP-12 08-OCT-12    400