Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-29 : 14:37:32

I have a list of stock transactions in a table as follows
tradedate/transactiontype/quantity/symbol/amount
I would like to create a query that shows the holdings of an account on a specific date.

Example:
tblTrans
1/1/2005/buy/100/msft/4000
2/1/2005/buy/100/ge/3000
3/1/2005/sell/100/msft/3000
4/1/2005/buy/100/intc/2200

So I know would like to know what the holdings of this portfolio are on 5/1/2005 which is:
100 shares of intc and 100 shares of ge

If I did the query for holdings on 2/15/2005 it would show:
100 shares of msft and 100 shares of ge

Any help on this is greatly appreciated as I can't seem to comeup with a non VBA solution.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 14:52:55

[code]-- Prepare sample data
DECLARE @Trans TABLE (TradeDate DATETIME, TransactionType VARCHAR(4), Quantity INT, Symbol VARCHAR(100), Amount MONEY)

INSERT @Trans
SELECT '1/1/2005', 'buy', 100, 'msft', 4000 UNION ALL
SELECT '2/1/2005', 'buy', 100, 'ge', 3000 UNION ALL
SELECT '3/1/2005', 'sell', 100, 'msft', 3000 UNION ALL
SELECT '4/1/2005', 'buy', 100, 'intc', 2200

-- Initialize search
DECLARE @theDate DATETIME
SET @theDate = '5/1/2005'

-- Show the expected output
SELECT Symbol,
Shares
FROM (
SELECT Symbol,
SUM(CASE
WHEN TransactionType = 'Buy' THEN 1
WHEN TransactionType = 'Sell' THEN -1
ELSE NULL
END * Quantity)AS Shares
FROM @Trans
WHERE TradeDate < @theDate
GROUP BY Symbol
) AS d
WHERE Shares <> 0
ORDER BY Symbol[/code]

E 12°55'05.25"
N 56°04'39.16"

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-29 : 17:00:45

Awesome - Works perfectly. Thanks so much for the help. I knew a complicated formula or stored procedure was not necessary.

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-29 : 18:45:30

One additional question which I guess I should have added in the first but how can I join the results above with the price data from another table to add a marketvalue column?

So in the case above the resulting table d is
Symbol Shares
ge 100
intc 200

The other table is tblPricing and looks like this
date/msft/ge/intc as the columns
5/1/2005/40.00/35.00/25.00

so the joined d would look like
Symbol Shares MarketVal
ge 100 3500
intc 200 5000

Again, thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources