Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Simpler way of calculating Time values stored as numbers

Jun 27 2012 12:00AM by Madhivanan   

Often you may come across a situation to calculate time values which are stored as numbers. There is a seperate TIME datatype to store time values starting from SQL Server 2008. But you might find that in earlier versions time values might be stored in DATETIME,Integer, Decimal, or other numeric datatypes depends on the format. Also if you import data from different system, you may need to use appropriate datatype to store time values.

Ex. The time value 02:30 AM has the following possibilities of being stored effectively based on the datatype

DATEIME : 19000101 02:30:00 (By default we can append with base date)
INTEGER : 150 (Time is converted to minutes so 2:30 hours is 150 minutes)
DECIMAL : 2.30
VARCHAR : 02:30 AM

There was a question in SQL Server forum about calculating time values which were stored in float datatype. Consider the following set of data

CREATE table #t (empid char(4), work_date datetime, working_hours float, ot_hours float, break_hours float)

INSERT INTO #t (empid, work_date, working_hours, ot_hours, break_hours)
select 'E001', '20120212', 1.3, 3.4, 0 union all
select 'E001', '20120212', 2, 1.5, 0 union all 
select 'E001', '20120213', 5.0, 2.2, 0.5 union all
select 'E002', '20120213', 4.45, 0, 1.35 union all
select 'E001', '20120214', 6.1, 2.0, 1.00 union all
select 'E002', '20120214', 1.3, 3.4, 0 union all
select 'E001', '20120215', 6.2, 0, 0 union all 
select 'E002', '20120215', 8.0, 0, 0 union all
select 'E002', '20120215', 1.5, 0, 4.5 union all
select 'E003', '20120215', 2.0, 1.0, 3.4

The original poster was looking for a way to produce the result in such a way that for each date if the total of three columns working_hours, ot_hours and break_hours is greater than or equal to 8 hours flag it to 1 otherwsie flag it to 0. The hours are stored in HH.MM format ie 2.3 means 2 hours and 30 minutes, 4.1 means 4 hours and 10 minutes, etc and there can't be values like 5.7,3.88,etc which are meaningless. Also addition of 2.3, 4.1 and 2.45 should be calculated as 9 hours and 25 minutes.

There can be many methods to calculate the time values that involve datatype convertions, extracting precision, scale and do calculations, etc.

Here is my method. Just do a SUM because of FLOAT datatype and when a value becomes >=7.6, it reaches 8 hours.

 

 
select 
	empid,
	work_date , 
	case when sum(working_hours+ot_hours+break_hours)>=7.6 then 1 else 0 end as status 
from 
	#t
group by 
	empid,work_date
Which produces the following result 
empid work_date               status
----- ----------------------- -----------
E001  2012-02-12 00:00:00.000 1
E001  2012-02-13 00:00:00.000 1
E002  2012-02-13 00:00:00.000 0
E001  2012-02-14 00:00:00.000 1
E002  2012-02-14 00:00:00.000 0
E001  2012-02-15 00:00:00.000 0
E002  2012-02-15 00:00:00.000 1
E003  2012-02-15 00:00:00.000 0

EDIT : The above method may not work for all set of data. See the first comment from Leszek Gniadkowski. Here is another alternate

select 
	empid,
	work_date , 
	case when 
		sum(floor(working_hours)*60+(working_hours-floor(working_hours))*100+
			floor(ot_hours)*60+(ot_hours-floor(ot_hours))*100+
			floor(break_hours)*60+(break_hours-floor(break_hours))*100) >=480	 
		then 1 else 0 
	end as status

from 
	#t
group by 
	empid,work_date

Post in the commet section if you have any other method.

Tags: sql,sql server,tsql

Madhivanan
3 · 39% · 12441
8
 
1
 
0
Refreshed
 
1
 
0
Incorrect


Submit

13  Comments  

  • 2.5 + 2.5 + 2.5 < 7.6 2h50m + 2h50m + 2h50m > 8h

    commented on Jun 27 2012 12:57AM
    Leszek Gniadkowski
    8 · 18% · 5718

  • Thanks Leszek Gniadkowski. I have included another method :)

    commented on Jun 27 2012 1:32AM
    Madhivanan
    3 · 39% · 12441

  • In the old days, we knew that the DATETIME was a FLOAT under the covers. We used FLOOR and CEILING to get the date and next day from these columns. Our real problem is that T-SQl has not finished implementing the ANSI Standards which have an INTERVAL data type family.

    commented on Jun 27 2012 10:06AM
    jcelko
    444 · 0% · 87

  • Joe Celko, Thanks for the feedback. Although SQL Server does not support INTERVAL, it can be simulated with DATEADD function

    commented on Jun 28 2012 12:07AM
    Madhivanan
    3 · 39% · 12441

  • Madhivanan, even your second method has issues. This is the problem with bad data modelling... it leads to inaccurate results and requires query gymnastics in order to fix.

    Calculating the number of minutes based on FLOOR([x]) and [x] - FLOOR([x]) should work and is mathematically valid. But the float data type is an approximate-number data type. You cannot count on your numerical values being handled exactly and it can result in subtle errors.

    SELECT
    [empid], 
    [work_date], 
    [working_hours], 
    FLOOR([working_hours]) as working_hours_hour,
    [working_hours] - FLOOR([working_hours]) as working_hours_min, 
    (FLOOR([working_hours]) * 60) + ([working_hours] - FLOOR([working_hours])) as working_hours_minutes_total
FROM #t

    results in:

    empid work_date               working_hours          working_hours_hour     working_hours_min      working_hours_minutes_total
----- ----------------------- ---------------------- ---------------------- ---------------------- ---------------------------
E001  2012-02-12 00:00:00.000 1.3                    1                      0.3                    60.3
E001  2012-02-12 00:00:00.000 2                      2                      0                      120
E001  2012-02-13 00:00:00.000 5                      5                      0                      300
E002  2012-02-13 00:00:00.000 4.45                   4                      0.45                   240.45
E001  2012-02-14 00:00:00.000 6.1                    6                      **0.0999999999999996**     360.1
E002  2012-02-14 00:00:00.000 1.3                    1                      0.3                    60.3
E001  2012-02-15 00:00:00.000 6.2                    6                      0.2                    360.2
E002  2012-02-15 00:00:00.000 8                      8                      0                      480
E002  2012-02-15 00:00:00.000 1.5                    1                      0.5                    60.5
E003  2012-02-15 00:00:00.000 2                      2                      0                      120

    You never know when these errors are going to pop up. First, CAST to decimal(6, 2). Why decimal(6, 2)? Because there are 1440 minutes in a day (4 decimal places), plus two places to the right of the decimal point. While it's unlikely that someone would work 24 hours in a single day, it is possible (and costs little to account for that edge case).

    SELECT
    [empid], 
    [work_date], 
    CAST([working_hours] as dec(6, 2)), 
    FLOOR(CAST([working_hours] as dec(6, 2))) as working_hours_hour,
    CAST([working_hours] as dec(6, 2)) - FLOOR(CAST([working_hours] as dec(6, 2))) as working_hours_min, 
    (FLOOR(CAST([working_hours] as dec(6, 2))) * 60) + (CAST([working_hours] as dec(6, 2)) - FLOOR(CAST([working_hours] as dec(6, 2)))) as working_hours_minutes_total
FROM @t

    results in:

    empid work_date                                                       working_hours_hour                      working_hours_min                       working_hours_minutes_total
----- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
E001  2012-02-12 00:00:00.000 1.30                                    1                                       0.30                                    60.30
E001  2012-02-12 00:00:00.000 2.00                                    2                                       0.00                                    120.00
E001  2012-02-13 00:00:00.000 5.00                                    5                                       0.00                                    300.00
E002  2012-02-13 00:00:00.000 4.45                                    4                                       0.45                                    240.45
E001  2012-02-14 00:00:00.000 6.10                                    6                                       0.10                                    360.10
E002  2012-02-14 00:00:00.000 1.30                                    1                                       0.30                                    60.30
E001  2012-02-15 00:00:00.000 6.20                                    6                                       0.20                                    360.20
E002  2012-02-15 00:00:00.000 8.00                                    8                                       0.00                                    480.00
E002  2012-02-15 00:00:00.000 1.50                                    1                                       0.50                                    60.50
E003  2012-02-15 00:00:00.000 2.00                                    2                                       0.00                                    120.00
    commented on Jun 28 2012 6:00AM
    Marc Jellinek
    97 · 2% · 546

  • Thanks. Thats good analysis Marc. This is another reason why float datatype should be avoided.

    commented on Jun 28 2012 6:12AM
    Madhivanan
    3 · 39% · 12441

  • Apologies: I forgot to multiply the minutes times 100: This will be closer to correct:

    SELECT
    [empid], 
    [work_date], 
    CAST([working_hours] as dec(4, 2)), 
    FLOOR(CAST([working_hours] as dec(4, 2))) as working_hours_hour,
    (CAST([working_hours] as dec(4, 2)) - FLOOR(CAST([working_hours] as dec(4, 2)))) * 100 as working_hours_min, 
    (FLOOR(CAST([working_hours] as dec(4, 2))) * 60) + (((CAST([working_hours] as dec(4, 2)) - FLOOR(CAST([working_hours] as dec(4, 2))))) * 100) as working_hours_minutes_total
FROM @t
    commented on Jun 28 2012 6:32AM
    Marc Jellinek
    97 · 2% · 546

  • @jcelko

    • datetime is not a float under the covers, at least not in SQL Server. This can be proven by examining the data pages containing datetime values, where you can see it is two 4-byte integers, one the number of days since the anchor date and one the number of 1/300th second ticks since midnight. In fact, round-tripping a datetime through float will break it. Try this:

      SELECT Convert(datetime, Convert(float, Convert(datetime, '20120628 13:37:59.997')))
-- oops. Yields 2012-06-28 13:37:59.993

      Float doesn't have enough precision for datetime!

    @Madhivanan:

    • Storing precise data in a float is not good.

    • Storing minutes that are 60-based embedded in a decimal number as if they were 100-based is even worse.

    Here's my solution to the given problem. As bad as the data storage is it can be accommodated fairly simply:

    SELECT
    T.empid, T.work_date,
    CASE WHEN Sum(Convert(int, Floor(H.Hrs)) * 60 + Convert(int, H.Hrs * 100) % 100) >= 480 THEN 1 ELSE 0 END Status
FROM
    #t T
    CROSS APPLY (
    	VALUES (T.working_hours), (T.ot_hours), (T.break_hours)
    ) H (Hrs)
GROUP BY T.empid, T.work_date;

    If you want to see some of the logic in that exposed, here's a more complicated query:

    WITH TimeData AS (
    SELECT
    	T.empid, T.work_date,
    	Sum(Convert(int, Floor(H.Hrs)) * 60 + Convert(int, H.Hrs * 100) % 100) TotalMinutes
    FROM
    	#t T
    	CROSS APPLY (
    		VALUES (T.working_hours), (T.ot_hours), (T.break_hours)
    	) H (Hrs)
    GROUP BY T.empid, T.work_date
)
SELECT
    empid,
    work_date,
    TotalMinutes,
    TotalMinutes / 60 WorkHours,
    TotalMinutes % 60 WorkMinutes,
    CASE WHEN TotalMinutes >= 480 THEN 1 ELSE 0 END Status
FROM
    TimeData;
    commented on Jun 28 2012 3:35PM
    ErikEckhardt
    65 · 3% · 887

  • Thanks ErikEckhardt. Clever usage of VALUES clause. How did I forget that? :)

    commented on Jun 29 2012 12:19AM
    Madhivanan
    3 · 39% · 12441

  • VALUES clause in CROSS APPLY? This is not something I'm familiar with. Looks like it UNPIVOTed the data.... interesting

    commented on Jun 29 2012 6:06AM
    Marc Jellinek
    97 · 2% · 546

  • Marc Jellinek , you can cleverly use VALUES clause starting from version 2008. Refer this http://beyondrelational.com/modules/2/blogs/70/posts/10905/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx

    commented on Jun 29 2012 6:24AM
    Madhivanan
    3 · 39% · 12441

  • @Marc it's just shorter syntax than SELECT 'a' UNION ALL SELECT 'b' UNION ALL .... SQL 2008 and up.

    commented on Jun 29 2012 11:32AM
    ErikEckhardt
    65 · 3% · 887

  • yep it works as unpivot and is one of places where VALUES can be used

    http://visakhm.blogspot.com/2012/05/multifacet-values-clause.html

    commented on Jul 5 2012 12:26AM
    visakhm
    186 · 1% · 248

Your Comment
Sign Up or Login to post a comment.
"Simpler way of calculating Time values stored as numbers" rated 5 out of 5 by 8 readers
Simpler way of calculating Time values stored as numbers , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]