Display result in hours, weeks and month

Hello!
I have data stored in a fld which is a datetime difference in hours. I
need to display it in 'Hours' (if it is less than 24), 'Ws' (if it is
greater than 168) and in 'Month' (if it is greater than 720). Is there
an easy to do it?
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***You forgot to mention dates
take a look at this, you might want to include years also
create table #timeStuff (TimeField int)
insert into #timeStuff
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
select TimeField,case
when TimeField < 24 then 'hours'
when TimeField between 24 and 168 then 'days'
when TimeField between 169 and 719 then 'ws'
when TimeField > 720 then 'months'
end
from #timeStuff
drop table #timeStuff
http://sqlservercode.blogspot.com/|||Would this work?
select TimeField,case
when TimeField < 24 then TimeField * 1.0
when TimeField between 24 and 168 then Timefield/24.0
when TimeField between 169 and 719 then Timefield/(24.0*7.0)
when TimeField > 720 then Timefield/(24.0*30.0)
end as result
from #timeStuff
(using DDL from second message:
create table #timeStuff (TimeField int)
insert into #timeStuff
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 issue here is that you are storing an interval, not hard dates, but
you want to figure out the number of months in hours. Since some
months are 30 days and some are 31 days (february-28 days), you won't
get an exact result here.
The other calculations should work since there are always 24 hours in a
day and 7 days in a w.
Christian|||No. This is what I want based on your data.
5 = 5 hurs
55 = 2 days 7 hrs
125 = 5 days 2 hrs
1225 = 1 month ws and hrs
555 = 3 ws and hours
and so on ...
*** Sent via Developersdex http://www.examnotes.net ***|||drop table #timeStuff
go
create table #timeStuff (hours int)
insert into #timeStuff
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
go
this should give you what you want, though I don't know how you want to
calculate months since that is a variable amount of time (ws are exactly
7 days (well, except for leap years, but for all intents and purposes :))
select hours as totalHours, hours/168 as ws, (hours % 168) / 24 as days,
((hours % 168) % 24) as hours
from #timestuff
/*
totalHours ws 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
*/
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OlHMYqKOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> No. This is what I want based on your data.
>
> 5 = 5 hurs
> 55 = 2 days 7 hrs
> 125 = 5 days 2 hrs
> 1225 = 1 month ws and hrs
> 555 = 3 ws and hours
> and so on ...
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks a lot everyone!!!!
*** Sent via Developersdex http://www.examnotes.net ***