Sum of selected values must be within a range

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum of selected values must be within a range

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 09:57:17

Hi,

I have a table for purchasing goods randomly.
I have to add the items to the carts so that the total package value
is in a range.

Name         Price
CPU            100
Monitor         89
Shoe            24
Book            12
Cup              1
Milk          3.51
Meat             6
Desk            34
...

I want the sum,
for instance
120<sum<=150

So can you advise me the query?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 10:22:53

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80857

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 10:25:06

Can you for example have 4 shoes (4x24=96) and a 3 books (3x12=36) equals 132, which is between 120 and 150.

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 10:30:38

It is possible.
Your algorithm is excellent.
But it will take a long time to understand it.
Can you simplify it for my specific case?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 11:26:08

[code]-- Initialize the search parameter
DECLARE @LowSum INT,
@HighSum INT

SELECT @LowSum = 120,
@HighSum = 150

-- Stage the source data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
[Description] VARCHAR(100),
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)

-- Aggregate the source data
INSERT @Data
(
[Description],
FaceValue,
MaxItems
)
SELECT 'CPU', 100, 20 UNION ALL
SELECT 'Monitor', 89, 15 UNION ALL
SELECT 'Shoe', 24, 9 UNION ALL
SELECT 'Book', 12, 45 UNION ALL
SELECT 'Cup', 1, 332 UNION ALL
SELECT 'Milk', 3.51, 617 UNION ALL
SELECT 'Meat', 6, 544 UNION ALL
SELECT 'Desk', 34, 1

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

-- If exact single wanted sum, select that item!
IF EXISTS (SELECT * FROM @Data WHERE FaceValue BETWEEN @LowSum AND @HighSum)
BEGIN
SELECT TOP 1
0 AS SumType,
1 AS Items,
FaceValue,
[Description]
FROM @Data
WHERE FaceValue BETWEEN @LowSum AND @HighSum

RETURN
END

-- If productsum is less to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) < @LowSum
BEGIN
SELECT -1 AS SumType,
MaxItems AS Items,
FaceValue,
[Description]
FROM @Data

RETURN
END

-- If productsum is equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) BETWEEN @LowSum AND @HighSum
BEGIN
SELECT 0 AS SumType,
MaxItems AS Items,
FaceValue,
[Description]
FROM @Data

RETURN
END

-- Delete all unworkable FaceValues, keep one greater FaceValue because of oversum.
DELETE
FROM @Data
WHERE FaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @HighSum)

-- Update MaxItems to a proper value
UPDATE @Data
SET MaxItems = CASE
WHEN 1 + (@HighSum - 1) / FaceValue < MaxItems THEN 1 + (@HighSum - 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 BETWEEN @LowSum AND @HighSum
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @HighSum
BEGIN
UPDATE @Data
SET BestUnder = CurrentItems

SET @BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @LowSum 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

-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @HighSum - @BestUnder < @BestOver - @LowSum
-- If BestUnder is closer to the sum we want, choose that
SELECT -1 AS SumType,
BestUnder AS Items,
FaceValue,
[Description]
FROM @Data
WHERE BestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECT 1 AS SumType,
BestOver AS Items,
FaceValue,
[Description]
FROM @Data
WHERE BestOver > 0
ELSE
-- We have an exact match
SELECT 0 AS SumType,
CurrentItems AS Items,
FaceValue,
[Description]
FROM @Data
WHERE CurrentItems > 0[/code]

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 13:31:18

Cool.

What is meant by SumType?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 13:35:11

-1 means UnderSum. It means no exact match is found and the closest sum is under the wanted one.
+1 means OverSum. It means no exact match is found and the closest sum is over the wanted one.
0 means ExactMatch. It means an exact match is found.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:36:40

quote:
Originally posted by zhshqzyc

Cool.

What is meant by SumType?

its just an alias name used for one of Column values returned.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 14:08:30

Can I skip the temp table @Data? The temp table is disallowed in my project.
Because I already have the table with the field MaxItems in table PLU.
In this case do I delete all unworkable FaceValues?
What kinds of sql it should be?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 14:11:09

There is a lot of action going on in the @Data table.
You have to add all other columns from @Data table to your PLU table. But that will negate multi-user usage.

Leave as is, or write your own code.

E 12°55'05.25"
N 56°04'39.16"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-12 : 14:25:53

quote:
Originally posted by zhshqzyc

Can I skip the temp table @Data? The temp table is disallowed in my project.

Then quit the project and get a job with real developers and project engineers who don't create stupid restrictions.

Boycott Beijing Olympics 2008

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 14:28:10

quote:
Originally posted by blindman

quote:
Originally posted by zhshqzyc

Can I skip the temp table @Data? The temp table is disallowed in my project.
Then quit the project and get a job with real developers and project engineers who don't create stupid restrictions.

Boycott Beijing Olympics 2008

May be they have 2-3GB space for TempDB in their server.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 17:15:06

Well. I have a resue flag "Y" or "N" in the table.
Suppose I already add it to @Data.
When "Y", the item can be reused forever. Otherwise it only can be used once.
So could you please ....?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:17:36

What is so hard about that?
In the INSERT @Data statement, use CASE when inserting MaxItems like this

CASE WHEN ReUseFlag = 'Y' THEN @LowSum ELSE 1 END

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 17:21:51

But @HighSum is the upper bound sum of the price instead of the number of the items.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:24:23

It is corrected later with the

UPDATE	@Data
SET	MaxItems

part.

You only have to initalize it to a value greater than @LowSum / FaceValue really.
If you have items (shoelaces) costing 1 dollar, and @LowSum is 120 dollars, you need 120 shoelaces at most to fulfill the request, right?

If you have items (caps) costing 4 dollars, and @LowSum is 120 dollars, you need 30 caps at most to fulfill the request, right?

So even if you enter @LowSum as items, the UPDATE later will recalculate the MaxItems number.
You only need to be sure it initially is a number that could make the algorithm return only products of same sort.

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 17:27:00

HeHe,

You are so smart. -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:30:03

There is a caveat though.
If you have a product costing less than a dollar, say a toothpick for 1 cent, you will need 100 * @LowCost as initial value.

Try this

CASE WHEN ReUseFlag = 'Y' THEN CEILING(1.0 * @LowSum / FaceValue) ELSE 1 END

or

CASE WHEN ReUseFlag = 'Y' THEN 2147483647 ELSE 1 END

E 12°55'05.25"
N 56°04'39.16"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-12 : 18:00:09

So you mean I can skip this part if MaxItems are initalized as 2147483647 or 1.

quote:

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 00:51:00

No, you can't.
I think it's time for you to study the algorithm.

E 12°55'05.25"
N 56°04'39.16"

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-08-13 : 05:24:34

Can anyone explain the big query written by peso.I dint understand from the starting of IF construct

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources