Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: How to do this without using a cursor?

    I am in the last stages of designing a forecasting "engine" for my company,
    and I'm stuck on something that seems simple in comparison to everything
    else I've done so far.

    I have product ABC, and it's total sales forecast is 15 units.
    I split the forecast into 2 different locations, based on an established percentage. In this case, I'll say 67% in location 'OH', and 33% in location 'AL' That's 10 units 'OH' and 5 units 'AL'. Then I get my actual orders by location, and compare them to the forecast.

    If the orders exceed the forecast, I'll use orders, otherwise, I use forecast. Whenever I do that, I need to reduce the forecast for the other location, in order to keep the total forecast of 15 whole. (It is not possible for total orders to exceed total forecast, I've already dealt with that.)

    Code:
    CREATE PROCEDURE tempSelect
    AS
    
    CREATE TABLE #tmpTest (
    	parent char(2),
    	proj_ship real,
    	open_ord real,
    	)
    
    insert into #tmpTest (parent, proj_ship, open_ord)
    select 'OH', 10, 4
    
    insert into #tmpTest (parent, proj_ship, open_ord)
    select 'AL', 5, 7
    
    SELECT PARENT, 'UNITS' = ???
    FROM #TMPTEST
    
    DROP TABLE #TMPTEST
    GO
    I need help with '???' in the query.

    The result set I am looking for is:
    OH 8
    AL 7
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have no way to differentiate forecasts from actuals in your schema. Seems like that could be a problem...
    You will need to store the total forecast at a higher level in the schema. For instance, how are OH and AL related, that they share a forecast? Store 15 at the level, and then you can easily balance out the actuals.
    So, it seems to me you have more of a design problem than an SQL problem.

    And another thing: If OH exceeds its forecast, then AL automatically falls below its forecast? That doesn't make any sense. What kind of business is this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Before I get to this level, I generate a forecast by customer "Bill-To", based on sales input, history, and current orders. One Bill-To can have multiple "Ship-To's", so it can be served by 2 or 3 locations. Based on this analysis, the total forecast will never be exceeded by open orders for one location. I break the location specific forecast out by taking the total forecast, and multiplying it by a factor stored in another table:

    BOD_ID|MONTH|PCT
    1234|1|.66
    1235|1|.34
    1234|2|.5
    1235|2|.5
    ...

    Where BOD_ID represents a combination of Site and Product.

    I agree that it seems odd that I would want to reduce the forecast for one location, because another location exceeds forecast. However Lawn Fertilizer is an unpredictable business, and we've decided that we want to keep the total forecast intact, because of the analysis that goes on prior to getting to that level.

    My guess would be that this will require multiple temp tables to do it without a cursor. I personally don't have anything against using a cursor, but it seems like all the pros don't like them, so I'm trying to learn the "right" way, just in case I ever want to go "Pro".
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Ok, I found either a butt-ugly or pure-genuis way of solving this:

    Code:
    CREATE PROCEDURE tempSelect
    AS
    
    CREATE TABLE #tmpTest (
    	parent char(2),
    	proj_ship real,
    	open_ord real,
    	)
    
    insert into #tmpTest (parent, proj_ship, open_ord)
    select 'OH', 10, 4
    
    insert into #tmpTest (parent, proj_ship, open_ord)
    select 'AL', 5, 7
    
    insert into #tmpTest (parent, proj_ship, open_ord)
    select 'PA', 3, 1
    
    SELECT PARENT,
    'UNITS' = CASE WHEN OPEN_ORD>PROJ_SHIP 
    THEN OPEN_ORD ELSE PROJ_SHIP-
    (((select sum(case when OPEN_ORD>PROJ_SHIP 
    THEN OPEN_ORD ELSE PROJ_SHIP END) as total from #tmpTest)-
    (select sum(proj_ship) from #tmpTest))*(proj_ship/
    (select sum(proj_ship) from #tmpTest where open_ord<proj_ship)))  
    END 
    FROM #TMPTEST
    
    DROP TABLE #TMPTEST
    GO
    This works for 2 or more sites, and if some or none of the sites' orders exceed forecast. It already looks very complex, and I still need to add grouping by product for the "real" query. Is there still a better way?
    Inspiration Through Fermentation

Posting Permissions

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