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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Recurring Order Entry
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nick
Posting Yak Master

155 Posts

Posted - 10/28/2001 :  10:19:00  Show Profile  Visit Nick's Homepage  Reply with Quote
Hello Again-

I'm desigining a DB for a medium sized manufacturing company. Right now they use a combination of badly designed spreadsheets.

One feature that they want the DB to have is the ability to look at order backlogs, and order scheduling. I can provide this ability by adding a due date to each order. The problem is that many of the orders they get come in the form of "100 parts starting on mm/dd/yyyy and 50 parts every week after until the balance of 1000 parts is met."

My initial thought was to have a products table, an orders table, and a n-to-n relation table between them. This should work (although I'm willing to take better suggestions.)

The problem comes with the order entry. I would like to do it via an ASP web page. My thought was to give the users a dropdown box with all of the part numbers. An initial shipment quantity and date text box. A dropdown box that allows for options such as every month, every week, every two weeks, and the total quantity requested.

I guess my real question is how would I write a stored procedure to take those inputs and enter into the orders_products table the correct info? Am I going at this the wrong way? Thanks in advance!

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 10/28/2001 :  15:48:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well, I came up with a stored procedure that should do the trick:

CREATE procedure GenerateOrders
@part varchar(20)=null,
--part to be ordered
@startdate datetime=null,
--first order date
@startqty int=1,
--first order quantity
@totalqty int=1,
--total quantity
@interval char(1)='w',
--interval type: w=week, d=day, m=month
@intervalcount int=1,
--how many intervals between orders (1=every day, 2=every 2 days)
@intervalqty int=1
--quantity per interval
AS
DECLARE @diffqty int, @numintervals int, @leftoverqty int

SELECT @diffqty = @totalqty - @startqty,
@numintervals = @diffqty / @intervalqty,
@leftoverqty = @diffqty - (@numintervals * @intervalqty),
@numintervals = @numintervals + CASE WHEN @startqty + (@numintervals * @intervalqty) < @totalqty THEN 1 ELSE 0 END

--INSERT INTO Orders (Part, OrderDate, Qty)
--uncomment this line to perform the INSERT
SELECT @part Part, @startdate TheDate, @startqty Qty
UNION ALL
SELECT @part Part,
CASE @interval
WHEN 'm' THEN DateAdd(mm,ID * @intervalcount, @startdate)
WHEN 'd' THEN DateAdd(dd,ID * @intervalcount, @startdate)
ELSE DateAdd(ww,ID * @intervalcount, @startdate) END TheDate,
CASE WHEN ID<@numintervals OR @leftoverqty=0 THEN @intervalqty ELSE @leftoverqty END Qty
FROM Sequence WHERE ID<=@numintervals


What does this mess do?

It accepts parameters for part ordered, startdate, etc., pretty much everything you need (each parameter is commented above). It does a little math to determine how many intervals are required to fill the order, and determine the leftover quantity for the final order, if there's a remainder from the total quantity.

The key part of this procedure is a Sequence table, which is a technique used in a couple of SQL Team articles:

http://www.sqlteam.com/item.asp?ItemID=5857
http://www.sqlteam.com/item.asp?ItemID=2652

All you need is a table with a single column (ID) of sequential integers, from 1 to whatever upper value (should be at least 8000, higher is OK). This is what will generate the sequence of order dates you need.

The INSERT statement is commented out in the procedure, so while it will return the appropriate orders, it won't put them into your orders table. I just used a basic Order table structure (Part, OrderDate, Qty) so you'll need to modify it to fit your structure.

The SELECT...UNION ALL...SELECT is there just so the entire order set can be INSERTed as one operation. You could perform two INSERT statements using each SELECT, but it will be harder to handle a rollback if something should fail.

The 1st SELECT puts in the initial order, with part, start date and start qty. The 2nd SELECT uses the sequence table to increment each date interval accordingly. The CASE statement determines the qty. for each order. It will use the interval qty. for each order; the last order will use the leftover qty. if there is one (greater than zero).

This was a total accident, but it will even handle an interval qty. greater than the total quantity! You could potentially use this for all of your order submissions regardless of quantity or intervals; it may need a little tweaking to handle missing dates and such.

One thing to look out for are the numeric values: if they are negative, zero, or Null, you may end up with an infinite loop somewhere. I didn't put any error checking in here but it would be pretty easy to add some code to ensure that the appropriate numbers are correct for the order.

HTH

Go to Top of Page

Nick
Posting Yak Master

155 Posts

Posted - 10/28/2001 :  17:53:32  Show Profile  Visit Nick's Homepage  Reply with Quote
robvolk-

I have to honestly tell you that you are the fucking man!

Thanks tons!

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/28/2001 :  18:23:53  Show Profile  Reply with Quote
quote:

robvolk-

I have to honestly tell you that you are the fucking man!




Since when did you start featuring in pornos Rob?

DavidM
Look, over the horizon..."Tutorial D"
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/28/2001 :  18:37:53  Show Profile  Visit Merkin's Homepage  Reply with Quote
eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
eeeeeeeeeewwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
wwwwwwwwwwwwwwwww




Damian

Edited by - merkin on 10/28/2001 18:38:41
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 10/28/2001 :  19:55:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
Hey, some of my older videos go for some good money on eBay! Mind you, only seriously disturbed individuals buy them....besides/including me.

Actually Nick, according to my Yahoo! name, you're not exactly correct there. Merkin will tell you what my Yahoo! name is.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/28/2001 :  20:00:53  Show Profile  Visit Merkin's Homepage  Reply with Quote
No I WILL NOT!



Damian
Go to Top of Page

fisherman_jake
Slave to the Almighty Yak

Australia
159 Posts

Posted - 10/28/2001 :  20:17:57  Show Profile  Send fisherman_jake a Yahoo! Message  Reply with Quote
let me have a guess.. something to do with latex, rubber and vinyl... Am I close??

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman
Go to Top of Page

Nick
Posting Yak Master

155 Posts

Posted - 10/29/2001 :  09:04:01  Show Profile  Visit Nick's Homepage  Reply with Quote
What have I started?

quote:

No I WILL NOT!



Damian



Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 10/29/2001 :  09:39:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
See, I think someone may be spending a little too much time in King's Cross, but since I don't live in Sydney, it can't be me, so I'm left wondering who

If King's Cross doesn't ring bells, think 42nd St. circa early 1980's, but much cleaner (meaning lack-of-dirt-in-the-street cleaner).

Go to Top of Page
  Previous Topic Topic Next Topic  
 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.08 seconds. Powered By: Snitz Forums 2000