Long Query Not working - 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 2000 Forums

Transact-SQL (2000)

Long Query Not working

Author

Topic

legacyvbc
Starting Member

37 Posts

Posted - 2007-09-28 : 13:27:18

Ahhhh this t-sql stuff is frustrating. The code below is what I have but it isn't returning the correct data.
Summary: 4 tables (tOrders, tCalendar, tPrice, tDividends)
I'm trying to get all orders from a tickerid (tId) and then add to the set of records all dividends paid by tId, rId, hId, and also add to the set of records all rebalance dates according to @rebal
I then want to join the prices of tId,rId,hId from tPrice onto the table of records above.

The code that I have is returning a duplicate record for each record and is not including the records from the UNION ALL tCalendar query.

Any help is greatly appreciated

Sample Data (reduced to include less columns than original query)

			
tCalendar			
dt	tdEOM		
2/22/2007	0		
2/23/2007	0		
2/24/2007	0		
2/25/2007	0		
2/26/2007	0		
2/27/2007	0		
2/28/2007	1		
3/1/2007	0		
3/2/2007	0		
			
tOrders			
Date	TickerId	TranId	Qty
2/23/2007	5	1	100
2/25/2007	4	2	200
3/5/2007	4	1	500
			
tDividends			
ExDate	TickerId	TranId	Amt
2/24/2007	5	5	0.15
2/28/2007	4	5	0.25
3/2/2007	73	5	0.15
			
tPrice			
Date	TickerId	Price_Close	
2/22/2007	15	 23.50 	
2/23/2007	15	 23.25 	
2/24/2007	15	 23.75 	
2/25/2007	15	 23.60 	
2/26/2007	15	 23.35 	
2/27/2007	15	 23.85 	
2/28/2007	15	 23.70 	
3/1/2007	15	 23.45 	
3/2/2007	15	 23.95 	
3/3/2007	15	 23.80 	
3/4/2007	15	 23.55 	
3/5/2007	15	 24.05 	
2/22/2007	86	 17.63 	
2/23/2007	86	 17.44 	
2/24/2007	86	 17.81 	
2/25/2007	86	 17.70 	
2/26/2007	86	 17.51 	
2/27/2007	86	 17.89 	
2/28/2007	86	 17.78 	
3/1/2007	86	 17.59 	
3/2/2007	86	 17.96 	
3/3/2007	86	 17.85 	
3/4/2007	86	 17.66 	
3/5/2007	86	 18.04 	
2/22/2007	73	 52.88 	
2/23/2007	73	 52.31 	
2/24/2007	73	 53.44 	
2/25/2007	73	 53.10 	
2/26/2007	73	 52.54 	
2/27/2007	73	 53.66 	
2/28/2007	73	 53.33 	
3/1/2007	73	 52.76 	
3/2/2007	73	 53.89 	
3/3/2007	73	 53.55 	
3/4/2007	73	 52.99 	
3/5/2007	73	 54.11

Result Set from Query should be


Dt	TickerId	TranId	Qty	tPrice	hPrice	rPrice
2/23/2007	5	1	100	 23.25 	 52.31 	 17.44 
2/25/2007	4	2	200	 23.60 	 53.10 	 17.70 
3/5/2007	4	1	500	 24.05 	 54.11 	 18.04 
2/24/2007	5	5	0.15	 23.75 	 53.44 	 17.81 
2/28/2007	4	5	0.25	 23.70 	 53.33 	 17.78 
3/2/2007	73	5	0.15	 23.95 	 53.89 	 17.96 
2/28/2007	0	6	0	 23.70 	 53.33 	 17.78


DECLARE @tId smallint  
DECLARE @hId smallint 
DECLARE @rId smallint 
DECLARE @rebal tinyint 
DECLARE	@stdt smalldatetime
DECLARE	@endt smalldatetime
SET @tId = 15
SET @hId = 73
SET @rId = 86
SET @rebal = 4

SET @stdt = (SELECT MIN([DATE]) FROM dbo.tOrders WHERE tickerid = @tid)
SET @endt = (SELECT MAX([DATE]) FROM dbo.tOrders WHERE tickerid = @tid)

			SELECT a.DATE,a.TickerId,a.TranId,a.Qty,a.Prin,a.Com,a.Amt, 
					b.Price_Close as HedgePrice, c.Price_Close AS ReinvestPrice, d.Price_Close AS TickPrice
			FROM
			(
				SELECT	DATE,TickerId,TranId,Qty,Prin,Com,Amt  
				FROM	dbo.tOrders 
				WHERE	tickerid = @tId
			UNION ALL
				SELECT  ExDate as Date, TickerId, 5 as TranId,0 as Qty,Amount as Prin, 0 as Com, 0 as Amt
				FROM	dbo.tblDividends 
			UNION ALL
				SELECT	DT, 0 as TickerId, 6 as TranId, 0 as Qty, 0 as Prin, 0 as Com, 0 as Amt
				FROM	dbo.tCalendar
				WHERE	(
							(@rebal=0 AND TD = 1)
				OR			(@rebal=1 AND TDEOY=1)
				OR			(@rebal=4 AND TDEOQ=1)
				OR			(@rebal=12 AND TDEOM=1)
				OR			(@rebal=52 AND TDEOW=1)
						)							
			) a INNER JOIN
				dbo.tPrice b ON a.Date = b.Date INNER JOIN
					dbo.tPrice c ON a.Date = c.Date INNER JOIN
						dbo.tPrice d ON a.Date = d.DATE 
			WHERE	(a.TickerId = @tId) AND (b.TickerId = @hId) AND (c.TickerId = @rId) AND (d.TickerId=@tId)
							AND (a.Date>@stdt) AND (a.Date <= @endt) OR
					(a.TickerId = @hId) AND (b.TickerId = @hId) AND (c.TickerId = @rId) AND (d.TickerId=@tId)
							AND (a.Date>@stdt) AND (a.Date <= @endt) OR
					(a.TickerId = @rId) AND (b.TickerId = @hId) AND (c.TickerId = @rId) AND (d.TickerId=@tId)
							AND (a.Date>@stdt) AND (a.Date <= @endt)
			ORDER By a.Date asc, a.qty asc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-28 : 13:29:53

We are not familiar with your data or your schema, so you need to provide more information. Please post sample data from all tables involved in the query and the expected result set using that same sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources