SQL Doubt

[kimmybunn]

Doubt to write a SQL:

I am having a table,with different fields such as date,week_end ,month_end,year _end etc
depends on the date I need to update the flags of the reamaining fields
eg: if date is 30/12/2010
week_end(last working day of the week) ='Y'
month_end(last working day of month) ='Y'
year_end (last working day of year) ='Y'

This date always varies and depends on this date I need to set the flags to the remaining fields
As I know only the basics of DB2,please anyone give me the complete SQL for the above mentioned scenario


Thanks in advance
Kimmy

[cburnett]

Not sure if this is best way of doing it, but if your objective is to create a calendar table, the following might be what you are after:

create table days(dt date,
week_end char(1) generated as 
    (case dayofweek(dt)
          when 6 then 'Y'
          else            'N'
     end),
month_end char(1) generated as
   (case
         when month(dt) = month(date(dt) +
                      (case dayofweek(date(dt))
                            when 6 then 3
                            when 7 then 0
                            when 1 then 0
                            else            1
                       end) days) then 'N'
          else                              'Y'
    end),
year_end char(1) generated as
    (case
          when year(dt) = year(date(dt) +
                      (case dayofweek(date(dt))
                            when 6 then 3
                            when 7 then 0
                            when 1 then 0
                            else            1
                       end) days) then 'N'
           else                             'Y'
     end))

Populate it with test data as follows and see if it's what you are after:

insert into days(dt)
  with i(lvl) as 
        (values(1)),
         n(lvl) as (select lvl
                        from i
                      union all
                      select n.lvl+1
                        from n
                      where n.lvl < 800)
  select current date + lvl days from n