Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Likely easy UPDATE question
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 09/19/2005 :  11:46:01  Show Profile
Hi All -- my first post. I've searched many threads trying to find a similar problem but no one is trying to do this update they way I need it.. I think a trigger might be the solution? let me know what you think


UPDATE	W	
	IF W.DESIRED_WANT_DATE < GETDATE()
		SET 	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, W.DESIRED_WANT_DATE)
	ELSE
		SET	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, RWD.REQUIRED_DATE)
	END IF 	
FROM 
	WORK_ORDER W
		JOIN (SELECT *, CASE CHARINDEX(',', USER_1) WHEN 0 THEN USER_1 ELSE LEFT(USER_1, (CHARINDEX(',', USER_1)-1)) END ISSUE_TO FROM WO_WANT_DATE) WO ON (WO.ROWID = W.ROWID)
		JOIN REQUIREMENT_WANT_DATE RWD ON (RWD.BASE_ID = WO.ISSUE_TO AND RWD.PART_ID = WO.PART_ID) 
		JOIN PART P ON (P.ID = W.PART_ID)


Basically I need help with the IFs that follow the UPDATE, is that possible to enclose the SET statements in there?

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 09/19/2005 :  11:56:26  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message
quote:
UPDATE	W	
	IF W.DESIRED_WANT_DATE < GETDATE()
		SET 	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, W.DESIRED_WANT_DATE)
	ELSE
		SET	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, RWD.REQUIRED_DATE)
	END IF


I guess you want to update w.Desired_want_date not wo.Desired_want_date.
If thats true, use case operator:
update w
  set w.Desired_want_date = case 
                              when w.desired_want_date < getDate() then DATEADD(day, @amount, W.DESIRED_WANT_DATE
                              else DATEADD(day, @amount, RWD.REQUIRED_DATE)
                            end
...

Edited by - mmarovic on 09/19/2005 11:56:55
Go to Top of Page

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 09/19/2005 :  12:41:56  Show Profile
Thanks that looks much more familiar then what I had. I didn't develop the entire query (and I didn't post all of it) but I think WO is a view based on W (WORK_ORDER)

I'll give the case statement a try but I think that'll do it :)

Edit: just following up with a happy thank you and success

Edited by - Kbalz on 09/20/2005 10:00:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000