LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » Updating table column based on other record column

Updating table column based on other record column

Options

Previous Topic · Next Topic

mailme180181

Posted: Monday, April 27, 2009 5:30:30 AM

Rank: Newbie

Joined: 4/27/2009
Posts: 1
Points: 3
Where do you live?: singapore

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.

Scott Whigham

Posted: Monday, April 27, 2009 1:24:58 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hi there -

So I only halfway understand the problem... If you could post more details along with some sample queries you've already tried that failed (and why they failed), perhaps it would be more clear?

bklr

Posted: Wednesday, May 06, 2009 4:13:48 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

can u post some sample output too...........

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.