Advanced DateDiff Logic/Code Needed

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

General SQL Server Forums

New to SQL Server Programming

Advanced DateDiff Logic/Code Needed

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-01-05 : 15:51:49

I am trying to figure out how much coverage (supervision time) an employee has based on their managers schedule.

Example
Name: Employee, SundayStart: 9 AM, SundayEnd: 5 PM
Name: Manager, SundayStart: 8 AM, SundayEnd: 4 PM

############# 8 AM |Covered Time| 5 PM
Employee ##########|------------|-----
Manger ######------|------------|

Employee is covered for 6 hours on sunday.

Anyway to figure this out? This is what I have so far. Sorry, but im in access 03.


SELECT  tblCAESchedule.Name, tblCAESchedule.Title, tblCAESchedule.SunStart, tblCAESchedule.SunEnd, 
        IIf(DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])>=1,DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend]),DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])+1440)/60 AS SunHours, 
        tblSupSchedule.Name, tblSupSchedule.SunStart, tblSupSchedule.SunEnd, 
	Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60) AS StartDif, 
        Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS EndDif, 
        Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60)+Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS ShiftDif
FROM    tblCAESchedule INNER JOIN tblSupSchedule ON tblCAESchedule.Skill = tblSupSchedule.Skill
WHERE   tblCAESchedule.Skill = "Sales"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-01-05 : 17:39:58

Do you want MS Sql Server code?

isn't 9AM to 4PM 7 hours rather than 6?

To avoid a lot of back and forth, please post:
1) DDL (create table statements for all tables involved)
2) DML (Insert statements to populate the tables with some sample data)
3) Desired output based on the sample data.

Can a person's time span through 12 midnight?
can a person's time be represented by multiple rows?

One solution for your simple example could be based on the following code:


declare @tb table (person int, startTime datetime, endtime datetime)
insert @tb 
select 1, '20070105 9:00:00.00', '20070105 17:00:00.00' union
select 2, '20070105 8:00:00.00', '20070105 16:00:00.00'


select	coveredtime / 60 hours
	,coveredtime % 60 minutes
	
from	(
	select	man.person [manager]
		,emp.person [employee]
		,datediff(minute,
			case
			when datediff(millisecond, man.startTime, emp.startTime) > 0 then emp.StartTime
			else man.StartTime
			end,
			case
			when datediff(millisecond, man.endTime, emp.endTime) > 0 then man.endTime
			else emp.endTime
			end) as [CoveredTime]
	from	@tb emp
	join	@tb man on man.person = emp.person + 1
	) a


output:
hours       minutes     
----------- ----------- 
7           0

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-05 : 18:56:02

tmaiden, if you have MS ACCESS queries, please post them in proper forum.
TG, what if employees time and manager time does not overlap at all?

-- prepare sample data
declare @tb table (person int, startTime datetime, endtime datetime)
insert @tb 
select 1, '20070105 12:00', '20070105 13:00' union all
select 2, '20070105 10:00', '20070105 18:00' union all
select 11, '20070105 9:00', '20070105 11:00' union all
select 12, '20070105 6:00', '20070105 13:00' union all
select 21, '20070105 10:00', '20070105 17:00' union all
select 22, '20070105 12:00', '20070105 14:00' union all
select 31, '20070105 9:00', '20070105 11:00' union all
select 32, '20070105 15:00', '20070105 19:00'

select	case
		when finish < start then 0
		else datediff(minute, start, finish) / 60
	end as [Covered time (hours)],
	case
		when finish < start then 0
		else datediff(minute, start, finish) % 60
	end as [Covered time (minutes)]
from	(
		select		man.person [manager],
				emp.person [employee],
				case
					when datediff(millisecond, man.startTime, emp.startTime) > 0 then emp.StartTime
					else man.StartTime
				end AS Start,
				case
					when datediff(millisecond, man.endTime, emp.endTime) > 0 then man.endTime
					else emp.endTime
				end AS Finish
		from		@tb emp
		inner join	@tb man on man.person = emp.person + 1
	) a

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-01-06 : 09:22:10

>>TG, what if employees time and manager time does not overlap at all?
Another good point. There are a lot of unknowns (by us) including the questions I asked earlier, that's why I suggested providing the actual schema and sample data. I just wanted to provide a basic idea the OP could build on if they wanted to. :)

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-06 : 10:19:44

I tried to write an UDF for this here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202

I am convinced that the UDF in the link will take care of most scenarios.

SELECT dbo.fnTimeOverlap('20070105 09:00', '20070105 17:00', '20070105 08:00', '20070105 16:00')

returns 25200, which is 7 hours.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources