Need Help in formating query

• • Reply

  13 years ago

  by krishna.a

  krishna

  , India

  Joined 13 years ago

  Hi I have the following query with me.

  Can one help in formatting this query as single query.

  Thanks for help.

   

   

  /* x days old : Creating Table for building the working days between given two dates*/

   

  /*Drop Table working_days;*/

  Create Table working_days As(

  Select

  cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') start_date, CURRENT_DATE end_date, (CURRENT_DATE - cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') )+1 no_of_days, no_of_days / 7 * 5 working_days /*excluding sat and sun*/, no_of_days mod 7 remain_days,

  Case

  When remain_days=0

  Then CURRENT_DATE-remain_days

  When remain_days In (1,2,3,4,5,6,7)

  Then CURRENT_DATE-remain_days+1

  End As check_date

  From krishna

  )with data primary index(start_date)

  ;

   

   

  /*drop table test ;*/

  Create Table test (

  start_date date,remain_days integer, check_date date, dow integer,working_days integer

  ) primary index(start_date)

  ;

  select * from test;

  Insert Into test

  Select start_date,remain_days, check_date, dayofweek(check_date),working_days

  From working_days

  ;

  /*Drop Table test1;*/

  Create Table test1 As (

  Select

  start_date,remain_days, check_date, dow,

  Case

  When remain_days=0

  And dow In (1,7)

  Then working_days+remain_days-1

  When remain_days=0

  And dow In (2,3,4,5,6)

  Then working_days+remain_days

  When remain_days=1

  And dow In (1,7)

  Then working_days+remain_days-1

  When remain_days=1

  And dow In (2,3,4,5,6)

  Then working_days+remain_days

  When remain_days=2

  And dow In (1,6)

  Then working_days+remain_days-1

  When remain_days=2

  And dow=7

  Then working_days+remain_days-2

  When remain_days=2

  And dow In (2,3,4,5)

  Then working_days+remain_days

  When remain_days=3

  And dow In (1,5)

  Then working_days+remain_days-1

  When remain_days=3

  And dow In (7 ,6)

  Then working_days+remain_days-2

  When remain_days=3

  And dow In (2,3,4)

  Then working_days+remain_days

  When remain_days=4

  And dow In (1,4)

  Then working_days+remain_days-1

  When remain_days=4

  And dow In (5,7 ,6)

  Then working_days+remain_days-2

  When remain_days=4

  And dow In (2,3)

  Then working_days+remain_days

  When remain_days=5

  And dow In (1,3)

  Then working_days+remain_days-1

  When remain_days=5

  And dow In (4,5,7 ,6)

  Then working_days+remain_days-2

  When remain_days=5

  And dow=2

  Then working_days+remain_days

  When remain_days=6

  And dow In (1,2)

  Then working_days+remain_days-1

  When remain_days=6

  And dow In (3,4,5,7 ,6)

  Then working_days+remain_days-2

  End As wd

  From test)

  with data

  Primary Index(start_date)

  ;

   

  Select a.start_date,a.end_date,c.wd

  From working_days a Left Join test b

  On a.start_date=b.start_date

  Left Join test1 c

  On b.start_date=c.start_date

  ;

   

  Regards,

  Krishna

  Report abuse