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.)

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 7You 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?|||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".|||Ok, I found either a butt-ugly or pure-genuis way of solving this:

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?

how to do it without using cursor?

Hi,

I need to insert several rows into two tables like: table product and table productDetail.

The product table has an identity column and the productDetail table has an id column references to the identity column.

So when one row is inserted into the product table, the corresponding productDetail row should be populated.

If there is only one row to insert, it won't be a problem. How can I insert several rows? I know I can use cursor, but I always heard about the performance issues related. Is there a chance to avoid cursor?

Many Thanks

Hi,

Try out the below mentioned link ,might be it helps you out.

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

Please don't use cursors or WHILE loop for these sort of operations. It will not scale well and perform poorly. In SQL Server 2005, you can do the following:

-- assuming that you are have the data to be inserted

-- in a temporary table for example:

begin tran

declare @.newprods table ( productid int, productname varchar(50) )

insert into product (productname...)

ouput inserted.productid, inserted.productname into @.newprods

select distinct productname ....

from #products

insert into productdetail (productid, sellername...)

select np.productid, p.sellername

from #products as p

join @.newprods as np

on np.productname= p.productname

...

commit

You can use similar logic in SQL Server 2000 also.