1. Registered User
Join Date
Oct 2005
Posts
183

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. Registered User
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```

3. Registered User
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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```
???

5. Registered User
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)

6. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
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
•