Updating table column based on other record column

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 2005 Forums

Transact-SQL (2005)

Updating table column based on other record column

Author

Topic

mailme180181
Starting Member

3 Posts

Posted - 2009-04-27 : 06:05:24

I posted this problem on some other portals also but couldnt get any proper solution .Query details are as follows.

There are some traps which has to be cleaned on regular basis.Each trap has unique id here it is GT_Id.
GT_Id is not primary key.primary key is some auto gen. no.
Every row in table contains data about GT_Id last cleaned date and predicted cleaning date.
(Predicted cleaning date is coming based on some other column value any way it is for info only)
Now for any trap "No_of_days_exceeding_predicated_date" is difference between current row "Last_cleaned_date"
and its "Predicted_cleaning_date" colums which is not in current row.We have to calculate it on the basis of last row for that trap (GT_Id) where its Predicted_cleaning_date was decided.
Sample data i am putting for making it more clear.

GT_ID Last_cleaned_date Predicted_cleaning_date No_of_days_exceeding_predicated_date
G1 21/1/2009 30/1/2009 3
G3 16/1/2009 24/1/2009 1
G1 12/1/2009 18/1/2009 5
G3 12/1/2009 15/1/2009 2 Since predicted date is 10th for this trap GT3 (from GT3 last record).
G3 1/1/2009 10/1/2009 0 Since it is first record for trap G3.
G1 1/1/2009 7/1/2009 0 Since it is first record for trap G1.

Note:
We can check last record for particular trap on the basis of Last_cleaned_date.

I hope this makes understanding about issue clear.I would be great if you could give me solution for this.
I am not able to solve it for long time and i tried every possible solution i knew.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-27 : 07:44:35

Try this:


declare @Sample table( GT_ID int, LastDate datetime, PredDate datetime, daysexceed int)
insert @Sample
select 3,'20090101','20090110',null union all
select 3,'20090112','20090115',null union all
select 3,'20090116','20090124',null union all
select 1,'20090101','20090107',null union all
select 1,'20090112','20090118',null union all
select 1,'20090121','20090130',null

update s1
set s1.daysexceed=isnull(DATEDIFF(day,s2.PredDate,s1.LastDate),0)
from
(select 
GT_ID, 
LastDate,
PredDate, 
daysexceed,
ROW_NUMBER() over (partition by GT_ID order by GT_ID,LastDate) as rownum
from @Sample) s1
left join 
(select 
GT_ID, 
LastDate,
PredDate, 
daysexceed,
ROW_NUMBER() over (partition by GT_ID order by GT_ID,LastDate) as rownum
from @Sample) s2
on s1.GT_ID=s2.GT_ID and s1.rownum-1=s2.rownum

select * from @Sample order by GT_ID,LastDate

--Result is:
GT_ID	LastDate	PredDate	daysexceed
1	2009-01-01 00:00:00.000	2009-01-07 00:00:00.000	0
1	2009-01-12 00:00:00.000	2009-01-18 00:00:00.000	5
1	2009-01-21 00:00:00.000	2009-01-30 00:00:00.000	3
3	2009-01-01 00:00:00.000	2009-01-10 00:00:00.000	0
3	2009-01-12 00:00:00.000	2009-01-15 00:00:00.000	2
3	2009-01-16 00:00:00.000	2009-01-24 00:00:00.000	1

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

mailme180181
Starting Member

3 Posts

Posted - 2009-04-27 : 20:47:57

Hats off to you webfred.
I have been trying for long time for this.
I posted query on many forums i didnt get result what i expected.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources