Time Worked in 15 minute increments

Question

Time Worked in 15 minute increments

Lowell

SSC Guru

Points: 323518
More actions
December 23, 2010 at 1:04 pm

#242674

This is nearly a payroll kind of question, where i need to add up a persons hours worked for a given period, ie a week/two weeks/month on a per day basis., but i need to round up and down to 15 minute increments; I think this is a typical issue that i haven't had to address yet, so i'll ping my peers to see if they would do it the same way.
how would you tackle this, or especially do you have a business rule that says things like if they worked 7 minutes into a 15 minute period, they get the quarter anyway? how do you award time worked?
I was assuming a simple case statement to round up/down for the total hours, using integer division on the minutes, but now i'm second guessing myself.
here's some sample data and of course the setup to fiddle with, which you'd love to see in every post:
/*
HRS MINS
8 46
10 7
4 19
6 5
9 28
8 51*/
With SomeSampleTimes AS
(
SELECT CONVERT(datetime,'12/06/2010 9:30:00.000') AS 'TheTimeIn',
CONVERT(datetime,'12/06/2010 17:46:00.000') AS 'TheTimeOut' UNION ALL
SELECT CONVERT(datetime,'12/07/2010 7:30:00.000'),CONVERT(datetime,'12/07/2010 17:07:00.000') UNION ALL
SELECT CONVERT(datetime,'12/09/2010 11:15:00.000'),CONVERT(datetime,'12/09/2010 15:19:00.000') UNION ALL
SELECT CONVERT(datetime,'12/10/2010 7:00:00.000'),CONVERT(datetime,'12/10/2010 13:05:00.000') UNION ALL
SELECT CONVERT(datetime,'12/14/2010 7:00:00.000'),CONVERT(datetime,'12/14/2010 16:28:00.000') UNION ALL
SELECT CONVERT(datetime,'12/17/2010 7:00:00.000'),CONVERT(datetime,'12/17/2010 15:51:00.000')
)
SELECT
TheTimeIn,
TheTimeOut,
datediff(minute,DATEADD(hour, DATEDIFF(hour,0,TheTimeIn), 0),TheTimeOut) / 60 AS HRS,
datediff(minute,DATEADD(hour, DATEDIFF(hour,0,TheTimeIn), 0),TheTimeOut) % 60 AS MINS
FROM SomeSampleTimes
you can assume clean data, so that there is always a Timeout with a matching Timein in the same row.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this seems to give me the results i was looking for, it's rather dawn out into CTE's to show all the logic and can be easily shortened up, but i'm posting it for reference:

I'm just looking for any of those biz "walmart" kind of code fixes, where for example if you worked 6 mines on monday and 6 minutes on tuesday, my rule would give them nothing, but some HR dept is paranoid and calculates all those under 15's together to make an extra quarter just in case....

does anyone really do that?

/*--Results

TheTimeIn TheTimeOut HRS MINS AwardedMinutes

----------------------- ----------------------- ----------- ----------- -------------- ---------------------------------------

2010-12-06 09:30:00.000 2010-12-06 17:46:00.000 8 46 45 8.750000

2010-12-07 07:30:00.000 2010-12-07 17:07:00.000 10 7 0 10.000000

2010-12-09 11:15:00.000 2010-12-09 15:19:00.000 4 19 15 4.250000

2010-12-10 07:00:00.000 2010-12-10 13:05:00.000 6 5 0 6.000000

2010-12-14 07:00:00.000 2010-12-14 16:28:00.000 9 28 30 9.500000

2010-12-17 07:00:00.000 2010-12-17 15:51:00.000 8 51 45 8.750000

*/

With SomeSampleTimes AS

(

SELECT CONVERT(datetime,'12/06/2010 9:30:00.000') AS 'TheTimeIn',

CONVERT(datetime,'12/06/2010 17:46:00.000') AS 'TheTimeOut' UNION ALL

SELECT CONVERT(datetime,'12/07/2010 7:30:00.000'),CONVERT(datetime,'12/07/2010 17:07:00.000') UNION ALL

SELECT CONVERT(datetime,'12/09/2010 11:15:00.000'),CONVERT(datetime,'12/09/2010 15:19:00.000') UNION ALL

SELECT CONVERT(datetime,'12/10/2010 7:00:00.000'),CONVERT(datetime,'12/10/2010 13:05:00.000') UNION ALL

SELECT CONVERT(datetime,'12/14/2010 7:00:00.000'),CONVERT(datetime,'12/14/2010 16:28:00.000') UNION ALL

SELECT CONVERT(datetime,'12/17/2010 7:00:00.000'),CONVERT(datetime,'12/17/2010 15:51:00.000')

),

SemiProcessed AS (

SELECT

TheTimeIn,

TheTimeOut,

datediff(minute,DATEADD(hour, DATEDIFF(hour,0,TheTimeIn), 0),TheTimeOut) / 60 AS HRS,

datediff(minute,DATEADD(hour, DATEDIFF(hour,0,TheTimeIn), 0),TheTimeOut) % 60 AS MINS

FROM SomeSampleTimes),

DrawnAndQuartered AS (

SELECT SemiProcessed.*,

CASE

WHEN (MINS % 15) >7

THEN ((MINS / 15) + 1) * 15

ELSE ((MINS / 15))* 15

END AS AwardedMinutes

FROM SemiProcessed )

SELECT DrawnAndQuartered.*,

DrawnAndQuartered.HRS + DrawnAndQuartered.AwardedMinutes / 60.0

FROM DrawnAndQuartered

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

I just tried the following sample code:

SELECT CONVERT(datetime,'12/05/2010 9:30:00.000') AS 'TheTimeIn',

CONVERT(datetime,'12/05/2010 9:35:00.000') AS 'TheTimeOut'

and it returned 0 HRS, 35MINS and 30 AwardedMinutes.

Are you sure this is what you're looking for?

Regarding your cumulative concern: it all comes up to the question: What is the base line for the 15min rule?

Once that is scratched in stone, it'll be easy (more or less): All you'd nee to do is collect the minutes per TheTimeIn-TheTimeOut interval and cumulate it up to the level where you'll need to round it. React accordingly. (= use your CASE statement at the level the business rule requires.)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Lowell (12/23/2010)
this seems to give me the results i was looking for, it's rather dawn out into CTE's to show all the logic and can be easily shortened up, but i'm posting it for reference:
I'm just looking for any of those biz "walmart" kind of code fixes, where for example if you worked 6 mines on monday and 6 minutes on tuesday, my rule would give them nothing, but some HR dept is paranoid and calculates all those under 15's together to make an extra quarter just in case....
does anyone really do that?

Yes... HR "paranoids" do really do that but not quite that way (or at least not that I've seen but it does make better sense). The true paranoids give up ANY time period less than 15 minutes as a 15 minute interval. In theory, you could get a total of 75 minutes for showing up only 1 minute for each of 5 days. You could also get more than 24 hours pay if you got stuck in the revolving door! 😛

What REALLY ticks me off is that this IS the age of computers! Why in the heck are people still worried about 15 minute intervals when they're going to decimal hours, anyway??? :blink:

Anyway... here's the code I use for such rounding because, yeah... I've got to put up with it too. If you want to get rid of the 1 minute rule stuff, remove the ISNULL(NULLIF combination.

WITH cteSomeSampleTimes (TheTimeIn, TheTimeOut) AS

( --=== This is test data and is NOT a part of the solution

SELECT CONVERT(DATETIME,'12/06/2010 9:30:00.000'),CONVERT(DATETIME,'12/06/2010 17:46:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/07/2010 7:30:00.000'),CONVERT(DATETIME,'12/07/2010 17:07:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/09/2010 11:15:00.000'),CONVERT(DATETIME,'12/09/2010 15:19:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/10/2010 7:00:00.000'),CONVERT(DATETIME,'12/10/2010 13:05:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/14/2010 7:00:00.000'),CONVERT(DATETIME,'12/14/2010 16:28:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/17/2010 7:00:00.000'),CONVERT(DATETIME,'12/17/2010 15:51:00.000') UNION ALL

SELECT CONVERT(DATETIME,'12/18/2010 7:00:00.000'),CONVERT(DATETIME,'12/18/2010 7:01:00.000') --Added to demo

)

,

cteRoundTo15 AS

( --=== Round the TheTimeOut to 15 minute intervals (rounds up or down depending).

-- Things less than 15 minutes total are rounded up to 15 minutes.

SELECT TheTimeIn,

TheTimeOut,

DATEDIFF(mi, TheTimeIn, TheTimeOut) AS DeltaT,

ISNULL(NULLIF(DATEDIFF(mi,TheTimeIn,TheTimeOut+'00:07:30')/15,0),1)*15 AS DeltaTRounded

FROM cteSomeSampleTimes

) --=== Produce decimal hours that are rounded to 15 minute intervals

SELECT TheTimeIn,

TheTimeOut,

DeltaT / 60 AS HRS,

DeltaT % 60 AS MINS,

DeltaTRounded/60.0 AS DecimalRoundedHours

FROM cteRoundTo15

;

TheTimeIn TheTimeOut HRS MINS DecimalRoundedHours

----------------------- ----------------------- ----------- ----------- ---------------------------------------

2010-12-06 09:30:00.000 2010-12-06 17:46:00.000 8 16 8.250000

2010-12-07 07:30:00.000 2010-12-07 17:07:00.000 9 37 9.500000

2010-12-09 11:15:00.000 2010-12-09 15:19:00.000 4 4 4.000000

2010-12-10 07:00:00.000 2010-12-10 13:05:00.000 6 5 6.000000

2010-12-14 07:00:00.000 2010-12-14 16:28:00.000 9 28 9.500000

2010-12-17 07:00:00.000 2010-12-17 15:51:00.000 8 51 8.750000

2010-12-18 07:00:00.000 2010-12-18 07:01:00.000 0 1 0.250000

(7 row(s) affected)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)