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

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/16/2007 :  04:52:40  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
hi all,
how do i select all the first date in first? here's my sample data :-

-- Prepare sample data
DECLARE	@DataList TABLE (ItemStorageID VARCHAR(2), Qty float, recvDate datetime)

INSERT	@DataList
SELECT	'A1', 24, '2006-11-17 00:00:00.000' UNION ALL
SELECT	'A4', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A5', 24, '2006-11-17 00:00:00.000' UNION ALL
SELECT	'A2', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A3', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A6', 24, '2006-08-11 00:00:00.000' 

DECLARE	@Data TABLE (ItemCount INT, itemQty float)

INSERT	@Data
SELECT	4, 24

SELECT dl.ItemStorageid, dl.RecvDate, dl.Qty
FROM		@Datalist AS dl
INNER JOIN @Data AS dt ON dt.ItemQty=dl.Qty
WHERE dt.ItemCount >= (SELECT COUNT(*) FROM @Datalist AS x WHERE x.Qty=dl.Qty AND x.ItemStorageID <=dl.ItemStorageID)

will give me
A1 2006-11-17 00:00:00.000 24.0 ****
A4 2006-08-11 00:00:00.000 24.0
A2 2006-08-11 00:00:00.000 24.0
A3 2006-08-11 00:00:00.000 24.0

when i expect it list all the first date
A1A6 2006-08-11 00:00:00.000 24.0 ****
A4 2006-08-11 00:00:00.000 24.0
A2 2006-08-11 00:00:00.000 24.0
A3 2006-08-11 00:00:00.000 24.0

thankss in advance

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

Edited by - maya_zakry on 04/16/2007 07:05:41

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/16/2007 :  04:54:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using SQL Server 2000 or SQL Server 2005?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/16/2007 :  05:04:47  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
hi peter,
im using :-
Microsoft SQL Enterprise Manager

Microsoft Corporation

Version: 8.0

Microsoft SQL server 2000



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

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/16/2007 :  05:20:58  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
There is no record where ItemStorageID = 'A1' and recvDate = '2006-08-11'. Then how can you expect the output you stated?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/16/2007 :  05:21:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have to tell us the business rules.
What are you trying to accomplish?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/16/2007 :  07:09:05  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
harsh,
sorry wrong expected output.. i've edit that output part..
A6 2006-08-11 00:00:00.000 24.0 ****
A4 2006-08-11 00:00:00.000 24.0
A2 2006-08-11 00:00:00.000 24.0
A3 2006-08-11 00:00:00.000 24.0

peter,
im trying to list all the record from datalist order by date.. like the datalist record, it contains a few records on 17 oct and 11 august.. i need to list all earlier date record first.. but i couldnt order it by recvdate.. it said couldnt order inline function..

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

Edited by - maya_zakry on 04/16/2007 07:15:57
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/16/2007 :  07:14:42  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
SELECT dl.ItemStorageid, dl.RecvDate, dl.Qty
FROM		@Datalist AS dl
INNER JOIN @Data AS dt ON dt.ItemQty=dl.Qty
WHERE dl.RecvDate = (SELECT min(RecvDate) FROM @Datalist)


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/16/2007 :  20:48:16  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
no, i dun think so.. this will only list all the minimum date.. whereas, according to @data, i want to list 4 record in @datalist which qty=24 (ive done this),this what i need to do next : list first date in first.. so in this case, there are records in @datalist :-

17 nov : 24 (A1)
11 aug : 24 (A4)
17 nov : 24 (A5)
11 aug : 24 (A2)
11 aug : 24 (A3)
11 aug : 24 (A6)

record in @Data
4 : how many record i want to print
24 : qty i must find

so, i need to find 4 records wif 24 qty... problem is i want to list down earlier date first.. so according to @Datalist i will list all 4 records in 11-august first followed by records in 17 november (if still not enough), result :-

11 aug : 24 (A4)
11 aug : 24 (A2)
11 aug : 24 (A3)
11 aug : 24 (A6)

so if my @Data record has 5, 24 means it want to list 5 records with qty=24, then the result will include date 17-nov becoz all records in 11 august has been list to fullfill the condition in @Data (find 5 records with qty=24)...(not all minimum date, harsh), result :-

11 aug : 24 (A4)
11 aug : 24 (A2)
11 aug : 24 (A3)
11 aug : 24 (A6)
17 nov : 24 (A1)

hopefully this is understandable.. besides my poor englishg.. thank u for reading :D

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  01:39:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Do the work
DECLARE	@Items INT,
	@Qty INT

SELECT	@Items = ItemCount,
	@Qty = ItemQty
FROM	@Data

SET ROWCOUNT @Items

SELECT		ItemStorageID,
		RecvDate,
		Qty
FROM		@Datalist
WHERE		Qty = @Qty
ORDER BY	RecvDate,
		ItemStorageID

SET ROWCOUNT 0


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  01:57:54  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
emm..yeayy.. there u are.. it works... but way to long to sip in to my SP.. nvm.. tq peter,

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  02:23:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by maya_zakry

emm..yeayy.. there u are.. it works... but way to long to sip in to my SP.. nvm.. tq peter,
Way too long for a stored procedure?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  03:04:15  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
way too long to sip in here or i dunno how to :-

CREATE PROCEDURE SP_StageAutoPick

(	
	@JobID VARCHAR(12),
	@DocRefID VARCHAR(12),
	@Shipper varchar(20),
	@ItemID varchar(50),
	@CustomLotNo VARCHAR(20),	
	@WantedValue INT
)
AS

-- Stage the source data
CREATE TABLE #Data 
	(
		RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		MaxItems INT,
		CurrentItems INT DEFAULT 0,
		FaceValue INT,
		BestUnder INT DEFAULT 0,
		BestOver INT DEFAULT 1
	)

-- Aggregate the source data
--This execution of Sp calls the filtered data from whateva filtered Sp out there, sum by AVAQty for filtering suitable pickqty
INSERT		#Data
		(
			MaxItems,
			FaceValue
		)
			EXEC  SP_StageData 
			@JobID = @JobID,
			@DocRefID = @DocRefID,
			@Shipper= @Shipper,	
			@ItemID= @ItemID,
			@CustomLotNo = @CustomLotNo,
			@WantedValue=@Wantedvalue

-- This execution of Sp calls the original data from #Data that had been sum up, to get the itemstorageid to populate the data for viewing
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

-- Declare some control variables
DECLARE	@CurrentSum INT,
	@BestUnder INT,
	@BestOver INT,
	@RecID INT

