SP problem

[xloop]

Hi

At the moment i have a sp that runs in 40 seconds and it needs to run in about 5 secs.

I'm populating a temporary table with about 20-50 rows of items. For each item i need to do some calculations from another table with roughly 20,000 (up to 100,000) records.

For each item i run these stored functions;

UPDATE #StockReport SET ValueAtStart=(dbo.fn_GetStockBalance(#StockReport.StyleID, @FromDate))

UPDATE #StockReport SET ValueAtFinish=(dbo.fn_GetStockBalance(#StockReport.StyleID, @ToDate))

UPDATE #StockReport SET WIP = dbo.fn_GetWIP(#StockReport.StyleID, @FromDate,@ToDate)

UPDATE #StockReport SET Broken = dbo.fn_GetBroken(#StockReport.StyleID,@FromDate, @ToDate)

UPDATE #StockReport SET Available = dbo.fn_GetAvailables(#StockReport.StyleID,@FromDate, @ToDate)

Now, the first four just run two count select statments and return the result, like so:

SET @QtyIN = (SELECT COUNT(*) FROM tblItemTransactions WHERE (StyleID = @StyleID) AND (TDate BETWEEN CONVERT(DATETIME,@FromDate, 103) AND CONVERT(DATETIME,@ToDate, 103)) AND (ToStatusID = 2))
SET @QtyOUT = (SELECT COUNT(*) FROM tblItemTransactions WHERE (StyleID = @StyleID) AND (TDate BETWEEN CONVERT(DATETIME,@FromDate, 103) AND CONVERT(DATETIME,@ToDate, 103)) AND (FromStatusID = 2))

Return (@qtyIN - @QtyOut)

However the fn_GetAvailables function must subtract WIP and Broken from what is actually available, instead of using whats already been calculated (by fn_GetWIP & fn_GetBroken) i end up having to call them again in fn_GetAvailables because i don't know another way of doing it.

fn_GetAvailables
---------------

SET @QtyIN = (SELECT Count(*) FROM tblItemTransactions WHERE (TDate<=CONVERT(Datetime, @ToDate, 103)) AND (StyleID=@StyleID) AND (TransactionType='IN'))
SET @QtyOUT = (SELECT Count(*) FROM tblItemTransactions WHERE (TDate<=CONVERT(Datetime, @ToDate, 103)) AND (StyleID=@StyleID) AND (TransactionType='OUT'))

SET @QtyIN = ISNULL(@QtyIN,0)
SET @QtyOUT = ISNULL(@QtyOUT,0)

-- Return everything that is currently WIP/Broken (taken from ToDate)
Exec @QtyWIP =dbo.fn_GetWIP @StyleID, @FromDate, @ToDate
Exec @QtyBroken = dbo.fn_GetBroken @StyleID,@FromDate,@ToDate

SET @DeductAmt = @QtyOUT + @QtyWIP + @QtyBroken
SET @DeductAmt = ISNULL(@DeductAmt,0)

RETURN (@QtyIN - @DeductAmt) -- The total available stock will be Everything minus WIP and Broken


Is there at all a better more sophisticated way of doing this.
Code example would be a great help.

Thanks