updating quert - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

updating quert

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-11 : 15:57:03

I have the following table

ID UID number Initial_date close_date update
3511 21346 201005 2010-06-0723:35:002010-06-0723:35:00 0
25202 21346 201005 2009-09-0723:35:002011-06-0923:53:35:00 1
23652 76543 567433 2005-08-0823:43:00 2005-23--07 23:45:00 1
23653 76543 567433 2011-9-0923:45:00 2012-23-09 23:56:00 0

I want to update the initial_date with the earliest date and close_date with the latest date fo a given UID and number. so as long as UID and number
are same, I need to update both initial_date and close date

so in the above example, I want to update the Initial_date for UID 21346 and number 201005 to 2010-06-0723:35:00 and close_date to 2011-06-0923:53:35:00
and for UID 76543 and number 567433 , I need to update initial date to 2005-08-0823:43:00 and close_date to 2012-23-09 23:56:00.

the UID and number are not in order.

How can I acheive this

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:27:57

[code]
UPDATE t
SET t.Initial_date = t1.MInInitial,
t.close_date = t1.MaxClose
FROM table t
INNER JOIN (SELECT UID,number,MIN(Initial_date) AS MInInitial,MAX(close_date) AS MaxClose
FROM table
GROUP BY UID,number
)t1
ON t1.UID= t.UID
AND t1.number = t.number
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources