Differences between two or more records

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

Transact-SQL (2000)

Differences between two or more records

Author

Topic

jj7353
Starting Member

10 Posts

Posted - 2006-07-18 : 09:10:31

Hi, I need some help figuring out a way to find the difference between two or more different records.

Let me try to explain by using some sample data.

1 zzz 07/01/2006 245     10  24.5  0
1 zzz 07/02/2006 220.5   15  12.25  5
1 zzz 07/03/2006 208.25  17  41.65  2
1 zzz 07/04/2006 203.35  100 166.6  83

Now, I have the data for columns 1 through 5 which are:
task_id,
person_alias,
date_task_completed,
estimate_time_to_complete_task,
percentage_of_task_completed

actual_completed_time,
diff_percentage_from_last_request

The last two columns are what I am trying to figure out how to derive. The way the others work is the 245 is a Given amount estimated time we think it will take to finish task 1. Each time they do some work, for task 1, they estimate how much of the total task completed and do so by posting a percentgage completed and continue to do that daily until they completed it 100%.

Between the days I need to determine the actual time they have taken and the difference in percentages.

Hope that makes sense.

Thanks

John

John

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 09:43:43

[code]declare @table table
(
task_id int,
person_alias varchar(10),
date_task_completed datetime,
estimate_time_to_complete_task int,
percentage_of_task_completed decimal(10,2)
)

insert into @table
select 1 , 'zzz', '07/01/2006', 245, 10 union all
select 1 , 'zzz', '07/02/2006', 220.5, 15 union all
select 1 , 'zzz', '07/03/2006', 208.25, 17 union all
select 1 , 'zzz', '07/04/2006', 203.35, 100

select *,
actual_completed_time = percentage_of_task_completed * estimate_time_to_complete_task / 100,
diff_percentage_from_last_request = percentage_of_task_completed -
isnull((select top 1 percentage_of_task_completed
from @table x
where x.task_id = t.task_id
and x.date_task_completed < t.date_task_completed
order by x.date_task_completed desc), percentage_of_task_completed)
from @table t
order by date_task_completed

/* RESULT
task_id person_alias date_task_completed estimate_time_to_complete_task percentage_of_task_completed actual_completed_time diff_percentage_from_last_request
----------- ------------ ------------------------------------------------------ ------------------------------ ---------------------------- --------------------------- ---------------------------------
1 zzz 2006-07-01 00:00:00.000 245 10.00 24.500000 .00
1 zzz 2006-07-02 00:00:00.000 220 15.00 33.000000 5.00
1 zzz 2006-07-03 00:00:00.000 208 17.00 35.360000 2.00
1 zzz 2006-07-04 00:00:00.000 203 100.00 203.000000 83.00

(4 row(s) affected)
*/[/code]

jj7353
Starting Member

10 Posts

Posted - 2006-07-18 : 15:11:04

KH - thank you so much for your help. I believe that is exactly what I need - your a life saver.

John

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources