473,226 Members | 1,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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

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]

Nov 2 '05 #1
5 1797
(Ja*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 3 '05 #2
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.)

Nov 4 '05 #3
(Ja*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 4 '05 #4
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

Nov 5 '05 #5
Stocks rally up early this coming week (Nov 7, 2005)

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

Nov 6 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Ronnie Patton | last post by:
Hello everyone can you help me find some information out about pros and cons using a global.asa in any asp application My co-works are saying its your choice to use one or not based on what the...
0
by: Steve | last post by:
We've recently got a new Server in. The server has 16Gb of RAM, 8 cpus etc We now have a choice of sticking with Windows 2000 Advanced Server or going with Windows 2003 Enterprise edition. ...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
5
by: Fred | last post by:
Not much expertise on XSLT and trying to understand it's uses when creating apps in VS.NET? If I wanted flexibility on the UI (View aspect of M.V.C.): - How does it compare with creating...
2
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the...
10
by: Steve | last post by:
I need to build a very dynamic client and would be interested in knowing the pros and cons of using JSF and Ajax to accomplish this. Thanks. Steve
3
by: Andrea | last post by:
Hello everyone, I'd like to know which are the main pros and cons of using XML implementation in business organizations. >From a technical perspective, I find XML powerful, but looks like it is...
62
by: estherschindler | last post by:
This is part of a series examining the strengths and weaknesses of various scripting languages, with particular attention to enterprise (read: big company) use. You Used Python to Write WHAT?...
3
by: ziycon | last post by:
What would be the pros/cons of turning off error warnings in the website pages like below? What are peoples opinions on this approach? <?php error_reporting(0); ?>
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.