rounding to nearest time increment 2

[kskinne]

I have a timeclock table with a timein and a timeout field, and I am using the following function(s) to return the time duration between timein and timeout, and display it in hours and minutes:

CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, TimeIn, TimeOut), 0), 108)

Here's what I need to do:

Round the minutes in the duration to the nearest 1/10th of an hour, or in other words, the neareast 6 minute increment (6, 12, 18, 24, 30, 36, 42, 48, 54, or 60 minutes)

UNLESS the minutes in the duration is closer to a 15 minute increment (15, 30, 45, or 60), in which case I need to round it to the nearest 15 minute increment instead.

For example here is a list of timein and timeout values, the actual duration between them, and what I need the rounded duration to be:

recordnbr | timein | timeout | duration | roundedduration
1 | 2008-10-09 07:45:00.000 | 2008-10-09 08:45:00.000 | 01:00 | 01:00
2 | 2008-10-09 07:45:00.000 | 2008-10-09 08:50:00.000 | 01:05 | 01:06
3 | 2008-10-09 07:45:00.000 | 2008-10-09 08:55:00.000 | 01:10 | 01:12
4 | 2008-10-09 07:45:00.000 | 2008-10-09 09:00:00.000 | 01:15 | 01:15
5 | 2008-10-09 07:45:00.000 | 2008-10-09 09:05:00.000 | 01:20 | 01:18
6 | 2008-10-09 07:45:00.000 | 2008-10-09 09:10:00.000 | 01:25 | 01:24
7 | 2008-10-09 07:45:00.000 | 2008-10-09 09:15:00.000 | 01:30 | 01:30
8 | 2008-10-09 07:45:00.000 | 2008-10-09 09:20:00.000 | 01:35 | 01:36
9 | 2008-10-09 07:45:00.000 | 2008-10-09 09:25:00.000 | 01:40 | 01:42
10 | 2008-10-09 07:45:00.000 | 2008-10-09 09:30:00.000 | 01:45 | 01:45
11 | 2008-10-09 07:45:00.000 | 2008-10-09 09:35:00.000 | 01:50 | 01:48
12 | 2008-10-09 07:45:00.000 | 2008-10-09 09:40:00.000 | 01:55 | 01:54
13 | 2008-10-09 07:45:00.000 | 2008-10-09 09:45:00.000 | 02:00 | 02:00

Note that in record number 4, where the duration is 1:15, it is not rounding up or down to 1:12 or 1:18, also not the same with record number 10, duration of 1:45, it is not rounded to 1:42 or 1:48.

Is this possible and if so how do I do this?? Please let me know if I need to supply any more information or explanation.

Thank you for your help,
Kevin

 

[gmmastros]

I would suggest that you add a table to your database. This table should have exactly 60 records (one for each minute). Then, you could have a column in this table for RoundedMinute. With that table, it would be relatively easy to accomplish this (without rounding) because a lookup table is used instead. This also gives you the flexibility to modify the rounding method without changing the code. You just need to change the data in the tables.

Here's an example:

[green]-- Sample data to test with[/green]
Declare @Temp Table(RecordNbr Int, TimeIn DateTime, TimeOut DateTime)

