Percentage of day used, free - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Percentage of day used, free

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2004-01-07 : 14:20:35

I am considering a view off a monthly calendar to show percentage of day used and color code with a (green, yellow, red) schema. Anyway, just curious on storage of data and ideas on how to SQL return the dataset.

I currently have data with start and end dates(datetime).

StartDate EndDate
'1-10-2004 10:00:00' '1-10-2004 11:00:00'
'1-10-2004 13:00:00' '1-10-2004 14:00:00'
'1-09-2004 08:00:00' '1-21-2004 17:00:00'
etc

Anyway I think I am trying to get the day and %used back. ie
(the day would be a variable but most likely 8AM-5PM)
1 %used
2 %used
3 %used
..

Thanks for any input, I understand a lot of this is probably on the client but, just wondering if any of you ever tackled something similar and has some advice.

Thanks

slow down to move faster...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-07 : 14:28:19

SELECT DATEDIFF(s, StartDate, EndDate)/(your variable in seconds) * 100
FROM Table1

"your variable in seconds" could be stored in a table and joined on the employee id or whatever you have.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-07 : 15:06:44

The following query will work provided you have the input params starttime and endtime which determine total utilization per day.

declare @starttime datetime, @endtime datetime

set @starttime = '1/1/2003 08:00:00'
set @endtime = '1/1/2003 17:00:00'

select startdate,enddate,((convert(decimal,datediff(ss,startdate,enddate))) / datediff(ss,@starttime,@endtime) ) * 100 as percent_used
from dates

But what is going on with the 3 line of data?
If this will be stored as such you will need to add a parameter indicating how many days/week a person can be utilized and the query will need to be modified to determine ttl utlilization for the day-spanning duration.

skillile
Posting Yak Master

208 Posts

Posted - 2004-01-07 : 16:59:23

Thanks for the response

The 3 lines of data are stored in the events table something like this

userid,
sdate,
edate,
event stuff...

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources