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 | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  04:56:53  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
huwaa huwaaa.. peso what r u doing.. the first one works excellento already.. this 2nd one got syntax error ',' (still checking) but i think ill go for the 1st one.. purfecto, shorter.. the 2nd one, i couldnt think how to digest

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/17/2007 :  05:01:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I have corrected the "CURSOR" approach for you.
Please test them both and post back here the times for completion using both approaches.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/17/2007 05:19:44
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  20:54:07  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
both took about the same time.. very fast.. 00:00:00 .. with that i grant u MVP : Maya Most valuab;e Professional.. LOL

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

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 04/17/2007 :  22:07:49  Show Profile  Reply with Quote
MVP : Most Valuable Peso
{:D]


KH

Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  22:16:07  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
hahaha.. betul betul(right2).. that match better

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

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  02:53:38  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
okay back here peter,, peter,,, peter parker..
if cursor approach is going to be applied.. i dunno how to transfer this to ur 2nd solution :-

-- Prepare sample data
DECLARE	@Table1 TABLE (ID varchar(12), Qty MONEY, RecvDate DATETIME, BB char(1) )


INSERT	@Table1
SELECT	'A', 18, '2006-08-13 00:00:00.000', 'Y' UNION ALL
SELECT	'B', 17, '2006-08-14 00:00:00.000', 'Y' UNION ALL
SELECT	'C', 5,  '2006-08-14 00:00:00.000', 'Y'UNION ALL
SELECT	'B1', 17, '2006-08-14 00:00:00.000', 'Y' UNION ALL
SELECT	'C2', 5,  '2006-08-14 00:00:00.000', 'Y'UNION ALL
SELECT	'b2', 18, '2006-08-22 00:00:00.000' , 'N'


-- Stage the data
DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1), ID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))

DECLARE @WantedValue INT
SET @WantedValue = 50

WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
	INSERT		@Stage (ID, Qty, RecvDate, BB)
	SELECT TOP 1	t1.ID,
			t1.Qty,
			t1.RecvDate,
			t1.BB
	FROM		@Table1 AS t1
	LEFT JOIN	@Stage AS s ON s.ID = t1.ID
	WHERE		s.ID IS NULL
	ORDER BY	t1.RecvDate,
			t1.Qty DESC

IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
	BEGIN
	DECLARE @tRecID INT, @tID VARCHAR(12), @tQty MONEY, @tRecvDate DATETIME,@tBB char(1), @PickQTy money 
	
	SELECT TOP 1 @tRecID=RecID, @tID=ID, @tQty=Qty, @tBB=BB 
	FROM @Stage Order BY RecID DESC
	
	SET @PickQty = @WantedValue - ((SELECT COALESCE(SUM(Qty), 0) FROM @Stage)- @tQty)
		
	IF @tBB='Y' --break last record to suit @wantedvalue
		BEGIN
			UPDATE @Stage
			SET Qty=@PickQty
			WHERE RecID=@tRecID
		
			SELECT * FROM @Stage
		END
	ELSE --take all 
		SELECT * FROM @Stage
	END
ELSE
	
select * from @Table1

and.. this while loop i need to make 3 copy wif differences in ORder By ... recvdate, recvdat desc, and expdate
note that the sample data will be replaced wif :-

CREATE TABLE #DataList
		( ItemStorageID varchar(10), Qty float, PickNm varchar(50), recvDate datetime, ExpDate datetime )

INSERT		#DataList
		(
			ItemStorageID,
			Qty,	
			PickNm,
			RecvDate, 
			ExpDate
		)
			EXEC  SP_StageAllData
			@JobID = @JobID,
			@DocRefID = @DocRefID,
			@Shipper= @Shipper,	
			@ItemID= @ItemID,
			@CustomLotNo = @CustomLotNo,
			@WantedValue=@Wantedvalue

thanks.. im getting faint by now.. run out of words to explain.. cry cry cryyy

~~~Focus on problem, not solution~~~

Edited by - maya_zakry on 04/18/2007 02:54:18
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/18/2007 :  03:23:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please do tell what you are trying to accomplish AFTER you have got all the records in the WHILE/CURSOR loop.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  04:50:24  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
i want to update the last record qty in @stage if sum(qty) > @wantedvalue to the balance of @wantedqty IF BB=Y (breakable)..
eg : @wantedValue=26..
the while loop produce 2 record with sum(qty) exceeded say 30.. say records produce by whileloop:-
1. 20 Y
2. 10 Y --since this BB=Y, so i can break it, take only 6 to match 20+6=26, if BB=N then need to take all..

so, if the BB=Y, i will break the 10 to the suppose balance =6.. so i will update the @stage to=6 making the result 20+6=26 to match the @wantedvalue=26.. BUT if the BB=N, then i have to display all the @stage as the final result...

all this process need to be in 3 IF statement just to separate the ORDER By recvdate, recvdate desc, and exp date.
is my sentences clear..

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/18/2007 :  04:55:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't understand anything of what you just wrote.
You want to correct to last records (highest recid) by reducing the value so that the total sum is equal to @wantedvalue?
Or you want to insert a new negative record so that the total sum is equal to @wantedvalue?

Do you have sample data to show instead?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  05:03:33  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
yes.. i want to correct last record being insert (highest recid), and reduce the value to match @wantedvalue ONLY if the BB=Y. if BB=N, then donrt need to do anything, just display it out..
(BB u can get from my latest sample data:-)

