Complex update - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Complex update

Author

Topic

steve4819
Starting Member

1 Post

Posted - 2008-12-16 : 13:21:44

I have a table which contains contract schedules. The data looks like:
contract_id contract_version sub_sequence product_id work_date activity_date
3177 1 1 1234 01/01/08 02/07/08
3177 1 2 5678 01/02/08 01/08/08
3177 2 1 1234 01/03/08 NULL
3177 2 2 5678 01/04/08 NULL
I need to update the work_date for each product line in the most recent version (2) with the activity_date+6 months from the previous version.
So in the example above the third record would have the work_date updated from 01/03/08 to 02/01/09

I have the following script which does the job, but I wondered if there was a simpler or better way to achieve the same results!

UPDATE schedule
SET work_date =
(SELECT activity_date
FROM schedule AS curr_schedule
WHERE (contract_id = '3177') AND (contract_version = '2' - 1) AND (sub_sequence =
(SELECT MAX(sub_sequence) AS Expr1
FROM schedule
WHERE (contract_id = '3177') AND (contract_version = '2' - 1))) AND (product_id = schedule.product_id))
WHERE (contract_id = '3177') AND (contract_version = '2') AND (sub_sequence = '1') AND (product_id IN
(SELECT product_id
FROM schedule
WHERE (contract_id = '3177') AND (contract_version = '2' - 1) AND (sub_sequence =
(SELECT MAX(sub_sequence) AS Expr1
FROM schedule
WHERE (contract_id = '3177') AND (contract_version = '2' - 1)))))

The script is to have the contract_id and version passed in as parameters eventually.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 14:09:54

[code]UPDATE t
SET t.work_date=DATEADD(mm,6,t1.activity_date)
FROm table t
JOIN table t1
ON t.contract_id=t1.contract_id
AND t.product_id=t1.product_id
AND t.contract_version=t1.contract_version+1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 14:12:59

and just in case there happens to be gap in versions,use this


UPDATE t
SET t.work_date=DATEADD(mm,6,t1.activity_date)
FROM table t
CROSS APPLY 
(SELECT TOP 1  activity_date 
FROM table 
WHERE contract_id=t.contract_id
AND product_id=t.product_id
AND contract_version<t.contract_version
ORDER BY contract_version DESC)t1

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-16 : 14:13:45

[code]
update a set work_date = dateadd(month,6,b.activity_date)
from schedule a inner join schedule b
on a.product_id = b.product_id and a.contract_version > b.contract_version
where a.contract_version = 2[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources