overlapping times-SQL_Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

overlapping times-SQL_Query

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-10 : 04:44:03

Hi Guys,
I am trying to do a query to get overlapping time sheets from the database.

We have Engineers who submit time sheets when they do a job in the field to get paid.
it has;"Travel_Start_Time", "On_site_Time", "Off_Site_Time", and
"Travel_End_Time".
These "time sheet entries", "Call Reference Number" and "related Engineer ID" are in the Call_Event Table.
This table is linked to the Personnel table which has engineer details.

How do i get about doing a query to obtain overlapping timesheets.(same call or different calls for the same engineer).

What is the approch i have to take.
I have done a report on crystal to get these data, in that i have done flags using loops to flag-up any overlapping timings.
Am i to take the same approch here....
Can somebody please help.....
Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-06-10 : 07:02:32

Can you give an example using data and desired output?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-10 : 08:02:10

[code]
create function dbo.overlaps (
@s1 datetime,
@e1 datetime,
@s2 datetime,
@e2 datetime )
returns bit
as
begin
return (
case
when not((@e1<@s2) or (@s1>@e2)) then 1
else 0
end )
end
[/code]

Jay White
{0}

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-11 : 04:15:21

Hi thanks for your replies,
just make it clear what exactly i want here i give u an example....
Job_Ref***Eng_ID***Travel_Start***On_Site***Off_Site***Travel_End
01 --------200------08.00----------08.30-----10.00------10.30
02 --------250------10.00----------10.30-----12.00------12.30
03 --------200------10.15----------11.00-----12.00------13.00
05 --------300------13.00----------13.30-----14.00------14.30
05 --------300------14.00----------14.30-----15.00------15.30

If u look at the record 01 and record 03 its the same Engineer but different jobs-(Timings are overlapping.(He has started the secound job for the day even before finishing the 1st one which is not possible))
Record 04 and 05--- same job and same engineer

i want a statment to go thru record
1)may be for the last week and flag up
2)Overlapping events like those two instances
3)it should be the same engineer all the time but could be different jobs or may be the same job.

i guess i have to loop through all the events and then flag what ever the overlapping events.
I am not really sure how to call the function been given above,
hope this example will help u to help me.
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-11 : 10:06:37

Declare @today datetime
Set @today = convert(nvarchar,getdate(),101)

Declare @testTbl table (job_ref int, engId int, travel_Start datetime, On_Site datetime, Off_Site datetime, travel_End datetime)
Insert Into @testTbl Select job_ref = 1, engId = 200, travel_Start = dateadd(mi,8*60,@today), On_Site = dateadd(mi,8.5*60,@today), Off_Site = dateadd(mi,10*60,@today), travel_End = dateadd(mi,10.5*60,@today)
Insert Into @testTbl Select job_ref = 2, engId = 250, travel_Start = dateadd(mi,10*60,@today), On_Site = dateadd(mi,10.5*60,@today), Off_Site = dateadd(mi,12*60,@today), travel_End = dateadd(mi,12.5*60,@today)
Insert Into @testTbl Select job_ref = 3, engId = 200, travel_Start = dateadd(mi,10.25*60,@today), On_Site = dateadd(mi,11*60,@today), Off_Site = dateadd(mi,12*60,@today), travel_End = dateadd(mi,13*60,@today)
Insert Into @testTbl Select job_ref = 5, engId = 300, travel_Start = dateadd(mi,13*60,@today), On_Site = dateadd(mi,13.5*60,@today), Off_Site = dateadd(mi,14*60,@today), travel_End = dateadd(mi,14.5*60,@today)
Insert Into @testTbl Select job_ref = 5, engId = 300, travel_Start = dateadd(mi,14*60,@today), On_Site = dateadd(mi,14.5*60,@today), Off_Site = dateadd(mi,15*60,@today), travel_End = dateadd(mi,15.5*60,@today)

Select * From @testTbl

--Overlapping jobs for an engId
Select * From @testTbl as A
Where exists(Select * From @testTbl as B Where B.job_ref <> A.job_ref and B.engId = A.engId and ((A.travel_Start > B.travel_Start and A.travel_Start < B.travel_End) or (A.travel_End > B.travel_Start and A.travel_End < B.travel_End)))

--Overlapping entries for the same job & engId
Select * From @testTbl as A
Where exists(Select * From @testTbl as B Where B.job_ref = A.job_ref and B.engId = A.engId and ((A.travel_Start > B.travel_Start and A.travel_Start < B.travel_End) or (A.travel_End > B.travel_Start and A.travel_End < B.travel_End)))

Does that help

Corey

dnagahawatte
Starting Member

24 Posts

Posted - 2004-06-14 : 05:53:57

This is brilliant
Exactly what i wanted
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources