Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Date ranges overlapping

    I've gone cold here. Dunno if I've had too little coffee - as I'm currently drinking some seriously wicked green tea - or whether my brain has locked down from yesterdays "bad eggs for lunch" experience.

    Anyway... I have database with a customer, for each customer is a related history table with assigned consultant.

    The assigned consultant table has information on consultant id, name, the start date of his assignment and the end date.

    I need to find all customers that currently have (or have had) two or more consultants actively assigned. In other words, I need to see if the start/end times overlap.

    At my current state, I'm just done.. i can't maintain the perspective... how do I do this?

  2. #2
    Join Date
    May 2002
    Posts
    299
    you can do it in a single query but you will have to derive the heck out of them. break it into 2 steps and it's a lot easier to digest.

    Code:
    declare @c table (cid int, sd datetime, ed datetime)
    
    declare @d datetime 
    set @d=getdate()
    insert @c select 1,@d-100,@d-50
    union all select 2,@d-300,@d-150
    union all select 3,@d-100,@d-150
    union all select 4,@d-200,@d-150
    union all select 5,@d-75,@d
    
    declare @cu table (custid int,cid int)
    insert @cu select 1,1
    union all select 1,2
    union all select 2,1
    union all select 1,5
    union all select 3,2
    union all select 4,4
    
    select c.*,x.*
    into #t
    from @c c 
    join @cu cu on c.cid=cu.cid
    join ( 
    select custid
    from @cu
    group by custid
    having count(*)>1) x on cu.custid=x.custid
    
    select t1.*
    from #t t1 join #t t2 on t1.cid!=t2.cid and ((t1.sd between t2.sd and t2.ed) or (t2.sd between t1.sd and t1.ed))
    
    drop table #t
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Holy smokes... lol.. yeah...

    Since I'm on the slow sides, I'll be doing most of the comparison work using a recordset and two arrays.

    The recordset will be based on a simple select query where customer_id equals i (i is an int in a for... next loop) and while the recordset is open, load row 1 into Array A, row 2 into Array B, do the compare... loop the process for the recordset until all rows have been traversed and then update the recordset with the next i (customer).

    Not very effecient, but as I'll end up building a front-end at somepoint anyway, might as wlel do it this way.

    Trin

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by oj_n
    you can do it in a single query but you will have to derive the heck out of them. break it into 2 steps and it's a lot easier to digest.

    Code:
    declare @c table (cid int, sd datetime, ed datetime)
     
    declare @d datetime 
    set @d=getdate()
    insert @c select 1,@d-100,@d-50
    union all select 2,@d-300,@d-150
    union all select 3,@d-100,@d-150
    union all select 4,@d-200,@d-150
    union all select 5,@d-75,@d
     
    declare @cu table (custid int,cid int)
    insert @cu select 1,1
    union all select 1,2
    union all select 2,1
    union all select 1,5
    union all select 3,2
    union all select 4,4
     
    select c.*,x.*
    into #t
    from @c c 
    join @cu cu on c.cid=cu.cid
    join ( 
    select custid
    from @cu
    group by custid
    having count(*)>1) x on cu.custid=x.custid
     
    select t1.*
    from #t t1 join #t t2 on t1.cid!=t2.cid and ((t1.sd between t2.sd and t2.ed) or (t2.sd between t1.sd and t1.ed))
     
    drop table #t
    Hi

    Forgive me if I missed smoething but isn't that an odd schema? What happens if consultant 4 does another job? The start and end time are not part of the consultant entity but a consultant-does-a-job-for-a-client entity aren't they?

    Would the below be more like it?

    Code:
     declare @c table (custid int, cid int, sd datetime, ed datetime)
    declare @d datetime 
     
    set @d=getdate()
    insert @c select 1,1,@d-100,@d-50
    union all select 1,2,@d-300,@d-150
    union all select 2,1,@d-100,@d-75
    union all select 3,2,@d-200,@d-150
    union all select 4,4,@d-75,@d
    union all select 4,5,@d-50,@d-25
     
    declare @cu table (custid int)
     
    insert @cu select 1
    union all select 2
    union all select 3
    union all select 4
    select * from @c
     
    SELECT DISTINCT Con1.*
    FROM @C Con1 Inner Join @C Con2 ON Con1.cid <> Con2.cid AND 
    Con1.custid = Con2.Custid
    WHERE Con1.sd < Con2.ed AND Con1.ed > Con2.sd
    ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    hi

    try this


    CREATE TABLE CUSTOMER
    (
    CUSTID SMALLINT IDENTITY NOT NULL,
    CUSTNAME VARCHAR(100)
    )
    GO
    CREATE TABLE CONSULTANT
    (
    CONSULTID SMALLINT IDENTITY NOT NULL,
    CONSULTNAME VARCHAR(100)
    )
    GO
    CREATE TABLE ASSIGNMENT
    (
    CUSTID SMALLINT NOT NULL,
    CONSULTID SMALLINT NOT NULL,
    STARTDATE SMALLDATETIME NOT NULL,
    ENDDATE SMALLDATETIME
    )

    /* Insert Sample Data */

    SELECT A.CUSTID,A.CONSULTID,A.STARTDATE,A.ENDDATE FROM assignmentTable A WHERE A.ENDDATE
    <= SOME(SELECT B.STARTDATE FROM assignmentTable B WHERE A.CUSTID = B.CUSTID)
    union
    SELECT A.CUSTID,A.CONSULTID,A.STARTDATE,A.ENDDATE FROM assignmentTable A WHERE A.startdate
    <= SOME(SELECT B.enddate FROM assignmentTable B WHERE A.CUSTID = B.CUSTID)
    Cheers....

    baburajv

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    For the same client you need to find the start time of one consultant between the start and end time of another, but watch out for equal start times as it can result in duplicates.
    Code:
    -- create some history
    declare @d datetime 
    set @d=getdate() - 20
    select consid=1,custid=1,@d+1 st,@d+5 et
    into #workhist
    union all select 1,2,@d+1,@d+6
    union all select 1,3,@d+1,@d+2
    union all select 2,1,@d+4,@d+6  -- overlap 1,1
    union all select 2,2,@d+5,@d+9  -- overlap 1,2 & 3,2
    union all select 2,3,@d+3,@d+5 
    union all select 3,2,@d+4,@d+8  -- overlap 1,2 
    union all select 4,3,@d+3,@d+5  -- equal 2,3
    
    -- now the select
    select a.consid, a.custid, b.consid, b.custid
    from #workhist a, #workhist b
    where a.custid=b.custid and a.consid!=b.consid  
      and (  (a.st > b.st and a.st<=b.et)
          or (a.st=b.st and a.consid>b.consid))
    order by 2,1
    
    -- Consultant on left is overlapping consultant on right --
    
    consid      custid      consid      custid      
    ----------- ----------- ----------- ----------- 
    2           1           1           1
    2           2           1           2
    2           2           3           2
    3           2           1           2
    4           3           2           3

Posting Permissions

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