Insert Into @Temp Values(1 ,'2008-10-09 07:45:00.000','2008-10-09 08:45:00.000')-- | 01:00 | 01:00
Insert Into @Temp Values(2 ,'2008-10-09 07:45:00.000','2008-10-09 08:50:00.000')-- | 01:05 | 01:06
Insert Into @Temp Values(3 ,'2008-10-09 07:45:00.000','2008-10-09 08:55:00.000')-- | 01:10 | 01:12
Insert Into @Temp Values(4 ,'2008-10-09 07:45:00.000','2008-10-09 09:00:00.000')-- | 01:15 | 01:15
Insert Into @Temp Values(5 ,'2008-10-09 07:45:00.000','2008-10-09 09:05:00.000')-- | 01:20 | 01:18
Insert Into @Temp Values(6 ,'2008-10-09 07:45:00.000','2008-10-09 09:10:00.000')-- | 01:25 | 01:24
Insert Into @Temp Values(7 ,'2008-10-09 07:45:00.000','2008-10-09 09:15:00.000')-- | 01:30 | 01:30
Insert Into @Temp Values(8 ,'2008-10-09 07:45:00.000','2008-10-09 09:20:00.000')-- | 01:35 | 01:36
Insert Into @Temp Values(9 ,'2008-10-09 07:45:00.000','2008-10-09 09:25:00.000')-- | 01:40 | 01:42
Insert Into @Temp Values(10,'2008-10-09 07:45:00.000','2008-10-09 09:30:00.000')-- | 01:45 | 01:45
Insert Into @Temp Values(11,'2008-10-09 07:45:00.000','2008-10-09 09:35:00.000')-- | 01:50 | 01:48
Insert Into @Temp Values(12,'2008-10-09 07:45:00.000','2008-10-09 09:40:00.000')-- | 01:55 | 01:54
Insert Into @Temp Values(13,'2008-10-09 07:45:00.000','2008-10-09 09:45:00.000')-- | 02:00 | 02:00

[green]-- Build a table to store Actual vs. rounded minutes[/green]
Declare @MinuteIncrement Table(ActualMinute Int, RoundedMinute Int)
Insert Into @MinuteIncrement Values(0, 0)
Insert Into @MinuteIncrement Values(1, 0)
Insert Into @MinuteIncrement Values(2, 0)
Insert Into @MinuteIncrement Values(3, 6)
Insert Into @MinuteIncrement Values(4, 6)
Insert Into @MinuteIncrement Values(5, 6)
Insert Into @MinuteIncrement Values(6, 6)
Insert Into @MinuteIncrement Values(7, 6)
Insert Into @MinuteIncrement Values(8, 6)
Insert Into @MinuteIncrement Values(9, 12)
Insert Into @MinuteIncrement Values(10, 12)
Insert Into @MinuteIncrement Values(11, 12)
Insert Into @MinuteIncrement Values(12, 12)
Insert Into @MinuteIncrement Values(13, 12)
Insert Into @MinuteIncrement Values(14, 15)
Insert Into @MinuteIncrement Values(15, 15)
Insert Into @MinuteIncrement Values(16, 15)
Insert Into @MinuteIncrement Values(17, 18)
Insert Into @MinuteIncrement Values(18, 18)
Insert Into @MinuteIncrement Values(19, 18)
Insert Into @MinuteIncrement Values(20, 18)
Insert Into @MinuteIncrement Values(21, 24)
Insert Into @MinuteIncrement Values(22, 24)
Insert Into @MinuteIncrement Values(23, 24)
Insert Into @MinuteIncrement Values(24, 24)
Insert Into @MinuteIncrement Values(25, 24)
Insert Into @MinuteIncrement Values(26, 24)
Insert Into @MinuteIncrement Values(27, 30)
Insert Into @MinuteIncrement Values(28, 30)
Insert Into @MinuteIncrement Values(29, 30)
Insert Into @MinuteIncrement Values(30, 30)
Insert Into @MinuteIncrement Values(31, 30)
Insert Into @MinuteIncrement Values(32, 30)
Insert Into @MinuteIncrement Values(33, 30)
Insert Into @MinuteIncrement Values(34, 36)
Insert Into @MinuteIncrement Values(35, 36)
Insert Into @MinuteIncrement Values(36, 36)
Insert Into @MinuteIncrement Values(37, 36)
Insert Into @MinuteIncrement Values(38, 36)
Insert Into @MinuteIncrement Values(39, 36)
Insert Into @MinuteIncrement Values(40, 42)
Insert Into @MinuteIncrement Values(41, 42)
Insert Into @MinuteIncrement Values(42, 42)
Insert Into @MinuteIncrement Values(43, 42)
Insert Into @MinuteIncrement Values(44, 45)
Insert Into @MinuteIncrement Values(45, 45)
Insert Into @MinuteIncrement Values(46, 45)
Insert Into @MinuteIncrement Values(47, 48)
Insert Into @MinuteIncrement Values(48, 48)
Insert Into @MinuteIncrement Values(49, 48)
Insert Into @MinuteIncrement Values(50, 48)
Insert Into @MinuteIncrement Values(51, 48)
Insert Into @MinuteIncrement Values(52, 54)
Insert Into @MinuteIncrement Values(53, 54)
Insert Into @MinuteIncrement Values(54, 54)
Insert Into @MinuteIncrement Values(55, 54)
Insert Into @MinuteIncrement Values(56, 54)
Insert Into @MinuteIncrement Values(57, 60)
Insert Into @MinuteIncrement Values(58, 60)
Insert Into @MinuteIncrement Values(59, 60)

[green]-- The query to use the actual minutes[/green]
Select T.*, CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, TimeIn, TimeOut), 0), 108) As ActualDuration,
       Convert(Char(5), DateAdd(Minute, DatePart(Hour, TimeOut-TimeIn) * 60 + M.RoundedMinute, 0), 108) As RoundedDuration
From   @Temp T
       Inner Join @MinuteIncrement M
         On DatePart(Minute, TimeOut - TimeIn) = M.ActualMinute
Order By T.RecordNbr

I created the MinuteIncrement table as a table variable. I would encourage you to make this a real table in your database (because the query will be faster). Also, I quickly created the 'Rounded Minute' values in it. You should spend some time making sure it's right.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Here's one method to get you started. You can probably clean up the code a bit, but you get the idea:

DECLARE @T1 TABLE
(
ClockIn DATETIME,
ClockOut DATETIME
)

INSERT INTO @T1 SELECT '2008-10-09 07:54AM', '2008-10-09 17:05PM'
INSERT INTO @T1 SELECT '2008-10-09 08:03AM', '2008-10-09 16:47PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 08:45PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 08:50PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 08:55PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:00PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:05PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:10PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:15PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:25PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:30PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:35PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:40PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:45PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:50PM'
INSERT INTO @T1 SELECT '2008-10-07 07:45AM', '2008-10-09 09:55PM'

SELECT x.*,
RoundedMinutes =
CASE
	WHEN ABS(MinutesQuarters - Minutes) <= ABS(MinutesTenths - Minutes) THEN MinutesQuarters
	ELSE MinutesTenths
END
FROM
	(SELECT *,
	DATEDIFF(minute, ClockIn, ClockOut)/60 AS Hours,
	DATEDIFF(minute, ClockIn, ClockOut) - ((DATEDIFF(minute, ClockIn, ClockOut)/60) * 60) AS Minutes,
	ROUND((DATEDIFF(minute, ClockIn, ClockOut) - ((DATEDIFF(minute, ClockIn, ClockOut)/60) * 60)) / CONVERT(DECIMAL(18,4), 6), 0) * 6 AS MinutesTenths,
	ROUND((DATEDIFF(minute, ClockIn, ClockOut) - ((DATEDIFF(minute, ClockIn, ClockOut)/60) * 60)) / CONVERT(DECIMAL(18,4), 15), 0) * 15 AS MinutesQuarters
	FROM @T1) x

 

[kskinne]

These are great thank you very much, I will test and post back if I have questions regarding these.

I have another question sort of related to the timeclock issue - is there a query I can use to return the current time on the SQL server, and not on the machine I am running the query from?

 

[RiverGuy]

SELECT GETDATE()

 

[kskinne]

Perfect - thank you

 

[kskinne]

ok here's another new twist in this, see my prior posts in this thread - instead of rounding the duration (the timeout minus the timein) to the nearest 6 minute increment, how would I go about just rounding the actual time to the nearest six minute increment? For example say I have an entry to the database like this:

recordnbr | timein | timeout
1 | 2008-11-05 08:05:00.000 | 2008-11-05 08:21:05.000

How do I make it so the timein is rounded and changed from 8:05:00.000 to 8:06:00.000, and the timeout would be rounded from 8:21:05.000 to 8:24:00.000?

If you need any more info please let me know. Thank you for your help,
Kevin

 

[kskinne]

Sorry here is a little more information that, hopefully, will help and shed light on what I'm trying to do.

I currently have this script that is rounding the actual clock in and clock out times, to the nearest 5 minutes, as part of a trigger in SQL:

UPDATE TimeClock
SET TimeOut =
DATEADD(mi,(DATEPART(mi,DATEADD(mi,1,TimeOut))/6)*6,DATEADD(hh,DATEDIFF(hh,0,DATEADD(mi,1,TimeOut)),0)),
TimeIn =
DATEADD(mi,(DATEPART(mi,DATEADD(mi,1,TimeIn))/6)*6,DATEADD(hh,DATEDIFF(hh,0,DATEADD(mi,1,TimeIn)),0))

What I would like to do is modify this, so that it rounds to the nearest 6 minute increment instead of the nearest 5 minutes, unless it is at or close to one of the 15 minute marks (15, 30, 45, or 60), then in that case it needs to round to the nearest 15 minutes. For example:

if someone clocks in or out at the following times, it will round the time accordingly:

actual time rounded to
8:01 8:00
8:05 8:06
8:10 8:12
8:15 8:15 (15 min. interval, not rounded to 6 min)
8:20 8:18
8:25 8:24

and so on...

I hope this helps to clarify what I'm trying to accomplish.

Thanks in advance
Kevin

 

[gmmastros]

I would suggest you use a User Defined Function for this.

In the function I show below, I am first removing the seconds from the time (effectively rounding it DOWN to the nearest minute). Then, I capture the minute part of the time and use it in a big select case to determine the number of minutes added or subtracted to the original time.

I've said it before, and I'll say it again. I generally don't like functions because they are usually a performance killer. In order for me to use a function, it MUST satisfy both of the following criteria.

1. It must be 're-usable'
2. It must be 'complicated enough' that it's worth hiding the implementation details.

Clearly, this satisfies both criteria and has the added benefit of not using data from any tables. Essentially, this is pure math, which should make the performance be fast.

Anyway, Create this function:

Alter Function dbo.RoundToTimeIncrement
  (@InputDateTime DateTime)
Returns DateTime
As
Begin
	
  Declare @Minute Int
  Declare @Output DateTime

  Set @Output = DateAdd(Minute, DateDiff(Minute, 0, @InputDateTime), 0)
  Set @Minute = DatePart(Minute, @Output)

  Select @Output = DateAdd(Minute, 
           Case When @Minute In (1,7,13,16,19,25,31,37,43,46,49,55) Then -1
                When @Minute In (2,8,20,26,32,38,50,56) Then -2
                When @Minute In (33,39,51) Then -3
                When @Minute In (3,9,21,27,57) Then 3
                When @Minute In (4,10,22,28,34,40,52,58) Then 2
                When @Minute In (5,11,14,17,23,29,35,41,44,47,53,59) Then 1
                Else 0 End,
            @Output)

    Return @Output
End

You'll need to test the function to make sure that all of the numbers (in the IN part of the code) are correct. Adjust accordingly.

After the function is created, you can use it anywhere you want to round the time.

Ex:

Select dbo.RoundToTimeIncrement(GetDate())

Or:

UPDATE TimeClock
SET    TimeOut = dbo.RoundToTimeIncrement(TimeOut),
       TimeIn = dbo.RoundToTimeIncrement(TimeIn)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kskinne]

George this works great, thanks so much for your help

Kevin

 

[gmmastros]

You're welcome.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom