Results 1 to 4 of 4
  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 10: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?

  4. #4
    Join Date
    Oct 2014
    Posts
    289
    Provided Answers: 7
    Realize this is an old post, but wanted to provide my findings. I'm sure there is a better way to do this.

    SELECT *
    FROM
    #TEMPDATES AS C
    EXCEPT (

    SELECT
    A.*

    FROM
    #TEMPDATES AS A CROSS JOIN #TEMPDATES AS B

    WHERE A.ROWNUM > B.ROWNUM
    AND A.Workenddate >= B.Workerstartdate
    AND B.Workenddate >= A.WorkerStartDate
    )

Posting Permissions

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