-- Prepare sample data
DECLARE	@Table1 TABLE (ID varchar(12), Qty MONEY, RecvDate DATETIME, BB char(1) )


INSERT	@Table1
SELECT	'A', 18, '2006-08-13 00:00:00.000', 'Y' UNION ALL
SELECT	'B', 17, '2006-08-14 00:00:00.000', 'Y' UNION ALL
SELECT	'C', 5,  '2006-08-14 00:00:00.000', 'Y'UNION ALL
SELECT	'B1', 17, '2006-08-14 00:00:00.000', 'Y' UNION ALL
SELECT	'C2', 5,  '2006-08-14 00:00:00.000', 'Y'UNION ALL
SELECT	'b2', 18, '2006-08-22 00:00:00.000' , 'N'


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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/18/2007 :  05:16:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You, oh why, do you make things more complicated?
Try this.
-- Prepare sample data
DECLARE	@Table1 TABLE (ID VARCHAR(12), Qty MONEY, RecvDate DATETIME, BB CHAR(1))

INSERT	@Table1
SELECT	'A', 18, '2006-08-13', 'Y' UNION ALL
SELECT	'B', 17, '2006-08-14', 'Y' UNION ALL
SELECT	'C', 5,  '2006-08-14', 'Y'UNION ALL
SELECT	'B1', 17, '2006-08-14', 'Y' UNION ALL
SELECT	'C2', 5,  '2006-08-14', 'Y'UNION ALL
SELECT	'b2', 18, '2006-08-22', 'N'

-- Initialize search parameter
DECLARE	@WantedValue MONEY,
	@Sum MONEY

SELECT	@WantedValue = 36,
	@Sum = @WantedValue

-- Stage the data
DECLARE	@Stage TABLE (RecID INT IDENTITY(1, 1), ID VARCHAR(12), Qty MONEY, RecvDate DATETIME, BB CHAR(1))

DECLARE	curStage CURSOR	FORWARD_ONLY READ_ONLY FOR
			SELECT		ID,
					Qty,
					RecvDate,
					BB
			FROM		@Table1
			ORDER BY	RecvDate,
					Qty DESC,
					BB

DECLARE	@ID VARCHAR(12),
	@Qty MONEY,
	@RecvDate DATETIME,
	@BB CHAR(1)

OPEN curStage

FETCH	NEXT
FROM	curStage
INTO	@ID,
	@Qty,
	@RecvDate,
	@BB

WHILE @@FETCH_STATUS = 0 AND @Sum > 0
	BEGIN
		SET @Sum = @Sum - @Qty

		INSERT	@Stage (ID, Qty, RecvDate, BB)
		SELECT	@ID,
			@Qty,
			@RecvDate,
			@BB

		FETCH	NEXT
		FROM	curStage
		INTO	@ID,
			@Qty,
			@RecvDate,
			@BB
	END

CLOSE curStage
DEALLOCATE curStage

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

-- Show the result
SELECT		*
FROM		@Stage
ORDER BY	RecID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  05:25:31  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
yup tyup peter... that's right what i want.. complicated my sentences are..
so this one dont have issue if i put the IF like :-

IF condition 1
'cursor solution with order by recvdate'

else if consition 2
'cursor solution with order by recvdate desc'

else
blablab????



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

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  05:28:23  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
problem is i dont quite understand this cursor approcah.. the code seem so long.. will it affect the process if i put the IF?? right now it work.. but later on im afraid if i need to add anything.., then i dunno where to modify .. and then i jump here and u get mad.. ahhahahhaha

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/18/2007 :  05:54:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have a VERY VERY bad case of database design if you need to alter the specifications once a week.

Try this approach
if condition = 1
	DECLARE	curStage CURSOR	FORWARD_ONLY READ_ONLY FOR
				SELECT		ID,
						Qty,
						RecvDate,
						BB
				FROM		@Table1
				ORDER BY	RecvDate,
						Qty DESC,
						BB
else
	DECLARE	curStage CURSOR	FORWARD_ONLY READ_ONLY FOR
				SELECT		ID,
						Qty,
						RecvDate,
						BB
				FROM		@Table1
				ORDER BY	RecvDate DESC,
						Qty,
						BB

I only get mad when you hold back your specs all the time.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/18/2007 :  06:53:57  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
u mean put this before we open the curStage?

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/18/2007 :  06:59:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. You replace the original declaration of the CURSOR with this optional check declaration.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  00:16:29  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
hello,
ive tried it out today.. worked excellento!!! pheww.... finally.. thanks peter. awesome u r!

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/19/2007 :  01:06:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well done!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/19/2007 :  04:30:15  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
peter..
i have one more issue.. how do i limit the reducing so that it wont exceed the total qty of our #sampledata?
eg : @wantedvalue=154, total record from #sampledata=153, previously we update the last recid=1 to match the @wantedvalue, but this exceed the total records in #sampledata

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 04/19/2007 :  04:33:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE	@Stage
SET	Qty = Qty + @Sum
WHERE	RecID = @@IDENTITY
	AND BB = 'Y'
	AND Qty > ABS(@Sum)

Do you want my PayPal account? Send a private message...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next 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.12 seconds. Powered By: Snitz Forums 2000