Calculate overtime - 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

General SQL Server Forums

New to SQL Server Programming

Calculate overtime

Author

Topic

kman
Starting Member

6 Posts

Posted - 2007-03-22 : 16:33:11

Hi, I'm having huge troubles calculating overtime work in my db.

I have a table containing working hours that looks like this:

Userid Login Logout
---------------------------------------------------
2 21.03.2007 12:00:00 21.03.2007 23:00:00
2 22.03.2007 08:00:00 21.03.2007 17:00:00

To complicate things I also have a table that stores overtime rates:

From To Percent
----------------------------
00:00:00 20:00:00 0
20:00:00 22:00:00 20
22:00:00 24:00:00 50

(That percentage part isn't that important in this case.)
Now here's the tricky part, I want to transform those tables into something like this:

Userid 00:00-20:00 20:00-22:00 22:00-24:00
------------------------------------------
2 8 2 1
2 9 0 0

I'm pretty sure this requires a complicated sql query, but I have no clue where to start,
can someone push me in the right direction?

Any help is appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-22 : 17:01:09

So does the query need to have columns corresponding to the rows in the overtime rates table and be able to dynamically adapt to any number of overtime rates?

kman
Starting Member

6 Posts

Posted - 2007-03-22 : 17:15:11

Yeah, ideally the colums in the query will be dynamically fetched from the overtime table.
They dont need to be named like '00:00-20:00' though, 1, 2, 3 etc. is also fine.
Do you think its doable?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-23 : 07:34:22

With the help of this function, --" rel="nofollow">http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202[code]-- Prepare sample data
DECLARE @Entry TABLE (UserID INT, Login DATETIME, Logout DATETIME)

INSERT @Entry
SELECT 2, '20070321 12:00', '20070321 23:00' UNION ALL
SELECT 2, '20070322 08:00:00', '20070322 17:00:00'

select * from @entry

DECLARE @Overtime TABLE (OT TINYINT, FromTime DATETIME, ToTime DATETIME, Payment SMALLMONEY)

INSERT @Overtime
SELECT 0, '00:00', '20:00', 0 UNION ALL
SELECT 1, '20:00', '22:00', 20 UNION ALL
SELECT 2, '22:00', '23:59:59', 50

-- Show the expected output
SELECT e.UserID,
SUM(CASE WHEN o.OT = 0 THEN dbo.fnTimeOverlap(e.c1, e.c2, o.FromTime, o.ToTime) ELSE 0 END) / 3600.0 AS [0],
SUM(CASE WHEN o.OT = 1 THEN dbo.fnTimeOverlap(e.c1, e.c2, o.FromTime, o.ToTime) ELSE 0 END) / 3600.0 AS [1],
SUM(CASE WHEN o.OT = 2 THEN dbo.fnTimeOverlap(e.c1, e.c2, o.FromTime, o.ToTime) ELSE 0 END) / 3600.0 AS [2]
FROM (
SELECT UserID,
Login,
LogOut,
Login - CAST(Login - 0.50000004 AS INT) AS c1,
Logout - CAST(Logout - 0.50000004 AS INT) AS c2
FROM @Entry
) AS e
CROSS JOIN @Overtime AS o
GROUP BY e.UserID
ORDER BY e.UserID

Peter Larsson
Helsingborg, Sweden

kman
Starting Member

6 Posts

Posted - 2007-03-23 : 08:31:59

That worked like a charm, thanks a ton Peter!
I'll work around that dynamic-issue by automatically generating the sql query in c#.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources