Compare Schedule vs Actual - 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 2008 Forums

Transact-SQL (2008)

Compare Schedule vs Actual

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2012-03-08 : 16:59:28

Hello All,

I'm looking for ideas on this one. I have two tables. One table stores a schedule that shows all time frames you are scheduled to work. The other stores the actual times worked.

Examples

Create Table #TempScheduled
(	
	AgentName varchar(50),
	BeginDate datetime,
	BeginTime datetime,
	EndTime datetime
)

Insert Into #TempScheduled
Select 'Bob Smith','01/04/2012','01/04/2012 12:00','01/04/2012 15:00' union all
Select 'Bob Smith','01/04/2012','01/04/2012 15:15','01/04/2012 17:00' union all
Select 'Bob Smith','01/04/2012','01/04/2012 18:00','01/04/2012 19:30'  

Create Table #TempActual
(	
	AgentName varchar(50),
	BeginDate datetime,
	BeginTime datetime,
	EndTime datetime
)

Insert Into #TempActual
Select 'Bob Smith','01/04/2012','01/04/2012 12:01:35','01/04/2012 12:30:00' union all
Select 'Bob Smith','01/04/2012','01/04/2012 12:50:00','01/04/2012 15:01:47' union all
Select 'Bob Smith','01/04/2012','01/04/2012 15:17:00','01/04/2012 16:59:45' union all
Select 'Bob Smith','01/04/2012','01/04/2012 18:03:00','01/04/2012 19:27:16'

Let's say I want to compare Bob's actual worked time with his scheduled. I want to copy all times that he was not working when he should have been, such as the 1 minute and 35 seconds he was late logging in the first time and the 20 minutes he logged out for at 12:30-12:50 so I can get a total sum. I don't care if he worked when he was not supposed to, only if he didn't work when he was supposed to. Anyone have any ideas?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 18:08:57

[code]
SELECT s.AgentName ,
s.BeginDate,
SUM(DATEDIFF(ss,s.BeginTime,s.EndTime)) AS ScheduleDuration,
SUM(DATEDIFF(ss,CASE WHEN a.BeginTime < = s.BeginTime THEN s.BeginTime ELSE a.BeginTime END,CASE WHEN a.EndTime > = s.EndTime THEN s.EndTime ELSE a.EndTime END)) AS ActualDuration
FROM #TempScheduled s
CROSS APPLY (SELECT BeginTime ,EndTime
FROM #TempActual
WHERE AgentName = s.AgentName
AND BeginDate = s.BeginDate
AND (BeginTime <= s.EndTime AND EndTime >= s.beginTime)
)a
GROUP BY s.AgentName ,
s.BeginDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vassago
Starting Member

33 Posts

Posted - 2012-03-08 : 19:38:23

quote:

Originally posted by visakh16


SELECT s.AgentName ,
	s.BeginDate,
      SUM(DATEDIFF(ss,s.BeginTime,s.EndTime)) AS ScheduleDuration,
      SUM(DATEDIFF(ss,CASE WHEN a.BeginTime < = s.BeginTime THEN s.BeginTime ELSE a.BeginTime END,CASE WHEN a.EndTime > = s.EndTime THEN s.EndTime ELSE a.EndTime END)) AS ActualDuration
FROM #TempScheduled s
CROSS APPLY (SELECT BeginTime ,EndTime 
             FROM #TempActual
             WHERE AgentName = s.AgentName 
             AND BeginDate = s.BeginDate 
             AND (BeginTime <= s.EndTime AND EndTime >= s.beginTime)
            )a
GROUP BY s.AgentName ,
	s.BeginDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is pretty epic! I didn't even think of doing it this way. My calculations were taking me into comparing minute by minute, which of course was not adequate. Thanks so much!

Vassago
Starting Member

33 Posts

Posted - 2012-03-08 : 19:39:13

I'm going to spend some time learning these cross apply joins.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 19:40:30

welcome

see this to understand what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources