5 Replies Latest reply: Nov 19, 2012 9:27 AM by Solomon Yakobson RSS

    using LEVEL keyword

    user10403630
      Hi,

      I have a table like below..
      CAL eff_date
      A 1/1/2012
      A 1/2/2012
      A 1/3/2012
      A 1/4/2012
      A 1/5/2012
      A 1/6/2012
      A 1/7/2012

      And now I need to somehow generate this weekly caledar for 1 year.(using sql) I tried using level.

      with t as
      (select level l from dual connect by level <=52),
      t1 as
      (SELECT c.cal,eff+l eff,perwgt,repeat,opt
      FROM cal c,t

      )
      select * from t1 order by eff

      Can anyone help me out how to add a seq no for each date. meaning all records should be added with 1 and then 2 and the 3... upto 52

      Thanks..
        • 1. Re: using LEVEL keyword
          BluShadow
          Can you please provide your data in a way we can use it (i.e. create table/insert statements) and give us an example of what you are expecting the output to look like, along with your database version which could influence what methods can be used.

          Read this: {message:id=9360002}

          Generating dates is not too tricky, it all depends what you want...
          SQL> break on month skip 1
          SQL> set linesize 200
          SQL> set pagesize 2000
          SQL> column month format a20
          SQL> column week format a4
          SQL> with req as (select '&Required_Year_YYYY' as yr from dual)
            2      ,offset as (select case when to_char(trunc(to_date(yr,'YYYY'),'YYYY'),'IW') in ('52','53') then 1 else 0 end as offset from req)
            3  select lpad( Month, 20-(20-length(month))/2 ) month,
            4         '('||week||')' as week, "Mo", "Tu", "We", "Th", "Fr", "Sa", "Su"
            5  from (
            6    select to_char(dt,'fmMonth YYYY') month,
            7    case when to_char(dt, 'mm') = '12' and to_char(dt,'iw') = '01' and offset = 0 then '53'
            8         when to_char(dt, 'mm') = '12' and to_char(dt,'iw') = '01' and offset = 1 then '54'
            9         when to_char(dt, 'mm') = '01' and to_char(dt,'iw') in ('52','53') then '1'
           10         else to_char(to_number(to_char(dt,'iw'))+offset) end as week,
           11    max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Mo",
           12    max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Tu",
           13    max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "We",
           14    max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "Th",
           15    max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Fr",
           16    max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Sa",
           17    max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Su"
           18    from ( select trunc(to_date(req.yr,'YYYY'),'y')-1+rownum dt
           19           from all_objects, req
           20           where rownum <= add_months(trunc(to_date(req.yr,'YYYY'),'y'),12) - trunc(to_date(req.yr,'YYYY'),'y') )
           21        ,offset
           22    group by to_char(dt,'fmMonth YYYY'),     case when to_char(dt, 'mm') = '12' and to_char(dt,'iw') = '01' and offset = 0 then '53'
           23                                                  when to_char(dt, 'mm') = '12' and to_char(dt,'iw') = '01' and offset = 1 then '54'
           24                                                  when to_char(dt, 'mm') = '01' and to_char(dt,'iw') in ('52','53') then '1'
           25                                                  else to_char(to_number(to_char(dt,'iw'))+offset) end
           26    ) x
           27  order by to_date( month, 'Month YYYY' ), to_number(x.week)
           28  /
          Enter value for required_year_yyyy: 2012
          old   1: with req as (select '&Required_Year_YYYY' as yr from dual)
          new   1: with req as (select '2012' as yr from dual)
          
          MONTH                WEEK Mo Tu We Th Fr Sa Su
          -------------------- ---- -- -- -- -- -- -- --
              January 2012     (1)                     1
                               (2)   2  3  4  5  6  7  8
                               (3)   9 10 11 12 13 14 15
                               (4)  16 17 18 19 20 21 22
                               (5)  23 24 25 26 27 28 29
                               (6)  30 31
          
             February 2012     (6)         1  2  3  4  5
                               (7)   6  7  8  9 10 11 12
                               (8)  13 14 15 16 17 18 19
                               (9)  20 21 22 23 24 25 26
                               (10) 27 28 29
          
               March 2012      (10)           1  2  3  4
                               (11)  5  6  7  8  9 10 11
                               (12) 12 13 14 15 16 17 18
                               (13) 19 20 21 22 23 24 25
                               (14) 26 27 28 29 30 31
          
               April 2012      (14)                    1
                               (15)  2  3  4  5  6  7  8
                               (16)  9 10 11 12 13 14 15
                               (17) 16 17 18 19 20 21 22
                               (18) 23 24 25 26 27 28 29
                               (19) 30
          
                May 2012       (19)     1  2  3  4  5  6
                               (20)  7  8  9 10 11 12 13
                               (21) 14 15 16 17 18 19 20
                               (22) 21 22 23 24 25 26 27
                               (23) 28 29 30 31
          
               June 2012       (23)              1  2  3
                               (24)  4  5  6  7  8  9 10
                               (25) 11 12 13 14 15 16 17
                               (26) 18 19 20 21 22 23 24
                               (27) 25 26 27 28 29 30
          
               July 2012       (27)                    1
                               (28)  2  3  4  5  6  7  8
                               (29)  9 10 11 12 13 14 15
                               (30) 16 17 18 19 20 21 22
                               (31) 23 24 25 26 27 28 29
                               (32) 30 31
          
              August 2012      (32)        1  2  3  4  5
                               (33)  6  7  8  9 10 11 12
                               (34) 13 14 15 16 17 18 19
                               (35) 20 21 22 23 24 25 26
                               (36) 27 28 29 30 31
          
             September 2012    (36)                 1  2
                               (37)  3  4  5  6  7  8  9
                               (38) 10 11 12 13 14 15 16
                               (39) 17 18 19 20 21 22 23
                               (40) 24 25 26 27 28 29 30
          
              October 2012     (41)  1  2  3  4  5  6  7
                               (42)  8  9 10 11 12 13 14
                               (43) 15 16 17 18 19 20 21
                               (44) 22 23 24 25 26 27 28
                               (45) 29 30 31
          
             November 2012     (45)           1  2  3  4
                               (46)  5  6  7  8  9 10 11
                               (47) 12 13 14 15 16 17 18
                               (48) 19 20 21 22 23 24 25
                               (49) 26 27 28 29 30
          
             December 2012     (49)                 1  2
                               (50)  3  4  5  6  7  8  9
                               (51) 10 11 12 13 14 15 16
                               (52) 17 18 19 20 21 22 23
                               (53) 24 25 26 27 28 29 30
                               (54) 31
          
          
          64 rows selected.
          
          SQL>
          • 2. Top-N Query
            Frank Kulash
            Hi,

            Sorry, it's very unclear what you want.

            Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}

            Maybe you want a Top-N Query , where you take the first N rows (N=5 in the example below) from the top of a sorted set (the set below is sorted by hirdeate):
            WITH     got_r_num   AS
            (
                 SELECT  empno, ename, hiredate
                 ,     ROW_NUMBER () OVER (ORDER BY hiredate)     AS r_num
                 FROM     scott.emp
            )
            SELECT       *
            FROM       got_r_num     r
            WHERE       r_num          <= 5
            ;
            Since I don't have a test version of your table, I used scott.emp (which I assume you have avaiable) to illustrate.
            • 3. Re: Top-N Query
              user10403630
              Thanks for your replies...let me put my qstn in this way.
              I have a table CAL which has 3 records.

              CAL : meaning 3 days of a week . Lets say my week start date is 1/10/2012 and end date 1/16/2012. Now I need to repeat this pattern i.e, 2nd,4th and 6th day of week for next 52 weeks.

              CAL DATE
              -----------------------------------
              A 1/11/2012
              A 1/13/2012
              A 1/15/2012


              Now I need to repeat this pattern for 52 times ( as 52 weeks for a year).some thing like below.

              CAL DATE
              -----------------------------------
              A 1/11/2012
              A 1/13/2012
              A 1/15/2012
              A 1/18/2012
              A 1/20/2012
              A 1/22/2012
              and so on for next 52 times.

              Thanks.
              • 4. Re: Top-N Query
                Solomon Yakobson
                Use:
                select  cal,
                        dt + (lvl - 1) * 7 dt
                  from  tbl,
                        (
                         select  level lvl
                           from  dual
                           connect by level <= 52
                        )
                  order by  lvl,
                            dt
                /
                For example:
                SQL> select  *
                  2    from  tbl
                  3  /
                
                C DT
                - ---------
                A 11-JAN-12
                A 13-JAN-12
                A 15-JAN-12
                
                SQL> select  cal,
                  2          dt + (lvl - 1) * 7 dt
                  3    from  tbl,
                  4          (
                  5           select  level lvl
                  6             from  dual
                  7             connect by level <= 52
                  8          )
                  9    order by  lvl,
                 10              dt
                 11  /
                
                C DT
                - ---------
                A 11-JAN-12
                A 13-JAN-12
                A 15-JAN-12
                A 18-JAN-12
                A 20-JAN-12
                A 22-JAN-12
                A 25-JAN-12
                A 27-JAN-12
                A 29-JAN-12
                A 01-FEB-12
                A 03-FEB-12
                .
                .
                .
                SY.
                • 5. Re: Top-N Query
                  Frank Kulash
                  Hi,
                  user10403630 wrote:
                  Thanks for your replies...let me put my qstn in this way.
                  I have a table CAL which has 3 records.
                  CAL : meaning 3 days of a week . Lets say my week start date is 1/10/2012 and end date 1/16/2012. Now I need to repeat this pattern i.e, 2nd,4th and 6th day of week for next 52 weeks.

                  CAL DATE
                  -----------------------------------
                  A 1/11/2012
                  A 1/13/2012
                  A 1/15/2012
                  Once again, post CREATE TABLE and INSERT statements.
                  Now I need to repeat this pattern for 52 times ( as 52 weeks for a year).some thing like below.

                  CAL DATE
                  -----------------------------------
                  A 1/11/2012
                  A 1/13/2012
                  A 1/15/2012
                  A 1/18/2012
                  A 1/20/2012
                  A 1/22/2012
                  and so on for next 52 times.
                  It looks like you want:
                  WITH     cntr     AS
                  (
                       SELECT     LEVEL - 1     AS weeks_away
                       FROM     dual
                       CONNECT BY     LEVEL <= 52
                  )
                  SELECT  cal.cal
                  ,     cal.dt + (7 * cntr.weeks_away)     AS dt
                  FROM            cal
                  CROSS JOIN     cntr
                  ;