This discussion is archived
5 Replies Latest reply: Nov 19, 2012 7:27 AM by Solomon Yakobson RSS

using LEVEL keyword

user10403630 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points