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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get firsrt in date out
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:34:06  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
well so far this works

DECLARE @TotalStage FLOAT
SELECT @TotalStage = SUM(Qty)  FROM #Datalist


IF NOT @Sum <= @TotalStage
UPDATE	@Stage
SET	Qty = Qty + @Sum
WHERE	RecID = @@IDENTITY
	AND BB = 'Y'


~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:35:53  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
huhh?? u so fast?>? didnt give me chance to figure out.. well ill use that one u posted just now.. he he ..

~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/19/2007 :  04:35:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See my previous response.
It is not clear if you want all records to be subtracted or only the last.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:38:19  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
no no peso.. yurs didnt work.. it keep on exceeding 153 to 156.. (i was about to ask yur paypal acc)... waahahhahaha

~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/19/2007 :  04:40:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Nice laugh.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:44:48  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
oh no.. it didnt add according to @sum when BB=Y

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:46:01  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
peter how to make it update the @stage with @SUm only if the @sum less than #sampledata?

~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/19/2007 :  04:50:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You figure it out.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:50:59  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
maybe a bit long, but it workde


DECLARE @TotalData FLOAT, @TotalStage FLOAT
SELECT @TotalData = SUM(Qty)  FROM #Datalist --153
SELECT @TotalStage = SUM(Qty)  FROM @Stage --153

IF @Sum + @TotalStage  <= @TotalStage
	UPDATE	@Stage
	SET	Qty = Qty + @Sum
	WHERE	RecID = @@IDENTITY
		AND BB = 'Y'


~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/19/2007 :  04:53:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is equal to what I wrote before
UPDATE	@Stage
SET	Qty = Qty + @Sum
WHERE	RecID = @@IDENTITY
	AND BB = 'Y'
	AND Qty > ABS(@Sum)
Just change to
UPDATE	@Stage
SET	Qty = Qty + @Sum
WHERE	RecID = @@IDENTITY
	AND BB = 'Y'
	AND @Sum < 0
But this technique will lead you into a number of troubles...
What if value of SUM is larger than the Qty for last record?

THINK!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:59:41  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
which one is equal to u wrote b4?
which techniique lead to troubles?


~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  05:00:34  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
if @sum is larger than it display all in #sampledata even though it less than @wantedvalue, that is okay

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  05:02:43  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
so ill use this one..
AND @Sum < 0
hehehhe.. fastest shorcut .. u crazee geek

~~~Focus on problem, not solution~~~
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 04/19/2007 :  05:05:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks.
Can you spell "toasted"?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  05:10:06  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
T-O-A-S-T-E-D
what is it wif toasted

~~~Focus on problem, not solution~~~
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000