Results 1 to 9 of 9

Thread: Happy New Year

  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile Unanswered: !!! Happy New Year !!!

    !!! Happy New Year to All of You !!!

    You can use this DB2 calendar:

    Code:
    with 
    last_day(last_day) as 
    (select date('2009-12-31') from sysibm.sysdummy1 )
    ,
    calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
    (select last_day, varchar('', 3), int(0) from last_day
    union all
    select last_day + (clnd_day_no + 1) day, 
    substr('SunMonTueWedThuFriSat', 
              3 * (dayofweek(last_day + (clnd_day_no + 1) day) - 1) + 1 , 3),
    clnd_day_no + 1
    from calendar, last_day
    where clnd_day_no  <= 365 
    and
    year(last_day + (clnd_day_no + 1) day) <= 2010
    )
    select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
    from calendar, last_day
    where year(clnd_day) > year(last_day)
    Lenny
    Last edited by Lenny77; 12-29-09 at 12:42.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile Another DB2 calendar

    This calendar also works:

    Code:
    with 
    last_day(last_day) as 
    (select current date - dayofyear(current date) days + 1 year 
       from sysibm.sysdummy1 )
    ,
    calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
    (select last_day, varchar('', 3), int(0) from last_day
    union all
    select clnd_day + 1 day, 
    substr('SunMonTueWedThuFriSat', 
              3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
    clnd_day_no + 1
    from calendar
    where clnd_day_no  <= 366 
    )
    select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
    from calendar, last_day
    where year(clnd_day) = year(last_day) + 1
    Lenny
    Last edited by Lenny77; 12-29-09 at 18:42.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can join me with yours New Year wishes and yours calendar queries....

    Lenny

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up The first day of the New Year

    !!! Holiday !!!

    Code:
    select 
    current date - (dayofyear(current date) - 1)  days + 1 year 
    from sysibm.sysdummy1
    Lenny
    Last edited by Lenny77; 12-29-09 at 14:31.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    С Наступающим Новым Годом!

    Happy New Year!

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Universal Calendar Query

    This calendar will change only on the last day of the current year:

    Code:
    with 
    last_day(last_day) as 
    (
    select 
    case 
    when month(current date) = 12 and day(current date) = 31 then  
       current date - dayofyear(current date) days + 1 year 
    else 
       current date - dayofyear(current date) days 
    end
    from sysibm.sysdummy1 
    )
    ,
    calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
    (
    select last_day, varchar('', 3), int(0) from last_day
    union all
    select clnd_day + 1 day, 
    substr('SunMonTueWedThuFriSat', 
              3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
    clnd_day_no + 1
    from calendar
    where clnd_day_no  <= 366 
    )
    select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
    from calendar, last_day
    where year(clnd_day) = year(last_day) + 1
    Lenny

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Calendar with some Holidays

    Calendar with some Holidays.
    You can add your own holidays, using the same logics:


    Code:
    with 
    last_day(last_day) as 
    (select 
    case 
    when month(current date) = 12 and day(current date) = 30 then  
       current date - dayofyear(current date) days + 1 year 
    else 
       current date - dayofyear(current date) days 
    end
    from sysibm.sysdummy1 
    )
    ,
    calendar(clnd_day, clnd_dayofweek, clnd_day_no, scheduling) as
    (select last_day, varchar('', 3), int(0), varchar('', 100) 
    from last_day
    union all
    select clnd_day + 1 day, 
    substr('SunMonTueWedThuFriSat', 
              3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
    clnd_day_no + 1,
    case 
      when (month(clnd_day + 1 day) = 1  and day(clnd_day + 1 day) = 1  )
      then 'New Year'
      when (month(clnd_day + 1 day) = 12 and day(clnd_day + 1 day) = 25)
      then 'Christmas Day'
      when (month(clnd_day + 1 day) = 7 and day(clnd_day + 1 day) = 4)
      then 'Independence Day'
      when (month(clnd_day + 1 day) = 11 and dayofweek(clnd_day + 1 day) = 5)
            and month(clnd_day + 8 day) = 12 
      then 'Thanksgiving Day'
      when (month(clnd_day + 1 day) = 2 and dayofweek(clnd_day + 1 day) = 2)
            and month(clnd_day - 6 day ) = 2 and month(clnd_day - 13 day) = 2 
            and month(clnd_day - 20 day) = 1  
      then 'President''s Day'
      when dayofweek(clnd_day + 1 day) in (2, 3, 4, 5, 6)
      then 'WeekDay'
      when dayofweek(clnd_day + 1 day) in (1, 7)
      then 'WeekEnd'
    end 
    from calendar
    where clnd_day_no  <= 366 
    )
    select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
    , scheduling
    from calendar, last_day
    where year(clnd_day) = year(last_day) + 1
    Lenny
    Last edited by Lenny77; 01-05-10 at 10:48.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    !!! Happy New "Old Russian" Year !!!
    Last edited by Lenny77; 01-14-10 at 12:01.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation

    ! Happy New Year 2011 !

    The calendar function which I have created 1 year ago still working without any corrections:

    Code:
    with 
    last_day(last_day) as 
    (select 
    case 
    when month(current date) = 12 and day(current date) = 30 then  
       current date - dayofyear(current date) days + 1 year 
    else 
       current date - dayofyear(current date) days 
    end
    from sysibm.sysdummy1 
    )
    ,
    calendar(clnd_day, clnd_dayofweek, clnd_day_no, scheduling) as
    (select last_day, varchar('', 3), int(0), varchar('', 100) 
    from last_day
    union all
    select clnd_day + 1 day, 
    substr('SunMonTueWedThuFriSat', 
              3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
    clnd_day_no + 1,
    case 
      when (month(clnd_day + 1 day) = 1  and day(clnd_day + 1 day) = 1  )
      then 'New Year'
      when (month(clnd_day + 1 day) = 12 and day(clnd_day + 1 day) = 25)
      then 'Christmas Day'
      when (month(clnd_day + 1 day) = 7 and day(clnd_day + 1 day) = 4)
      then 'Independence Day'
      when (month(clnd_day + 1 day) = 11 and dayofweek(clnd_day + 1 day) = 5)
            and month(clnd_day + 8 day) = 12 
      then 'Thanksgiving Day'
      when (month(clnd_day + 1 day) = 2 and dayofweek(clnd_day + 1 day) = 2)
            and month(clnd_day - 6 day ) = 2 and month(clnd_day - 13 day) = 2 
            and month(clnd_day - 20 day) = 1  
      then 'President''s Day'
      when dayofweek(clnd_day + 1 day) in (2, 3, 4, 5, 6)
      then 'WeekDay'
      when dayofweek(clnd_day + 1 day) in (1, 7)
      then 'WeekEnd'
    end 
    from calendar
    where clnd_day_no  <= 366 
    )
    select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
    , scheduling
    from calendar, last_day
    where year(clnd_day) = year(last_day) + 1
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •