automatically calculate % complete??

Question

automatically calculate % complete??

sqlpro

Hall of Fame

Points: 3859
More actions
June 14, 2004 at 3:20 pm

#108398

Hi friends
I've 2 tables.assignment(pk) and worktime(fk)
assignment has fields like
staffid,estimatedduration,status(i.e how much % an assignemt completed),assid
worktime table has
starttime,stoptime,fk_assid
everytime a staff starts new worktime an entry will be created in worktime.
my question is,
how can automatically update the percentage complete(i.e status field). say Allocate 2 days, spend 1 day and the % figure would be 35. Allocate 2 days spend two days and the % figure would be 70.(when this assignment tested then it is 100% but for the time being i've to update upto 70% only) .
Can u guys guide me how can i achiev this
sample data..
staff table
----
assid, staffid,estimatedduration,status(i.e how much % an assignemt completed)
381 ellen 1d            35(rough figures)
382 karl 12h          75
383 RAJ 6min        60
wortime table
-----
fk_assid,starttime,stoptime
381 2003-12-03 10:53:00.000 2003-12-03 13:49:00.000
381 2003-12-03 12:12:00.000 2003-12-03 13:49:00.000
381 2003-12-03 10:29:00.000 2003-12-03 13:49:00.000
381 2003-12-03 10:44:00.000 2003-12-03 13:49:00.000
381 2003-12-03 10:32:00.000 2003-12-03 13:49:00.000
381 2003-12-03 10:27:00.000 2003-12-03 13:49:00.000
381 2003-12-03 13:49:00.000 2003-12-03 13:49:42.640
381 2003-12-03 10:51:00.000 2003-12-03 13:49:00.000
381 2003-12-03 13:51:00.000 2003-12-03 13:51:26.467
381 2003-12-03 10:52:00.000 2003-12-03 13:49:00.000
381 2003-12-03 10:45:00.000 2003-12-03 13:49:00.000
382 2003-12-03 16:21:00.000 2003-12-03 16:21:00.000
382 2003-12-03 16:20:00.000 2003-12-03 16:21:00.000
383 2003-12-05 16:51:00.000 2003-12-12 16:40:23.550
383 2003-12-05 16:50:00.000 2003-12-05 16:51:00.000

many thanks for ur ideas 🙂
---------

--
cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Next what does 1 day represent? 24hrs? And are you just taking the sum of time for all start and stop times to calculate the values? Your comments and sample leave me confused.

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

First off what version of SQL are you using?

--sql server 2000 dev edition

Next what does 1 day represent?

--8hours

And are you just taking the sum of time for all start and stop times to calculate the values?

--Yes

Thanks antares686

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Ok this is where you confuse me.

"Allocate 2 days spend two days and the % figure would be 70."

If a allocate 2 and spend 2 days then how am I getting 70%?

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Ok, first thing I know I would do thou at this point is factor down to a common element on the estimatedduration column.

Since so far your smallest unit is 6 min (or mintues to be precise) store all values as number of minutes.

1 day or 8 hrs = 60 * 8 or 480 min

12 hrs = 60 * 12 or 720 min

and store a visual rep in another field sayign whether to convert to visual for hours days or mintues.

This way you have a common element of work units.

Then you should be able to SUM all the differences in mintues between start and stop times grouped by assid. Which then you can compare for calcualtion for the percentage.

I think IMHO you are over complicating things with the mixed values in the one column.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Then it would be a simple matter of a trigger each time the value is updated. Or better get rid of the column and make a view with that column calculated.

sqlpro Hall of Fame Points: 3859 More actions · Answer 6

actually it becomes 100 when this assignment tested.

ok ,to make things easier we'll say

"Allocate 2 days spend two days and the % figure would be 100"

"Allocate 2 days, spend 1 day and the % figure would be 50."

hope i am clear now