-- If productsum is less than or equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM #Data) <= @WantedValue
	BEGIN
			
		SELECT DISTINCT dl.ItemStorageId, dl.Qty AS BalQty, dl.PickNm,  d.ItemID, d.DocRefID, d.CustomlotNo, d.StorageID, d.CartonID, d.WhsId, d.LocID, d.recvdate, d.ExpiryDate,  r.PickType, d.RET, d.ConsignmentStock, d.NonConsignmentStock, 			
			 c.ClientUDF1, c.ClientUDF2, c.ClientUDF3,  c.ClientUDF4, c.ClientUDF5, c.ClientUDF6,  c.ClientUDF7, c.ClientUDF8, c.ClientUDF9, c.ClientUDF10,
			d.RecvUDF1, d.RecvUDF2, d.RecvUDF3,  d.RecvUDF4, d.RecvUDF5, d.RecvUDF6,  d.RecvUDF7, d.RecvUDF8, d.RecvUDF9, d.RecvUDF10, r.Breakbulk, r.CBreakbulk, d.DocRefID, r.PickUOM
			
		FROM #DataList  AS dl
		INNER JOIN #Data AS dt ON dt.FaceValue=dl.Qty
		INNER JOIN tblItemDetail d ON d.ItemStorageID = dl.ItemStorageID
		LEFT JOIN tblClient c ON c.ClientID=@Shipper
		LEFT JOIN tblItemCrossRef r On r.ClientID=@Shipper AND r.ItemID=d.ItemID
		
		WHERE dt.MaxItems >= (SELECT top 1  COUNT(*) FROM #Datalist AS x WHERE x.Qty=dl.Qty AND x.ItemStorageID <=dl.ItemStorageID order by dl.recvdate ) 			
		RETURN
	END

-- Delete all unworkable FaceValues (more than wantedvalue)
DELETE
FROM	#Data
WHERE	FaceValue > (SELECT MIN(FaceValue) FROM #Data WHERE FaceValue >= @WantedValue)

-- Update MaxItems to a proper value
UPDATE	#Data
SET	MaxItems =	CASE
				WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
				ELSE MaxItems
			END

-- Update BestOver to a proper value
UPDATE	#Data
SET	BestOver = MaxItems

-- Initialize the control mechanism
SELECT	@RecID = MIN(RecID),
	@BestUnder = 0,
	@BestOver = SUM(BestOver * FaceValue)
FROM	#Data

-- Do the loop!
WHILE @RecID IS NOT NULL
	BEGIN
		-- Reset all "bits" not incremented
		UPDATE	#Data
		SET	CurrentItems = 0
		WHERE	RecID < @RecID

		-- Increment the current "bit"
		UPDATE	#Data
		SET	CurrentItems = CurrentItems + 1
		WHERE	RecID = @RecID

		-- Get the current sum
		SELECT	@CurrentSum = SUM(CurrentItems * FaceValue)
		FROM	#Data
		WHERE	CurrentItems > 0

		-- Stop here if the current sum is equal to the sum we want
		IF @CurrentSum = @WantedValue
			BREAK
		ELSE
			-- Update the current BestUnder if previous BestUnder is less
			IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
				BEGIN
					UPDATE	#Data
					SET	BestUnder = CurrentItems

					SET	@BestUnder = @CurrentSum
				END
			ELSE
				-- Update the current BestOver if previous BestOver is more
				IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
					BEGIN
						UPDATE	#Data
						SET	BestOver = CurrentItems

						SET	@BestOver = @CurrentSum
					END

		-- Find the next proper "bit" to increment
		SELECT	@RecID = MIN(RecID)
		FROM	#Data
		WHERE	CurrentItems < MaxItems
	END

IF @RecID IS NULL
	IF @WantedValue - @BestUnder <  @BestOver - @WantedValue
		-- If BestUnder is closer to the sum we want, choose that	
		BEGIN
		--breakbulk=Y
		SELECT DISTINCT  dl.ItemStorageId, dl.Qty AS BalQty, dl.PickNm,  d.ItemID, d.DocRefID, d.CustomlotNo, d.StorageID, d.CartonID, d.WhsId, d.LocID, d.recvdate, d.ExpiryDate,  r.PickType, d.RET, d.ConsignmentStock, d.NonConsignmentStock, 			
			 c.ClientUDF1, c.ClientUDF2, c.ClientUDF3,  c.ClientUDF4, c.ClientUDF5, c.ClientUDF6,  c.ClientUDF7, c.ClientUDF8, c.ClientUDF9, c.ClientUDF10,
			d.RecvUDF1, d.RecvUDF2, d.RecvUDF3,  d.RecvUDF4, d.RecvUDF5, d.RecvUDF6,  d.RecvUDF7, d.RecvUDF8, d.RecvUDF9, d.RecvUDF10, r.Breakbulk, r.CBreakbulk, d.DocRefID, r.PickUOM
			--MAX(d.recvdate) AS MaxRecvdate, MIN(d.recvdate) AS MinRecvdate
		FROM #DataList  AS dl
		LEFT JOIN #Data AS dt ON dt.FaceValue=dl.Qty
		INNER JOIN tblItemDetail d ON d.ItemStorageID = dl.ItemStorageID
		LEFT JOIN tblClient c ON c.ClientID=@Shipper
		LEFT JOIN tblItemCrossRef r On r.ClientID=@Shipper AND r.ItemID=d.ItemID	

		WHERE dt.BestUnder > 0 AND
		  	 dt.BestUnder >=(SELECT top 1 with  ties  COUNT(*) FROM #Datalist AS x WHERE x.Qty=dl.Qty AND x.ItemStorageID <=dl.ItemStorageID order by dl.recvdate )
		
		
		RETURN
		END
		
	ELSE
		-- If BestOver is closer to the sum we want, choose that
		BEGIN
		--Breakbulk=Y
		SELECT DISTINCT dl.ItemStorageId, dl.Qty AS BalQty, dl.PickNm,  d.ItemID, d.DocRefID, d.CustomlotNo, d.StorageID, d.CartonID, d.WhsId, d.LocID, d.recvdate, d.ExpiryDate,  r.PickType, d.RET, d.ConsignmentStock, d.NonConsignmentStock, 			
			 c.ClientUDF1, c.ClientUDF2, c.ClientUDF3,  c.ClientUDF4, c.ClientUDF5, c.ClientUDF6,  c.ClientUDF7, c.ClientUDF8, c.ClientUDF9, c.ClientUDF10,
			d.RecvUDF1, d.RecvUDF2, d.RecvUDF3,  d.RecvUDF4, d.RecvUDF5, d.RecvUDF6,  d.RecvUDF7, d.RecvUDF8, d.RecvUDF9, d.RecvUDF10, r.Breakbulk, r.CBreakbulk, d.DocRefID, r.PickUOM
			
		FROM #DataList  AS dl
		LEFT JOIN #Data AS dt ON dt.FaceValue=dl.Qty
		INNER JOIN tblItemDetail d ON d.ItemStorageID = dl.ItemStorageID
		LEFT JOIN tblClient c ON c.ClientID=@Shipper
		LEFT JOIN tblItemCrossRef r On r.ClientID=@Shipper AND r.ItemID=d.ItemID

		WHERE dt.BestOver > 0 AND
		  	 dt.BestOver >=(SELECT top 1 with  ties  COUNT(*) FROM #Datalist AS x WHERE x.Qty=dl.Qty AND x.ItemStorageID <=dl.ItemStorageID order by dl.recvdate )
		
		RETURN
		END
	
ELSE
	-- We have an exact match	
		BEGIN
		--breakbulk=Y		
		SELECT DISTINCT  dl.ItemStorageId, dl.Qty AS BalQty, dl.PickNm,  d.ItemID, d.DocRefID, d.CustomlotNo, d.StorageID, d.CartonID, d.WhsId, d.LocID, d.recvdate, d.ExpiryDate,  r.PickType, d.RET, d.ConsignmentStock, d.NonConsignmentStock, 			
			 c.ClientUDF1, c.ClientUDF2, c.ClientUDF3,  c.ClientUDF4, c.ClientUDF5, c.ClientUDF6,  c.ClientUDF7, c.ClientUDF8, c.ClientUDF9, c.ClientUDF10,
			d.RecvUDF1, d.RecvUDF2, d.RecvUDF3,  d.RecvUDF4, d.RecvUDF5, d.RecvUDF6,  d.RecvUDF7, d.RecvUDF8, d.RecvUDF9, d.RecvUDF10, r.Breakbulk, r.CBreakbulk, d.DocRefID, r.PickUOM
			--MAX(d.recvdate) AS MaxRecvdate, MIN(d.recvdate) AS MinRecvdate
		FROM #DataList  AS dl
		LEFT JOIN #Data AS dt ON dt.FaceValue=dl.Qty
		INNER JOIN tblItemDetail d ON d.ItemStorageID = dl.ItemStorageID
		LEFT JOIN tblClient c ON c.ClientID=@Shipper
		LEFT JOIN tblItemCrossRef r On r.ClientID=@Shipper AND r.ItemID=d.ItemID

		WHERE dt.CurrentItems > 0 AND
		  	 dt.CurrentItems >= (SELECT top 1 with  ties  COUNT(*) FROM #Datalist AS x WHERE x.Qty=dl.Qty AND x.ItemStorageID <=dl.ItemStorageID order by dl.recvdate )
		RETURN
		END		
		

DROP TABLE #Data
GO

yeah, remember this one worked well before..the one with ur script to find combination of X, but then i have to consider the dates also..

but forget it.. maybe need another approach, i need to ask ur opinion on how do we find X records with sum(col1)=any value..

from my sample @datalist, say i have param @wantedValue=50, how do i find total sum <= 50?

this one surely cant work, : how?
select * from @datalist where sum(Qty) <= @Wantedvalue
order by recvdate

thanks

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  03:07:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, no, no....
Leave that algorithm intact and use the result FROM it with the suggestion I made earlier today.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  03:25:29  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
okay peter, help again,yeah forget abt the algorithm, this is different..

im not sure how to use set rowcount into this..
Scenario : Find from this records(dont care how many rows), where total of Qty=72

-- Prepare sample data
DECLARE	@DataList TABLE (ItemStorageID VARCHAR(2), Qty float, recvDate datetime)

INSERT	@DataList
SELECT	'A1', 24, '2006-11-17 00:00:00.000' UNION ALL
SELECT	'A4', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A5', 24, '2006-11-17 00:00:00.000' UNION ALL
SELECT	'A2', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A3', 24, '2006-08-11 00:00:00.000' UNION ALL
SELECT	'A6', 24, '2006-08-11 00:00:00.000' 

HELP ME FROM HERE
Declare @Total AS INT
SET @Total=72   

-- Do the work

SET ROWCOUNT --what to set here

SELECT		ItemStorageID,
		RecvDate,
		Qty
FROM		@Datalist
WHERE		sum(QTy) <= @Total
ORDER BY	RecvDate,
  		ItemStorageID 

SET ROWCOUNT 0


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

Edited by - maya_zakry on 04/17/2007 03:29:48
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  03:34:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Using my original algorithm, you will get a result of
SumType	Items	FaceValue
-------	-----	---------
      0     3          24
Are you with me?

Write
INSERT #Data
EXEC <Name for my original algorithm SP here>

and hereafter use the code suggested in this topic, in your code AUTOPICK
-- Do the work
DECLARE	@Items INT,
	@Qty INT

SELECT	@Items = ItemCount,
	@Qty = ItemQty
FROM	#Data -- Formerly known as @Data

SET ROWCOUNT @Items

SELECT		ItemStorageID,
		RecvDate,
		Qty
FROM		@Datalist
WHERE		Qty = @Qty
ORDER BY	RecvDate,
		ItemStorageID

SET ROWCOUNT 0


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  04:08:10  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
<-- cry cry cryy

1.okay, i decided not to use that script nmore coz i want my combination must be MORE than my @WantedValue, and must consider dates. Previously, it pick 50 when my @WantedValue=51.. and it lists down not according to date.. ok so i drop the case

2.in this new case, i want to ask how do we select record with total sum(someFloatcolumn) >= someInteger value with Order by somedatecolumn?
u recommended using SET ROWCOUNT, but i dunno how..

eg : @WantedValue=36
id. Qty rdate
1. 18 13-aug-2006
2. 17 14-aug-2006
3. 5 14-aug-2006
4. 18 22-aug-2006

so given @WantedValue=36, i need to find earliest records with total of column Qty >= 36 i expect to get :-
id. Qty rdate
1. 18 13-aug-2006
2. 17 14-aug-2006
3. 5 14-aug-2006

-- Prepare sample data
DECLARE	@table1 TABLE (ID INT, Qty float, recvDate datetime)

INSERT	@table1
SELECT	'1', 18, '2006-08-13 00:00:00.000' UNION ALL
SELECT	'2', 17, '2006-08-14 00:00:00.000' UNION ALL
SELECT	'3', 5,  '2006-08-14 00:00:00.000' UNION ALL
SELECT	'4', 18, '2006-08-22 00:00:00.000' 

select * from @table1






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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  04:29:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
DECLARE	@Table1 TABLE (ID INT, Qty MONEY, RecvDate DATETIME)

INSERT	@Table1
SELECT	'1', 18, '2006-08-13 00:00:00.000' UNION ALL
SELECT	'2', 17, '2006-08-14 00:00:00.000' UNION ALL
SELECT	'3', 5,  '2006-08-14 00:00:00.000' UNION ALL
SELECT	'4', 18, '2006-08-22 00:00:00.000' 

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

DECLARE @WantedValue INT
SET @WantedValue = 36

WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
	INSERT		@Stage (ID, Qty, RecvDate)
	SELECT TOP 1	t1.ID,
			t1.Qty,
			t1.RecvDate
	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
	SELECT	ID,
		Qty,
		RecvDate
	FROM	@Stage
ELSE
	SELECT 'No oversum available'

Peter Larsson
Helsingborg, Sweden

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

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  04:36:24  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
yeayyyyyyy... terima kasih peso means thanks hehehhe
even though ive no idea how it works.. need sometime to digest

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

Edited by - maya_zakry on 04/17/2007 04:40:29
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  04:43:02  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
cry cry cry ... it takes sometimes to run..

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

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 04/17/2007 :  04:47:16  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
wow.. the new edited works double trouble triple faster


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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/17/2007 :  04:50:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is this slow?
-- Prepare sample data
DECLARE	@Table1 TABLE (ID INT, Qty MONEY, RecvDate DATETIME)

INSERT	@Table1
SELECT	'1', 18, '2006-08-13 00:00:00.000' UNION ALL
SELECT	'2', 17, '2006-08-14 00:00:00.000' UNION ALL
SELECT	'3', 5,  '2006-08-14 00:00:00.000' UNION ALL
SELECT	'4', 18, '2006-08-22 00:00:00.000' 

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

SELECT	@WantedValue = 36,
	@Sum = @WantedValue

-- Stage the data
DECLARE	@Stage TABLE (ID INT, Qty MONEY, RecvDate DATETIME)

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

DECLARE	@ID INT,
	@Qty MONEY,
	@RecvDate DATETIME

OPEN curStage

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

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

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

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

CLOSE curStage
DEALLOCATE curStage

-- Show the expected output
IF @Sum <= 0
	SELECT		ID,
			Qty,
			RecvDate
	FROM		@Stage
	ORDER BY	RecvDate,
			Qty DESC
ELSE
	SELECT 'No oversum found'

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/17/2007 05:02:02
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
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.27 seconds. Powered By: Snitz Forums 2000