Need help with a complex query in sql 2005

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)

Need help with a complex query in sql 2005

Author

Topic

V-Nest
Starting Member

10 Posts

Posted - 2007-10-24 : 19:37:17

Hello All,

I need help on a complex query that I've been working quite some time and can't figure it out. Here is my scenario;

I have 3 table. One is Securities and the other one is SecurityPrices and the last one is SecurityTypes table. What I'm trying to do is to get the latest price on these securities. However there are some conditions, for example there are some special securities that has only one price in each month. So I need to find that price to show it. Here is what I have so far.



SELECT     TOP (100) PERCENT SecurityID, SecurityPriceID, DataSetID, Price, PriceDate
FROM         dbo.SecurityPrices AS S1 
WHERE     (SecurityPriceID IN
               (SELECT     MAX(SP.SecurityPriceID) AS Expr1
                FROM          dbo.SecurityPrices AS SP  INNER JOIN
                              dbo.Securities AS S ON SP.SecurityID = S.SecurityID
                WHERE      (S1.SecurityID = SP.SecurityID AND S.SecurityTypeID = 4 AND SP.Price <> 0 AND SP.DataSetID = 3) OR
			   (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 2) AND (SP.Price <> 0) AND (SP.DataSetID = 3) OR                           
			   (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 1) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 3) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 5) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 6) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 7) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 8) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 9) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 10) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 11) AND (SP.DataSetID = 3) OR
                           (S1.SecurityID = SP.SecurityID) AND (S.SecurityTypeID = 12) AND (SP.DataSetID = 3) 
))
ORDER BY PriceDate DESC

The issue with this code is that there are special securities in all of the Security Types that don't have prices everyday but only have once a month. There is another field that might be useful but I couldn't find a way to use it is on the securities table which is called SecurityPricingID. If Those special funds have SecurityPricingID as 50.

I would greatly appreciate if you could help me on this query, I'm going to be insane soon otherwise.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 19:38:55

Please provide structure of the 3 tables + some sample code + expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

V-Nest
Starting Member

10 Posts

Posted - 2007-10-24 : 19:54:58

Table structures are;

CREATE TABLE [dbo].[SecurityPrices](
[SecurityPriceID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DataSetID] [int] NOT NULL,
[SecurityID] [int] NOT NULL,
[PriceDate] [datetime] NOT NULL,
[Price] [float] NOT NULL
CONSTRAINT [PK_SecurityPrices] PRIMARY KEY NONCLUSTERED

CREATE TABLE [dbo].[SecurityTypes](
[SecurityTypeID] [tinyint] NOT NULL,
[SecurityTypeName] [dbo].[TypeName] NULL
CONSTRAINT [PK_SecurityTypes] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Securities](
[SecurityID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SecurityTypeID] [tinyint] NOT NULL,
[DataSetID] [int] NOT NULL,
[Symbol] [varchar](9) NOT NULL,
[Description] [varchar](50) NOT NULL,
[SecurityPricingID] [tinyint] NULL
CONSTRAINT [PK_Securities] PRIMARY KEY NONCLUSTERED

It would be very hard to paste the sample data. However you can use random numbers based on the table structures to vitualize the scenario.

Expected output is;

SecurityID, SecurityPriceID, DataSetID, Price, PriceDate
123, 12548, 3, 12.20, 10/23/2007
2356, 1321, 3 15.3, 9/28/2007
.
.
.
and so on.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 02:19:03

[code]SELECT TOP (100) PERCENT
SecurityID,
SecurityPriceID,
DataSetID,
Price,
PriceDate
FROM (
SELECT sp.SecurityID,
sp.SecurityPriceID,
sp.DataSetID,
sp.Price,
sp.PriceDate,
ROW_NUMBER() OVER (PARTITION BY sp.SecurityID ORDER BY sp.SecurityPriceID DESC) AS RecID
FROM dbo.SecurityPrices AS sp
INNER JOIN dbo.Securities AS s ON s.SecurityID = sp.SecurityID
WHERE sp.DataSetID = 3
AND (
s.SecurityTypeID IN (2, 4) AND sp.Price <> 0
OR s.SecurityTypeID IN (1, 3, 5, 6, 7, 8, 9, 10, 11, 12)
)
) AS d
WHERE RecID = 1
ORDER BY PriceDate DESC[/code]

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

V-Nest
Starting Member

10 Posts

Posted - 2007-10-25 : 18:18:03

Peso,

Thank you for your reply. Your version is quite cleaner than mine however my version takes 3 seconds to return results whereas yours takes 6 seconds. Do you know why?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-10-26 : 07:10:38

Do both solutions return the CORRECT data? If so, then use the faster one.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-26 : 13:07:28

You could just use Peso's WHERE clause in your query if it is faster. Also you can remove the TOP 100 PERCENT as it does nothing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources