Hi Rajani,

I'm not sure why do you want to store the 'status' if you can get it from existing data any time you need it. Assuming that 'estimatedduration' is stored in minutes, here's the query that should do that.

SELECT st.assid, st.staffid, 
	(SUM(DATDIFF(minute, wt.starttime,wt.stoptime) / 
           MAX(st.estimatedduration)) * 100 AS status
	FROM staff st 
		JOIN wortime wt ON st.assid = wt.fk_assid
	GROUP BY assid, staffid

>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