quote:

---

Originally posted by matpan

Thanks visakh16, that will do the job although it does mean that we are querying the data twice causing a performance overhead. On a single query that's not an issue but this will be used with Date cross sections querying for thousands and thousands of Stocks. Is there a way to avoid/reduce the performance overhead?

Thanks

---

quote:

---

Originally posted by matpan

The stored procedure will be called by a finance application which operates off a given universe of stocks. A universe of stocks is simply the entire list of stocks that the financial application works with. If you tell the application to present the data for you in a 'Date Cross section' format then what it will do is call the stored procedure below for every stock (whose identifier is the @SEDOL parameter) and then present the data to you grouped by date.

For all intents and purposes, what i'm saying is that the stored procedure will will be called thousands if not hundreds of thousands of times in rapid succession.

CREATE PROCEDURE dbo.GetTotalValueByDivReq (@SEDOL VARCHAR(7), @DIVREQ_IDENTIFIER int)

AS

SELECT Dateadded, TotalValue
FROM
(
SELECT Dateadded, TotalValue,
CASE WHEN DividendRequirement = 100 THEN 1
WHEN DividendRequirement > 100 THEN 2
WHEN DividendRequirement < 100 THEN 3
END AS DivReqIdentifier
FROM tableName
WHERE SEDOL = @SEDOL
AND RecordType = 2
) t
WHERE t.DivReqIdentifier = @DIVREQ_IDENTIFIER

---