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?

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

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

I removed the space between 24 and From

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

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

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

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.....

You need to use a CTE

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

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.

You can do better than using a cte:

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