Need Help in formating query

oracle India
  • 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

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Nine people can't make a baby in a month.” - Fred Brooks