I need some assistance...not sure how to do this

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

Transact-SQL (2000)

I need some assistance...not sure how to do this

Author

Topic

delcom5
Starting Member

11 Posts

Posted - 2007-09-20 : 20:48:14

Hi,
I am tasked with a query with which I am not sure how to start. I need to
cut ordes in an order table to match the quantity we have in inventory so for
each product in the inventory table, i have to get the quantity on hand, then
go through the orders table, sum up all the orders for that product, if the
quantity is more than the inventory amount then cut orders at random so that
the amount match what's on the inventory.

sample inventory

prodid,invqty
1 500
2 300

sample order

custid, prodid,ordqty
1 1 50
2 1 300
3 1 200
1 2 200
2 2 100

In the above case I need to cust the customers at random for product 1 so
that the order matches 500. I know orders are not supposed to exceed what is
on inventory and some may argue I should use trigger. The nature of our
business is quite different and we process based on orders form 3 parties.
The don't always have an update of what we have on hand.

Please advice on this query.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 02:51:33

What is your business logic on this ? How do you want to cut ?

What is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-21 : 09:30:07

He listen, just do top of stack, so if they order spinach, and you're out, send condoms

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-21 : 10:34:03

Right, 'cause you can catch diseases from spinach, while condoms PREVENT disease. So your customers will be grateful.

e4 d5 xd5 Nf6

delcom5
Starting Member

11 Posts

Posted - 2007-09-21 : 13:05:43

That's not funny guys. The business logic is cut stores at random for that product so that the overall total for that product matches what's on inventory. At least 1 qty order should be left to a customer.

Thanks

delcom5
Starting Member

11 Posts

Posted - 2007-09-21 : 13:07:48

X002548, If I knew the query, ddl I wouldn't ve asked. I am not sure where to start that's why I am asking.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 13:22:47

You can start here, but this will only tell you when the amount ordered exceeds inventory. How to cut back on the orders is a whole different story (cut a little from each customer, all from one, etc.)

DECLARE @inv TABLE (prodid int,invqty int)
DECLARE @orders TABLE (custid int, prodid int ,ordqty int)

INSERT INTO @inv
SELECT 1,500
UNION ALL
SELECT 2,300

INSERT INTO @orders
SELECT 1 ,1 ,50 UNION ALL
SELECT 2, 1 ,300 UNION ALL
SELECT 3 ,1 ,200 UNION ALL
SELECT 1, 2 ,200 UNION ALL
SELECT 2, 2 ,100

SELECT i.prodid,i.invqty,o.ordqty
,'LeftInStock' = (o.ordqty - invqty)
FROM
@inv i
INNER JOIN
(select prodid, 'OrdQty' = sum(ordqty)
from @orders
group by prodid
)o

ON
i.prodid = o.prodid

Jim

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 13:25:33

Why wouldn't you provide stock in strict date/time sequence of when orders were place?

Or Back Order the remainder ...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 13:28:17

oops
,'LeftInStock' = (i.invqty-o.ordqty )

mr.modus
Starting Member

27 Posts

Posted - 2007-09-21 : 13:42:35

I would really think you should add a datetime field for when an order was placed so it's a matter of simply cueing the older order or as Kristen suggested back ordering. I would think if it's just random you are going to have some angry customers.

oh... and by the way X002548's and blindman's comments ARE funny. They just aren't helpful.

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 13:45:00

Indeed. But I think that comes for incredulousness that you would cut orders on a random basis.

I can't think of an industry where that would be a good Business Rule, but I would love to hear what the scenario is where this works well, if you can share pls?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-21 : 13:55:43

I doubt random was even the right choice of words. You can't really program a computer to do things at random.

mr.modus
Starting Member

27 Posts

Posted - 2007-09-21 : 13:57:25

No need. If it's a Micro$oft product it will do things at random already.

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 14:03:49

"You can't really program a computer to do things at random"

Well, apart from an intellectual debate over "random" and "pseudo-random" there is always:

ORDER BY NewID()

Kristen

delcom5
Starting Member

11 Posts

Posted - 2007-09-21 : 14:04:22

The nature of our business is a bit different. Orders are placed for a customer, it remains the same everytime we ship the product until we are notified to change the quantity. As the inventory changes we have to keep updating our suppliers. During that time, executives keep adjusting store orders based on where the products are selling better and at the same time adding new customer.

When we process a delivery, we have to try to fulfill as much as we can with what we have on hand at that time and then notify our suppliers if we have quantity increase or decrease, which we should have for the next delivery.

At this point during the delivery process I have to cut orders to match what we have on hand. At this point I am just cutting random until the overall quantity matches what is inventory the I stop and proceed to the next product. After all the process products matches what we have on inventory I begin a delivery process.

I've never done this before and I have no clue wher to at least start.

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-21 : 14:11:43

Try this:

DECLARE @Product TABLE (ProductID INT, InvQty INT)

INSERT @Product
SELECT 1, 500
UNION ALL SELECT 2, 300

DECLARE @Order TABLE (CustomerID INT, ProductID INT, OrderQty INT)

INSERT @Order
SELECT 1, 1, 50
UNION ALL SELECT 2, 1, 300
UNION ALL SELECT 3, 1, 200
UNION ALL SELECT 1, 2, 200
UNION ALL SELECT 2, 2, 100

DECLARE @TempOrder TABLE (ID INT IDENTITY(1,1), CustomerID INT, ProductID INT, OrderQty INT, OrderQtyAvail INT)

INSERT @TempOrder
SELECT
	CustomerID,
	ProductID,
	OrderQty,
	0 AS OrderQtyAvail
FROM
	@Order	
ORDER BY
	ProductID,
	OrderQty

SELECT *
FROM @TempOrder

DECLARE @ProductID INT
DECLARE @AvailQty INT
DECLARE @TempID INT

SET @TempID = 0

SELECT @ProductID = MIN(ProductID) FROM @TempOrder

WHILE @ProductID IS NOT NULL
BEGIN
	SELECT @AvailQty = InvQty FROM @Product WHERE ProductID = @ProductID
	
	IF @AvailQty IS NOT NULL AND @AvailQty > 0
	BEGIN

		WHILE @AvailQty > 0 AND EXISTS(SELECT * FROM @TempOrder WHERE ProductID = @ProductID AND OrderQty <> OrderQtyAvail)
		BEGIN

			SELECT @TempID = MIN(ID) FROM @TempOrder WHERE ProductID = @ProductID AND OrderQty > OrderQtyAvail

			WHILE @TempID IS NOT NULL
			BEGIN
				UPDATE @TempOrder
				SET OrderQtyAvail = OrderQtyAvail + 1
				WHERE ID = @TempID

				SET @AvailQty = @AvailQty - 1

				SELECT @TempID = MIN(ID) FROM @TempOrder WHERE ProductID = @ProductID AND ID > @TempID AND OrderQty > OrderQtyAvail
			END -- WHILE
		END -- WHILE
	END -- IF

	SELECT @ProductID = MIN(ProductID) FROM @TempOrder WHERE ProductID > @ProductID

END -- WHILE

SELECT *
FROM @TempOrder

There is probably a better way to do this, but this works for the sample data.

delcom5
Starting Member

11 Posts

Posted - 2007-09-21 : 15:14:46

Hi All,
Thanks for the interesting information so far. At least I have things to work with. I will keep you posted as I try some samples. One thing I was discussing was to order the customers by DESC then cut those with high quantuty first, and if the overall order quantity for a product still does not match the proceed to the other customers. At this point I won't cust anyone to 0 unless I really cant help it.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:46:18

Well, if you have 5 customer orders and 100 in stock you can satisfy any order up to 20.

So first off fulfill all orders of 20 or less.

Now repeat.

Lets say there was one order for 10, and another for 15. They are "done" and you have 75 left and 3 order left to fulfil. Any of those orders, up to 25, can be fulfilled.

And so on.

When you can no longer fulfill ANY order (e.g. at the first iteration all orders were for MORE than 20) then each customer gets the same amount.

You could use a similar method with qty proportionate to the size of the orders, but customers will get wise to that and just order trillions of them!

I presume we are talking something like the Cabbage Patch Doll craze at Christmas in the half half of the '80s IIRC?

Kristen

delcom5
Starting Member

11 Posts

Posted - 2007-09-26 : 13:41:15

Hi,
I've given all the examples a try. The only one came 97% accurate was the one from lamprey

DECLARE @Product TABLE (ProductID INT, InvQty INT)

INSERT @Product
SELECT 1, 500
UNION ALL SELECT 2, 300

DECLARE @Order TABLE (CustomerID INT, ProductID INT, OrderQty INT)

INSERT @Order
SELECT 1, 1, 50
UNION ALL SELECT 2, 1, 300
UNION ALL SELECT 3, 1, 200
UNION ALL SELECT 1, 2, 200
UNION ALL SELECT 2, 2, 100

DECLARE @TempOrder TABLE (ID INT IDENTITY(1,1), CustomerID INT, ProductID INT, OrderQty INT, OrderQtyAvail INT)

INSERT @TempOrder
SELECT
CustomerID,
ProductID,
OrderQty,
0 AS OrderQtyAvail
FROM
@Order
ORDER BY
ProductID,
OrderQty

SELECT *
FROM @TempOrder

DECLARE @ProductID INT
DECLARE @AvailQty INT
DECLARE @TempID INT

SET @TempID = 0

SELECT @ProductID = MIN(ProductID) FROM @TempOrder

WHILE @ProductID IS NOT NULL
BEGIN
SELECT @AvailQty = InvQty FROM @Product WHERE ProductID = @ProductID

IF @AvailQty IS NOT NULL AND @AvailQty > 0
BEGIN

WHILE @AvailQty > 0 AND EXISTS(SELECT * FROM @TempOrder WHERE ProductID = @ProductID AND OrderQty <> OrderQtyAvail)
BEGIN

SELECT @TempID = MIN(ID) FROM @TempOrder WHERE ProductID = @ProductID AND OrderQty > OrderQtyAvail

WHILE @TempID IS NOT NULL
BEGIN
UPDATE @TempOrder
SET OrderQtyAvail = OrderQtyAvail + 1
WHERE ID = @TempID

SET @AvailQty = @AvailQty - 1

SELECT @TempID = MIN(ID) FROM @TempOrder WHERE ProductID = @ProductID AND ID > @TempID AND OrderQty > OrderQtyAvail
END -- WHILE
END -- WHILE
END -- IF

SELECT @ProductID = MIN(ProductID) FROM @TempOrder WHERE ProductID > @ProductID

END -- WHILE

SELECT *
FROM @TempOrder

This works fine with small datasets like 10 products 15 customers and 30% order difference. It doesn't work accurately with the products and customer list I tried, 50 products and 200 customers with a few customer order difference with 1 to 7 units. It does cut the orders but it still does not match. Do you thing cuting by the percent deifference will work?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:49:29

Post the sample data you are talking about and your expected output.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:51:37

Or just do precentage?
If you have 500 in stock and 700 in orders, give every customer 5.0/7.0 (71.43) percent of what they have ordered?

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

Next Page