January 9, 2006 at 7:18 am
Hi,
I trying to write a script that will update an available field that will either subtract or add depending on the demand vs supply fields Here is a representation of what I'm trying to accomplish:
Please note the Green is the initial available units based on item number. The red cells represent demand which is to be subtracted from available. The yellow cells represent the supply ordered which is to be added to the available field. The last row in blue represents the final calculation and if the available is a negative number for the last record for that itemno it will be an exception. Also the calculation starts with the most current delivery date.
Any assistance would be great in trying to figure this out.
Thanks,
| itemno | available | demand(reserved) | supply(ordered) | deliverydate |
| 1 | 81 | 9/28/2005 | ||
| 1 | 52 | 29 | 9/28/2005 | |
| 1 | 26 | 26 | 10/31/2005 | |
| 1 | 26 | 0 | 12/28/2005 | |
| 1 | 26 | 0 | 1/2/2006 | |
| 1 | 66 | 40 | 1/27/2006 | |
| 1 | 106 | 40 | 1/27/2006 | |
| 1 | 66 | 40 | 1/27/2006 | |
| 1 | 26 | 40 | 1/27/2006 | |
| 1 | 55 | 29 | 2/28/2006 | |
| 1 | 80 | 25 | 2/28/2006 | |
| 1 | 40 | 40 | 2/28/2006 | |
| 1 | 0 | 40 | 2/28/2006 | |
| 1 | 40 | 40 | 3/31/2006 | |
| 1 | 80 | 40 | 3/31/2006 | |
| 1 | 40 | 40 | 3/31/2006 | |
| 1 | 0 | 40 | 3/31/2006 | |
| 1 | 52 | 52 | 4/28/2006 | |
| 1 | 92 | 40 | 4/28/2006 | |
| 1 | 40 | 52 | 4/28/2006 | |
| 1 | 0 | 40 | 4/28/2006 | |
| 1 | 56 | 56 | 5/31/2006 | |
| 1 | 0 | 56 | 5/31/2006 | |
| 1 | 40 | 40 | 6/30/2006 | |
| 1 | 95 | 55 | 6/30/2006 | |
| 1 | 40 | 55 | 6/30/2006 | |
| 1 | 0 | 40 | 6/30/2006 | |
| 1 | 41 | 41 | 7/31/2006 | |
| 1 | 0 | 41 | 7/31/2006 |
January 10, 2006 at 6:10 am
Hi Joe,
Ok, we're trying to do the same things at the moment so I understand where you're coming from.
What I can't reconcile is why you have shoved everything into a single column, why not have an ordered total, a supply total and a demand total in seperate fields? Alternatively you could just add the whole column, if you populate the values as +/-.
I'm assuming this table is either a view on other tables (in which case I think you should revisit how you're organising the data) or that there is a selectable expression which tells you what "colour" a column should be.
Personally, quick and dirty, whenever I'm trying to sort a messy datasource out I always run a stored procedure and insert my data into temporary tables and then manipulate it from there :
select sum(demand) into #newtabledemand
from itemstable WHERE demand = "RED"
group by itemnumber
select sum(stock) into #newtablestock
from itemstable WHERE demand = "GREEN"
group by itemnumber .
select sum(ordered) into #newtableorders
from itemstable WHERE demand = "YELLOW"
group by itemnumber
Select * INTO #totalstable
from #newtabledemand nd
innerjoin #newtablestock ns on nd.itemnumber = ns.itemnumber
innerjoin #newtableorders no on ns.itemnumber = no.itemnumber
Then do your maths using INSERT/UPDATE on #totalstable, then SELECT * FROM #totalstable as your return value for the SP.
HTH
Rich
January 10, 2006 at 7:56 am
Hi This it take two I lost my initial repsonse. The reason why this structure is set this way is because of the MRP methodology in place. Procurement needs to identify the total available for a given delivery date. For example, if on 1/1/2006 I have an initial total available of 100 units for a given item number. On 1/2/2006 I had a demand of 40 units for that item number, I then have to update the total available column for 1/2/2006 item number 1 to total available = total available - demand which would be 60. On 1/3/2006 I ordered (supply) 20 units, my total available update would be total available = total available + supply for 80 units on 1/3/2006.
| Item No. | Total Available | Demand | Supply | Delivery Date |
| 1 | 100 | 0 | 0 | 1/1/2006 |
| 1 | 60 | 40 | 0 | 1/2/2006 |
| 1 | 80 | 0 | 20 | 1/3/2006 |
This is what I have so far. Need assistance in figuring out how check for new item number (any help would be grateful). This code was derived from http://www.sqlteam.com/item.asp?ItemID=3856
The color scheme was just to distinguish between supply and demand calculations.
Here is my code so far:
CREATE TABLE #Available(itemno varchar(32),AvailableCount smallint, rDemand float, rSupply float, RunningTotal float)
DECLARE @Itemno varchar(32),
@AvailableCount smallint,
@rDemand float,
@rSupply float,
@RunningTotal float
SET @RunningTotal = 0
DECLARE rt_cursor CURSOR
FOR
SELECT itemno,available,[demand(reserved)],[supply(ordered)]
FROM procurement_demandvssupply
ORDER BY available DESC,deliverydate,itemno DESC
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @Itemno,@AvailableCount,@rDemand,@rSupply
WHILE @@FETCH_STATUS = 0
BEGIN
--If @AvailableCount IS NULL
If @AvailableCount > 0
SET @RunningTotal = @AvailableCount
If @rDemand > 0
SET @RunningTotal = @RunningTotal - @rDemand
INSERT #Available VALUES (@itemno,@AvailableCount,@rDemand,@rSupply,@RunningTotal)
If @rSupply > 0
SET @RunningTotal = @RunningTotal - @rDemand
INSERT #Available VALUES (@itemno,@AvailableCount,@rDemand,@rSupply,@RunningTotal)
FETCH NEXT FROM rt_cursor INTO @Itemno,@AvailableCount,@rDemand,@rSupply
END
CLOSE rt_cursor
DEALLOCATE rt_cursor
SELECT * FROM #Available
DROP TABLE #Available
Thanks,
Joe
January 10, 2006 at 12:17 pm
Need assistance why my if conditions are failing:
DECLARE @Itemno varchar(32),
@currentAvailable smallint,
@AvailableCount smallint,
@currentDemand float,
@previousDemand float,
@currentSupply float,
@previousSupply float,
@deliverydate smalldatetime,
@RunningTotal float
SET @RunningTotal = 0
SELECT @currentAvailable = 0,@previousDemand = 0
DECLARE rt_cursor CURSOR
FOR
SELECT itemno,available,[demand(reserved)],[supply(ordered)],CONVERT(smalldatetime,deliverydate,101) AS deliverydate
FROM procurement_demandvssupply
ORDER BY available DESC,deliverydate,itemno DESC
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @Itemno,@AvailableCount,@currentDemand,@currentSupply,@deliverydate
WHILE @@FETCH_STATUS = 0
BEGIN
If @currentAvailable != @AvailableCount
--Print @currentAvailable
BEGIN
/* First I check to see if current demand value gt 0 if true next step */
If @currentDemand > 0
/* I check against previous value only insert into temp table if different from previous value (this will change only for testing purposes right now) */
If @currentDemand != @previousDemand
Print @currentDemand
SET @RunningTotal = @currentAvailable - @currentDemand
BEGIN
/* Here's my issue. I thought that I only load to the temp table if the conditions are true. However what is happening is that all records are being inserted from the Cursor. What am I doing wrong. I would normally do all this in .NET however I'm building a datasource for a report and didn't think an exe is required since I can do this directly in SQL Server. Please help!! */
INSERT #Available VALUES (@itemno,@currentDemand,@currentSupply,@RunningTotal,@deliverydate)
END
SELECT @currentAvailable = @RunningTotal
SELECT @previousDemand = @currentDemand
END
If @AvailableCount > 0
SELECT @currentAvailable = @AvailableCount
FETCH NEXT FROM rt_cursor INTO @Itemno,@AvailableCount,@currentDemand,@currentSupply,@deliverydate
END
CLOSE rt_cursor
DEALLOCATE rt_cursor
Thanks,
Joe
January 10, 2006 at 12:21 pm
I'm seeing "IF" statements that are missing a "BEGIN", so that the conditional applies to the next statement only, and not the entire indented block. I think thats' where your bug is.
January 10, 2006 at 12:28 pm
You're pretty short on details about exactly what you want to accompllish, but this query might be a useful starting point. This would calculate the Available amount for all items by totaling the demand and supply since the last available record.
SELECT
ItemNo
,InitialInventory
,TotalDemand
,TotalSupply
,Available
FROM (
-- Sum all demand and supply numbers since the last inventory point
-- Default initial inventory to 0 available on 1/1/1900 for new items
SELECT p1.ItemNo
,ISNULL(InitialInventory, 0) AS InitialInventory
,SUM(Demand) as TotalDemand
,SUM(Supply) as TotalSupply
,ISNULL(InitialInventory, 0) + SUM(Supply) - SUM(Demand) as Available
FROM procurement_demandvssupply p1
LEFT JOIN (
-- Get the most recent inventory amount for each item
SELECT p2.ItemNo, p2.Available as InitialInventory, p2.LastAvailDate
FROM procurement_demandvssupply p2
INNER JOIN (
-- Get the date of the most recent inventory point for each item
SELECT ItemNo, MAX(DeliveryDate) as LastAvailDate
FROM procurement_demandvssupply
GROUP BY ItemNo
) p3 ON p2.ItemNo = p3.ItemNo and p2.LastAvailDate = p3.LastAvailDate
) inv ON p1.ItemNo = inv.ItemNo
WHERE p1.DeliveryDate >= ISNULL(inv.LastAvailDate, '1900-01-01')
GROUP BY p1.ItemNo, inv.InitialInventory
) x
If your goal was to generate purchase records for every item that has dropped below 0 plus every new item, you could wrap the above query with this:
SELECT ItemNo, 50 as Available, 0 as Reserved, 50 - Available as Supply, cast(floor(cast(getdate() as
float)) as datetime) as DeliveryDate
FROM (
...
) z
WHERE Available < 0
It would be even more useful if you had another table with reorder quantities and delivery times for each item.
January 10, 2006 at 1:57 pm
Hi,
This is what I'm trying to accomplish.
The reason why this structure is set this way is because of the MRP methodology in place. Procurement needs to identify the total available for a given delivery date. For example, if on 1/1/2006 I have an initial total available of 100 units for a given item number. On 1/2/2006 I had a demand of 40 units for that item number, I then have to update the total available column for 1/2/2006 item number 1 to total available = total available - demand which would be 60. On 1/3/2006 I ordered (supply) 20 units, my total available update would be total available = total available + supply for 80 units on 1/3/2006.
| Item No. | Total Available | Demand | Supply | Delivery Date |
| 1 | 100 | 0 | 0 | 1/1/2006 |
| 1 | 60 | 40 | 0 | 1/2/2006 |
| 1 | 80 | 0 | 20 | 1/3/2006 |
Thanks,
Joe
January 11, 2006 at 2:27 am
Just a thought Joe - the total available is presumably derived from your stock figures, I'm wondering whether you should just drag this out "live" as and when you need it.
Perhaps the answer lies in looking at the figures today for available, then extrapolate the ordered and demand up to "date of interest" (which you can do simply with SUM queries) and then work out whether available is +/-, in the case of - you then need to order x.
I can't help thinking the reason you're getting bogged down in the maths is because you're trying to hit a moving target, and getting it to stand still would be a better option.
Rich
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply