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
SELECT P.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *
IC.[Shares]) AS [Position]
FROM [IndexComponent] IC
JOIN [Position] P
ON P.[Symbol] = IC.[IndexSymbol]
) D
GROUP BY [Account], [Symbol]
ORDER BY [Account], [Symbol]
