-- 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)

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,

---

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

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

SumType	Items	FaceValue
-------	-----	---------
      0     3          24

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

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

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

-- 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'