Pros / Cons to this approach

I have a requirement where I need to perform a query for position
information. But for some types of entries, I need to "expand" the row
to include additional position rows. Let me explain with an example:

An index is a security that is made up of components where each
component has a "weight" or a number of shares. So if I have 1 share of
the index, I have X shares of each component.

AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say that
SPY has one component, AAPL, with shares being 10. (1 share of SPY = 10
shares of AAPL).

So, I do some trading and I end up with positions as follows:

+10 AAPL
-5 CSCO
+2 SPY

The query I need returns:

+10 AAPL
-5 CSCO
+2 SPY
+20 AAPL (from 2 SPY * 10 shares)

which becomes (after grouping):

+30 AAPL
-5 CSCO
+2 SPY

-------------

Based on that criteria and the following schema (and sample data):

-- Drop tables
DROP TABLE [SecurityMaster]
DROP TABLE [Position]
DROP TABLE [IndexComponent]

-- Create tables
CREATE TABLE [SecurityMaster] (
[Symbol] VARCHAR(10)
, [SecurityType] VARCHAR(10)
)

CREATE TABLE [Position] (
[Account] VARCHAR(10)
, [Symbol] VARCHAR(10)
, [Position] INT
)

CREATE TABLE [IndexComponent] (
[IndexSymbol] VARCHAR(10)
, [ComponentSymbol] VARCHAR(10)
, [Shares] INT
)

--Populate tables
INSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')
INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')

INSERT INTO [Position] VALUES ('001', 'AAPL', 10)
INSERT INTO [Position] VALUES ('001', 'MSFT', -5)
INSERT INTO [Position] VALUES ('001', 'CSCO', 10)
INSERT INTO [Position] VALUES ('001', 'SPY', 15)
INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)
INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)
INSERT INTO [Position] VALUES ('002', 'APPL', 20)
INSERT INTO [Position] VALUES ('003', 'SPY', -2)

INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)
INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)
INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)

-- *****************************

-- Based on the rules:
-- 1) Index positions appear like other positions (account /
symbol) pair, but
-- its components show up as new rows of account (of index),
symbol (equal
--to component symbol), position (equal to shares * index position)
-- 2) One row for each account / symbol pair (GROUP BY account and
symbol, SUM position)

-- Expected output (without grouping) (sorted by account / symbol)
-- 001 AAPL 10
-- 001 AAPL 375 (component shares * index position) (25
* 15) (SPY)
-- 001 AAPL 693 (component shares * index position) (33
* 21) (QQQQ)
-- 001 CSCO 10
-- 001 CSCO 750 (component shares * index position) (50
* 15) (SPY)
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50 (component shares * index position) (25
* -2) (SPY)
-- 003 CSCO -100 (component shares * index position) (50
* -2) (SPY)
-- 003 SPY -2

-- Expected output (with grouping account / symbol) (sorted by account
/ symbol)
-- 001 AAPL 1078
-- 001 CSCO 760
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50
-- 003 CSCO -100
-- 003 SPY -2

--------------

Is a UNION the best way to perform the query. What are the pros and
cons? What, if any, is a better way?

SELECT
[Account], [Symbol], SUM([Position]) AS [Position]
FROM
(
SELECT[Account], [Symbol] , [Position]
FROM[Position]

UNION ALL

SELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *
IC.[Shares]) AS [Position]
FROM[IndexComponent] IC
JOIN[Position] P
ONP.[Symbol] = IC.[IndexSymbol]
) D
GROUP BY[Account], [Symbol]
ORDER BY[Account], [Symbol](JayCallas@.hotmail.com) writes:
> I have a requirement where I need to perform a query for position
> information. But for some types of entries, I need to "expand" the row
> to include additional position rows. Let me explain with an example:
> An index is a security that is made up of components where each
> component has a "weight" or a number of shares. So if I have 1 share of
> the index, I have X shares of each component.

Now, this sounds funny to me, because in our system you can also define
indexes. However, indexes are virtual - you can never have a position
in an index directly. (But you can have a position in a derivative that
has the index as its contract base.)

> Is a UNION the best way to perform the query. What are the pros and
> cons? What, if any, is a better way?

There might be other alternatives, but I think the UNION query is fine.
Since I was given this query in my lap, I ran out of fantasy of trying
something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Not all index can have positions. For instance, the Dow Jones
Industrial Average is an an index but you cannot buy shares of it, only
shares of the components. But there is a class of indices called ETF
(Exchange Traded Fund) that you can buy and sell shares of. Here is a
link for the definition of an ETF,
http://www.investorwords.com/1755/ETF.html. (There are probably better
ones out there but this gives the basics.)|||(JayCallas@.hotmail.com) writes:
> Not all index can have positions. For instance, the Dow Jones
> Industrial Average is an an index but you cannot buy shares of it, only
> shares of the components. But there is a class of indices called ETF
> (Exchange Traded Fund) that you can buy and sell shares of. Here is a
> link for the definition of an ETF,
> http://www.investorwords.com/1755/ETF.html. (There are probably better
> ones out there but this gives the basics.)

I think we have those in Sweden as well. I would guess that our
customers handle them as stocks. At least I have not heard of any
requirement to add any support for them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I expect the DOW JONES Industrial Average Index to rocket
up past 12500 by early 2006. Today it closed at 10530.

I won't be surprised if there is a Santa Claus Rally this year (2005).

Good luck,
Steve|||Stocks rally up early this coming week (Nov 7, 2005)

DOW +100 >
NAS +35 >
watch it happen
Good luck,
Steve