1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Finding individuel dates having date+2 days..

Discussion in 'SQL PL/SQL' started by avols143, Jan 26, 2011.

  1. avols143

    avols143 Guest

    Hi,

    I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

    Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

    If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

    seq date
    ------------------------------
    1 01.01.10

    2 05.01.10
    3 06.01.10

    4 10.01.10
    5 12.01.10

    6 13.01.10
    7 14.01.10
    8 15.01.10

    9 16.01.10
    10 18.01.10

    11 19.01.10
    12 20.01.10
    13 21.01.10

    14 23.01.10

    15 26.01.10
    16 27.01.10

    17 29.01.10
    18 31.01.10


    The result should be (Don't use Pl/Sql)

    seq date
    ------------------------------
    1 01.01.10
    2 05.01.10
    3 10.01.10
    4 13.01.10
    5 16.01.10
    6 19.01.10
    7 23.01.10
    8 26.01.10
    9 29.01.10

    After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

    I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

    You people can have another best idea to do this.

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This 'data' you present is confusing -- what does it represent? Are you looking for 'gaps' in the work history outside of weekends? How, exactly, is this table defined? You need to present much more information before anyone can help you.