Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Posts
    4

    Unhappy Unanswered: Need help with distinct Dates

    Hi

    I have a need to only find distinct dates in which a worker worked in the factory using SQL

    rownum workerstartDate workerenddate
    1 2012-08-08 2012-10-10
    2 2012-08-10 2012-08-31
    3 2012-09-05 2012-09-15
    4 2012-10-15 2012-12-19
    5 2013-01-02 2013-03-14
    6 2013-03-15 2013-05-23


    Basically, I am looking for the above to look like this

    rownum workerstartDate workerenddate
    1 2012-08-08 2012-10-10
    4 2012-10-15 2012-12-19
    5 2013-01-02 2013-03-14
    6 2013-03-15 2013-05-23




    Can you please help.

    Thanks,
    Hans
    Last edited by hansr; 09-04-14 at 11:44. Reason: updating workerid to rownum to avoid confusion

  2. #2
    Join Date
    Sep 2014
    Posts
    4
    We are removing the row 2 and 3 because the date range is inside the date range of row 1 i.e., 8-8-2012 to 10-10-2012

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Code:
    select * from workeddays w1
    where not exists (select rownum from workeddays w2
                      where w2.workerstartDate <= w1.workerstartDate
                      and   w2.workerenddate   >= w1.workerenddate
                      and   w2.rownum < w1.rownum);
    But what result do you want if two periods partly overlaps each other?

    E.g. add the two following rows:
    7 2014-01-02 2014-03-14
    8 2014-03-10 2014-03-20

    Do you want the two new rows to be merged? If that's the case, which rownum should be returned?

Posting Permissions

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