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.
| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-06-14 : 17:15:16
|
| Hi friendsI've 2 tables.assignment(pk) and worktime(fk)assignment has fields likestaffid,estimatedduration,status(i.e how much % an assignemt completed),assidworktime table has starttime,stoptime,fk_assideverytime 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(same sql to calculate % complete)sample data..staff table----assid, staffid,estimatedduration,status(i.e how much % an assignemt completed)381 ellen 1d 35(rough figures)382 karl 12h 75383 RAJ 6min 60wortime table-----fk_assid,starttime,stoptime381 2003-12-03 10:53:00.000 2003-12-03 13:49:00.000381 2003-12-03 12:12:00.000 2003-12-03 13:49:00.000381 2003-12-03 10:29:00.000 2003-12-03 13:49:00.000381 2003-12-03 10:44:00.000 2003-12-03 13:49:00.000381 2003-12-03 10:32:00.000 2003-12-03 13:49:00.000381 2003-12-03 10:27:00.000 2003-12-03 13:49:00.000381 2003-12-03 13:49:00.000 2003-12-03 13:49:42.640381 2003-12-03 10:51:00.000 2003-12-03 13:49:00.000381 2003-12-03 13:51:00.000 2003-12-03 13:51:26.467381 2003-12-03 10:52:00.000 2003-12-03 13:49:00.000381 2003-12-03 10:45:00.000 2003-12-03 13:49:00.000382 2003-12-03 16:21:00.000 2003-12-03 16:21:00.000382 2003-12-03 16:20:00.000 2003-12-03 16:21:00.000383 2003-12-05 16:51:00.000 2003-12-12 16:40:23.550383 2003-12-05 16:50:00.000 2003-12-05 16:51:00.000many thanks for ur ideas :-)----------- cheersCheers |
|
|
mtomeo
Starting Member
30 Posts |
Posted - 2004-06-15 : 11:52:13
|
| Personally, I wouldn't store the status in your DB if it can be computed from other DB fields. I guess you could write a trigger (on the Work Table) to update the status field based on your logic. But as I said, if it were up to me I would compute the percent complete at runtime... |
 |
|
|
|
|
|
|
|
automatically updating % complete