Query Help Part 2 - 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 Part 2

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-30 : 21:03:40

Peso provided me with a perfect solution to a query problem that I had yesterday with this t-sql code but I need to add an additional twist and I have been unsuccessful thus far.

I am trying to join the results from a query with the price data from another table and also add a column.

So using the example from Peso's solution
////////////////////////////////////
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
//////////////////////////////////

I now have a resulting table d that looks like this.
Symbol Shares
ge 100
intc 200

The table I am trying to join 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

Any help is greatly appreciated.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:07:34

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

declare @tblpricing table(date datetime,msft decimal(4,2),ge decimal(4,2), intc decimal(4,2))
insert @tblpricing
select '5/1/2005', 40.00, 35.00, 25.00

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

-- Show the expected output
SELECT Symbol,
Shares,
shares * cast(e.col2 as decimal(4,2)) as Marketval
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 join
(Select 'msft' as col1, cast(msft as varchar) as col2 from @tblpricing union all
Select 'ge' as col1, cast(ge as varchar) as col2 from @tblpricing union all
Select 'intc' as col1, cast(intc as varchar) as col2 from @tblpricing
) as e on d.Symbol = e.col1 WHERE Shares <> 0
ORDER BY Symbol

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 02:33:32

Why are you converting msft to a varchar and then back to decimal, when it already is decimal?
Also, what happens with your query if there are more records in the pricing table?

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

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-08-31 : 02:46:01

ohh...sorry yaar....Conversion not required....initially i included date also thats why i casted, then i removed the date and forgot to remove the casting.

SELECT Symbol,
Shares,
shares * e.col2 as Marketval
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 join
(Select 'msft' as col1, msft as col2 from @tblpricing union all
Select 'ge' as col1, ge as col2 from @tblpricing union all
Select 'intc' as col1,intc as col2 from @tblpricing
) as e on d.Symbol = e.col1
WHERE Shares <> 0
ORDER BY Symbol

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 02:50:10

[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

DECLARE @Pricing TABLE(Date DATETIME, msft DECIMAL(4,2), ge DECIMAL(4,2), intc DECIMAL(4,2))

INSERT @Pricing
SELECT '5/1/2005', 40.00, 35.00, 25.00 UNION ALL
SELECT '4/30/2005', -1.00, -1.00, -1.00

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

-- Show the expected output
SELECT d.Symbol,
d.Shares,
e.Price,
d.Shares * e.Price AS Marketval
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
LEFT JOIN (
SELECT x.Date,
x.Stock,
x.Price,
ROW_NUMBER() OVER (PARTITION BY x.Stock ORDER BY x.Date DESC) AS RecID
FROM @Pricing
UNPIVOT (
Price FOR Stock IN (msft, ge, intc)
) AS x
WHERE x.Date <= @theDate
) AS e ON e.Stock = d.Symbol AND e.RecID = 1[/code]

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

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-31 : 11:44:39

Thanks for the help.
There is one problem though. I am trying to avoid actually naming the tickers. The Price table actually has about 100 columns each representing a different ticker. Is it possible to do this without naming each of the tickers? The reason is that with several years of data there will be several transactions but at one point in time the first Select query that Peso provided gets just the holdings on that date so I only want to lookup those prices.

I guess this brings me to a whole different question which is whether or not my price table should be setup as it is (Tickers as column names).
Should it be a different table for each ticker?

I'm starting to think this is something that should be done on the app interface side.

Again, thank you for your time and help. Any additional insight is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-31 : 15:13:30

That is REALLY bad design.
If you can, redesign that part of your application so that the ticker information is stored normalized.

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

legacyvbc
Starting Member

37 Posts

Posted - 2007-08-31 : 19:38:03

What does that mean normalized? Logically, it seems that having date as a column and ticker as a column gives you the least amount of redundant data (not repeating dates). Would you provide an example? If I create a new table for each ticker don't I still have a problem looking up the values since I have to reference the table name?

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources