THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Puzzle: Hours Stored As Integers

You have a table where hours are stores as integers and you need to display it in weeks, days and hours
If you have the following table

CREATE TABLE #Hours (hours int)
INSERT INTO #Hours
SELECT 5 UNION ALL
SELECT 55 UNION ALL
SELECT 125 UNION ALL
SELECT 1225 UNION ALL
SELECT 555 UNION ALL
SELECT 721 UNION ALL
SELECT 719

 


The expected output is this

 

Hours

weeks

days

hours

5

0

0

5

55

0

2

7

125

0

5

5

1225

7

2

1

555

3

2

3

721

4

2

1

719

4

1

23

Big deal right? So here is the deal, can you generate this output with code that is as short as possible. You do not need to alias the columns.

The code that I have is 50 characters.

Published Monday, April 14, 2008 12:34 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Simon Worth said:

I'm not sure I understand the question.  You say you want it displayed in Weeks / Days / Hours, yet the output you have doesn't conform to the output you want, as well, the output columns don't match your header.

You have Hours - Weeks - Days - Hours.  So shouldn't the first and last column for the output be the same?

April 14, 2008 2:05 PM
 

Sam said:

I can do it in 55 characters; I'm rather curious what your solution is :)

April 14, 2008 2:31 PM
 

Denis Gobo said:

This is why developers don't write specs  :-)

the first column is the column in the table

so for example

55 0 2 7

this means 55 hours = 2 days(48 hours) + 7 hours

April 14, 2008 2:32 PM
 

Denis Gobo said:

Sam, I am pretty sure I know what you have use a * and eliminate a space  :-)

April 14, 2008 2:54 PM
 

Sam said:

Actually, I just took it down to 48 characters based on your suggestions. Order of operations...

April 14, 2008 3:02 PM
 

Denis Gobo said:

I am down to 49  :-(

select*,hours/168,hours/24%7,hours%24 from #Hours

How did you get 48?

April 14, 2008 3:11 PM
 

Sam said:

I removed the space between 24 and From

April 14, 2008 3:18 PM
 

Denis Gobo said:

I tried that but since it wasn't blue anymore I didn't bother running it at all

April 14, 2008 3:20 PM
 

david wei said:

48 characters, you don't need space before [*] and even don't need space before the [from] :-)

runs under both SQL 2000 and SQL 2005

select*,hours/168,hours/24%7,hours%24from #Hours

hours (No column name) (No column name) (No column name)

5 0 0 5

55 0 2 7

125 0 5 5

1225 7 2 1

555 3 2 3

721 4 2 1

719 4 1 23

April 14, 2008 3:26 PM
 

Stephen Moore said:

Got it in 50.  Getting from 52 to 50 was the hardest part for me.

April 14, 2008 3:30 PM
 

Denis Gobo said:

Nice, now we created PERL out of SQL  ;-)

April 14, 2008 3:30 PM
 

Stephen Moore said:

Dang --  I had the white space figured out, I just wasn't using the best formula for calculating days.  I had

select*,hours/168,hours%168/24,hours%24from #hours

April 14, 2008 3:44 PM
 

Further Challenge said:

If the create table was:

CREATE TABLE #Hours (hourstaken int)

Then the length would be 63 with

select*,hourstaken/168,hourstaken/24%7,hourstaken%24from #Hours

but there is a shorter way.

Have a guess.....

April 15, 2008 11:07 AM
 

Denis Gobo said:

CREATE TABLE #Hours (hourstaken int)

INSERT INTO #Hours

SELECT 5 UNION ALL

SELECT 55 UNION ALL

SELECT 125 UNION ALL

SELECT 1225 UNION ALL

SELECT 555 UNION ALL

SELECT 721 UNION ALL

SELECT 719

I get 63 characters

select*,h/168,h/24%7,h%24from(select hourstaken'h'from #Hours)x

or

select*,h/168,h/24%7,h%24from(select hourstaken h from #Hours)x

April 15, 2008 11:25 AM
 

Further Challenge said:

You need to use a CTE

with h(h)as(select*from #hours)select*,h/168,h/24%7,h%24from h

April 15, 2008 11:55 AM
 

Scott R. said:

Another derived version of Denis's SQL puzzle - this one storing an integer seconds value in the table and the query returning the seconds from the table along with computed values for weeks, days, hours, minutes, and seconds (within minutes):

--  Seconds stored as integers puzzle.

--  Derived from Denis Gobo's blog posting from 04/14/2008:

--  http://sqlblog.com/blogs/denis_gobo/archive/2008/04/14/6197.aspx

CREATE TABLE #Seconds (seconds int)

INSERT INTO #Seconds

SELECT 5050 UNION ALL

SELECT 550055 UNION ALL

SELECT 1250125 UNION ALL

SELECT 1225221 UNION ALL

SELECT 555000555 UNION ALL

SELECT 721000721 UNION ALL

SELECT 719000719 UNION ALL

SELECT 71900719 UNION ALL

SELECT 7190719 UNION ALL

SELECT 719719

Select * From #Seconds

--  Functioning result with formatted SQL and aliased column names

--  (not the shortest length SQL statement, but more readable)

Select

 seconds TotalSeconds,

 (seconds / 604800) As weeks,

 (seconds / 86400) % 7 As days,

 (seconds / 3600) % 24 As hours,

 (seconds / 60) % 60 As minutes,

 (seconds % 60) As seconds

From #Seconds

--  Functioning result with unformatted SQL and aliased column names

--  (shorter - 151 characters - but less readable and not the shortest length SQL statement)

Select seconds TotalSeconds, seconds/604800 weeks, seconds/86400%7 days, seconds/3600%24 hours, seconds/60%60 minutes, seconds%60 seconds From #Seconds

--  Functioning result with unformatted SQL, without aliased column names,

--  and removing spaces after commas, before *, and before From.

--  (yet shorter - 92 characters - much less readable)

Select*,seconds/604800,seconds/86400%7,seconds/3600%24,seconds/60%60,seconds%60From #Seconds

--  Functioning result with unformatted SQL, without aliased column names,

--  removing spaces after commas, before *, and before From,

--  and using a sub-select to shorten seconds column name to s.

--  (shortest - 86 characters - much less readable)

--  The artificial sub-query name qualifier i at the end of the overall query appears to be necessary.

--  I couldn't get it to run without a sub-query name (1 extra character - wow!)

Select*,s/604800,s/86400%7,s/3600%24,s/60%60,s%60From(Select seconds s From #Seconds)i

--  While the pattern of divide and modulo constants to use in the original hours example

--  may have been apparent to some, the extensions from this version that uses seconds

--  make the pattern of divide and modulo constant values more apparent, as described below.

--  The modulo constant for a given level is the number of units of the given level to make one unit

--  of the next higher level.

--  For example:

--  60 seconds in one minute

--  60 minutes in one hour

--  24 hours in one day

--  7 days in one week

--  There is no need for modulo operator or constant on the highest level - weeks in this case

--  (same as if a modulo constant of the divisor was used).

--  The divisor constant for a given level is the product of the modulo values from all previous levels

--  through the current level.

--  No divide operator or divisor constant is needed for the lowest level - seconds in this case

--  (same as if a divide by 1 was used).

--  For example:

--  A divisor of 60 for minutes (60 seconds per minute)

--  A divisor of 3,600 for hours (60 seconds per minute * 60 minutes per hour)

--  A divisor of 86,400 for days (60 seconds per minute * 60 minutes per hour * 24 hours per day)

--  A divisor of 604,800 for weeks

--      (60 seconds per minute * 60 minutes per hour * 24 hours per day * 7 days per week)

--  Both examples (hours-based and seconds-based) benefit in brevity (fewest characters

--  in the SQL statement) from the use of integer field values and integer divisors,

--  which resulted in implicit integer results without having to explicitly use

--  the CAST or CONVERT functions to get integer results.

Scott R.

April 15, 2008 12:41 PM
 

Lionel said:

You can do better than using a cte:

select*,a/168,a/24%7,a%24from(select*from #hours)h(a)

May 15, 2008 7:35 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement