3 Replies Latest reply: Feb 18, 2013 6:25 AM by 991674 RSS

    Grouping the data weekly - given date range

    991674
      Hi,
      I want to display a data in date range with week of month.

           Week     
      01-01-13 to 07-01-13
      08-01-13 to 14-01-13     
      15-01-13 to 20-01-13     

      Here my date range is start date=01-01-13 and end date 20-01-13

      I am trying by adding 7 days to start date and I am getting the results as

           Week     
      01-01-13 to 07-01-13
      08-01-13 to 14-01-13     
      15-01-13 to 21-01-13     

      Please suggest me how to get the required results using simple SQL.

      Thanks in Advance,
      KB
        • 1. Re: Grouping the data weekly - given date range
          jeneesh
          The below will give you ranges
          with dates as
          (
          select to_date('01-01-2013','dd-mm-yyyy') from_dt,
                 to_date('20-01-2013','dd-mm-yyyy') to_dt
          from dual       
          )
          select from_dt+(level-1)*7 from_dt,
                 least(from_dt+(level*7)-1,to_dt) to_dt
          from dates
          connect by from_dt+(level-1)*7 <= to_dt;
          
          FROM_DT   TO_DT   
          --------- ---------
          01-JAN-13 07-JAN-13 
          08-JAN-13 14-JAN-13 
          15-JAN-13 20-JAN-13 
          If you want something else, please provide sample data(CREATE TABLE and INSERT statements), expected output and your db version..

          Edited by: jeneesh on Feb 18, 2013 5:38 PM
          • 2. Re: Grouping the data weekly - given date range
            793996
            Is this what you looking for?

            WITH T AS
            (select to_date('01/01/2013', 'MM/DD/YYYY') FROM_DT, to_date('01/20/2013', 'MM/DD/YYYY') TO_DT from dual)
            SELECT --FROM_DT, TO_DT, LEVEL M_WEEK,
            FROM_DT + ((LEVEL - 1) * 7) NEW_FROM,
            LEAST(FROM_DT + ((LEVEL) * 7), TO_DT ) NEW_TO
            FROM T
            CONNECT BY LEVEL <= CEIL((TO_DT - FROM_DT)/7)

            If no post some sample data and desired output.

            Thanks,
            Vivek
            • 3. Re: Grouping the data weekly - given date range
              991674
              Thanks for your valuable suggestions.
              Now I am able to get the required results.