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 this in SQL Server Stored Procedure

Here is my problem:

I am designing Support System. I have a stored procedure for storing new Support Ticket. This stored procedure internally gets next ticket number and inserts Support Ticket

CREATE PROCEDURE [sp_SaveSupportTicket]
(
@.pid int,
@.uidGen int,
@.status VarChar (100),
@.probDes text,
@.probSol text,
@.guestName VarChar (100),
@.os VarChar (100),
@.roomNum VarChar (100)
)
AS
DECLARE @.ticNum int
SELECT @.ticNum = MAX(ticNum) + 1 FROM sup_TicDetails
INSERT INTO sup_TicDetails ( ticNum, pid, uidGen, status, probDes, probSol, guestName, os, roomNum,dateofsub)
VALUES (@.ticNum, @.pid, @.uidGen, @.status, @.probDes, @.probSol, @.guestName, @.os, @.roomNum, CONVERT(VARCHAR,GETDATE(),101))
GO

Now... before this happens, on my ASP.NET Page I have a label Ticket# . This label displays next ticket number

CREATE PROCEDURE [sp_GetNextTicketNumber] AS
SELECT max (ticNum) + 1
FROM sup_TicDetails
GO

Now.. how can I have only 1 stored Procedure so that I can obtain next ticket number and display it on ASP.NET page and when I hit "Submit Ticket" sp_SaveSupportTicket gets executed ??

I hope I have made my problem clear !! If not let me know......What you are doing will fail under load. BEtween the time you get the ticket number and insert, another thread could have inserted that same number. Why not use an IDENTITY column? Is it really essential to have the ticket number before you actually save it?|||I see what you are saying !!! no its not necessary to get ticket number before I save it.. it is just to display purpose.

There are problems with identity column ( I read in couple of aticles) so, I am not using Identity column..

Ok, if I don't display ticket number before saving it on my ASP.NET page.. will it still fail while load ??

How to handle thread part !!!!|||I have not heard of the problems you describe with Identity columns. They are certainly more reliable than what you have done here. Even if you do not display the number first, what you are doing will be prone to failure if multiple users are hitting the stored procedure at once. Trust me, Identity is the answer, and then use Scope_Identity() to get the new ID created right after an insert.|||Read this...

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

Note that if you delete row/s in a table and start adding row/s, the identity column will pick up where it left off.

If I delete a ticket, or try to rollback, I will in problem with Identity column...|||Here is link on MS Website describing Identity Column problem

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

In our case, I am not sure, but we might have to delete some tickets and than I will be in trouble when I run Reporting Query !!!!!|||After talking to you I me and my friend tried ...

We both saw Ticket# 617 on our ASP.NET display page...

After entering data and hitting "submit", we went into Database to see what happend.

We saw 2 tickets Ticket# 617 created by me and 618 created by him.

So I guess concurrent access didn't gave us problem for insertion.. it displays wrong ticket number on screen ....

I guess I should remove Ticket# from screen than !!!!!!!!!!!

Any other idea ...|||The problem is that you may not be able to duplicate it at will, but the code you are using CAN fail if the two request come in at EXACTLY the same moment.|||Ok.. douglas

I gave you reason why I am not using Identity Column and I told you what happened when we submitted tickets.

I am understanding what you are saying.. .so what is the solution ? What are the chances that both tickets arriave to database at exactly same time ? What happens in that case ?

How can I handle this issue ? Everyone who is developing Web Applications might be running into same issues, how they are handling this or how are you handling such problem ?|||I use Identity columns as meaningless keys to link various rows in a database. It would not bother me if a row was deleted, because I would never rely on these numbers to know how many tickets were outstanding. I would use a SQL Query, not rely upon the ID number of the row. This matches what you would do in a system of paper tickets, because if a ticket is discarded, you would void the ticket and not reuse that number.

How often will folks have a problem with duplicate ticket numbers? Hard to say. You could use some sorts of locking inside of transactions. This is not something I would normally do, and it will serialize this section of code, meaning that only a single user at a time will be able to get to this section of code.|||hummm... Thats interesting !!!! If that is the case how will you set up primary key in your table ?

Here is structure of my table:

ticNum int 4 (primary key) (not auto generated, not identity)
pid int 4 (Property ID Referes to Property Table Primary Key)
uidGen int 4 (UserID Referes to User table Primary Key)
status varchar100
probDes text 16
probSol text 16
guestName varchar100
os varchar100
roomNum varchar100
dateofsub smalldatetime4|||Here is what I found out.. I have total 614 tickets, but when I do SELECT COUNT(*) from sup_TicDetails I see 614. I scanned my records and found that Tic# 100 and 472 are missing.

So I guess my previous developer might be using Identity. I removed Identity column after reding 2 articles i mentioned.

if I try convert primary key into Identity column now, will I be having any problem ?

How can I reset my Identity column every once in a while to fill out gaps or is it Ok to Have Column as identity & primary key and have gap in it ??|||It causes NO PROBLEM AT ALL to have gaps in the numbers.|||So, how you do testing of your code ?Everytime I Add/delete I am going to create gaps and when I transfer that on my production system, I am going to have gap tooo.....

How do I handle database on local system and on production system ?|||This is never something that has been an issue for me...