Difficult inventory calc in SQL!

# Thread: Difficult inventory calc in SQL!

1. Junior Member
Join Date
Dec 1969
Posts
28

## Difficult inventory calc in SQL!

I need help to determine how difficult it would be to write a SQL Stored Procedure to calculate inventory for me. If it were a simple formula of Beginning Inventory + Production - Shipments there would be no problem, but it isn&#039;t that simple for this application. Here&#039;s the situation. My database currently holds data for historical and planned Production and Shipments for 100&#039;s of products by week and month. Every product may have 1 or more PROMOTIONS that are related to it, and which I need to estimate weekly/monthly inventory for. (i.e. Product A has Promotion 1,2 & 3 assigned to it. I need to calculate estimated inventory for each promotion) Each PROMOTION has a number called a PACKOUT assigned to it, which is a target production number. Also, the inventory calculation is going to change depending on if the week/month is prior to or within the range of the PROMOTION dates. The calculations are as follows: Inventory of Promotion 1 for Product A, beginning 6 weeks prior to and upto the start date of the PROMOTION is the sum total of Production disregarding shipments. After the start date of the PROMOTION, the inventory calculation become a function of Beginning Inventory + Production - Shipments. These calculations occur until a certain Total Production # called a Packout is reached at which time the inventory produced and shipped is assigned to another PROMOTION. What I need to know is how difficult would it be to write a function like this in SQL? And if not too difficult, I would appreciate some pointers as to how best to go about doing this. Thanks in advance!

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Difficult inventory calc in SQL!

What I need to know is how difficult would it be to write a function like this in SQL<BR><BR><BR>On a scale from one to ten I guess a 5<BR><BR>

3. Member
Join Date
Dec 1969
Posts
87

## RE: Difficult inventory calc in SQL!

Not really that difficult, but I would recommend doing yourself a favour and writing some functions to do some of the hard work handling packouts etc as components, to simplify the overall procedure (at least make it more readable).<BR><BR>As always the testing will take about 5 times as long as the code will take to design, document, write and unit test :]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•