Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
sql query help joinkind of a tough one i think but any help is appreciated. please try to stay away from T-SQL... I have a table with products and closing dates for each of 4 quarters and annual for several years back. (Earning_Dates) I have another table that has those products with dates and closing prices. (undPrices) i have a third table which has the id for all the products. I need a query that will look at earning_dates and return the latest 6 records for each product. ALSO a query which will do that as well as return the closing_price at each of those 6 dates for each of the products in earning_dates. parts of the tables: select top 10 * from WTGlobal.dbo.Earnings_Dates select top 10 * from [wtchi-sqldb].pnl.dbo.undprices order by Trade_date desc select top 10 * from [wtchi-sqldb].pnl.dbo.products Product BB_exch Expected_Report_Dt Expected_Report_Peri od -------------------------------- ----------- ------------------------------------------------------ -------------------------------- A 3 2005-11-14 00:00:00.000 2005:A A 3 2005-11-14 00:00:00.000 2005:Q4 A 3 2005-08-15 00:00:00.000 2005:Q3 A 3 2005-05-16 00:00:00.000 2005:Q2 A 3 2005-02-14 00:00:00.000 2005:Q1 A 3 2004-11-11 00:00:00.000 2004:A A 3 2004-11-11 00:00:00.000 2004:Q4 A 3 2004-08-12 00:00:00.000 2004:Q3 A 3 2004-05-17 00:00:00.000 2004:Q2 A 3 2004-02-17 00:00:00.000 2004:Q1 (10 row(s) affected) Product_id Price_Open Price_Close Trade_Date Trade_Date_Prev Trade_Date_Next umi ----------- -------------------- -------------------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ----------- 7968 4.38000 4.43000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7963 23.32000 23.84000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7961 6.77000 7.20000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7960 10.05000 10.12000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7959 16.97000 15.99000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7958 15.72000 15.98000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7957 66.62000 70.59000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7956 31.35000 31.62000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7955 5.15000 5.09000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 7953 5.25000 5.34000 2006-02-16 00:00:00.000 2006-02-15 00:00:00.000 2006-02-17 00:00:00.000 1 (10 row(s) affected) Product_id Product Currency_id Locale_id umi update_date ----------- -------------------------------- ----------- ----------- ----------- ------------------------------------------------------ 3594 .DJX 1 40 1 2006-02-16 08:55:39.810 3595 .MNX 1 40 1 2006-02-16 08:55:39.810 3596 .MOX 1 40 1 2006-02-16 08:55:39.810 3597 .NDX 1 40 1 2006-02-16 08:55:39.810 3598 .OEX 1 40 1 2006-02-16 08:55:39.810 3599 .OSX 1 40 1 2006-02-16 08:55:39.810 3600 .RLG 1 40 1 2006-02-16 08:55:39.810 3601 .RLV 1 40 1 2006-02-16 08:55:39.810 3602 .RUI 1 40 1 2006-02-16 08:55:39.810 3603 .RUT 1 40 1 2006-02-16 08:55:39.810 (10 row(s) affected)
Post Follow-up to this message
Re: sql query help join(uspensky@gmail.com) writes: > kind of a tough one i think but any help is appreciated. please try to > stay away from T-SQL... You want an MDX query instead? :-) > I have a table with products and closing dates for each of 4 quarters > and annual for several years back. (Earning_Dates) > I have another table that has those products with dates and closing > prices. (undPrices) > > i have a third table which has the id for all the products. > > I need a query that will look at earning_dates and return the latest 6 > records for each product. > > ALSO > > a query which will do that as well as return the closing_price at each > of those 6 dates for each of the products in earning_dates. I'm sorry, but I can't connect the product in Earning_Dates to the columns in undprices or products. I could make a guess, but I don't like guessing, as it lead me to spend time solving the wrong problem. Also, I'm uncertaing which are the "latest 6 records". What all this boils down is the standarad recommendation that posts of this kind should ionclude: o CREATE TABLE statements for your tables. o INSERT statements with sample data. o The desired output, given the sample. This makes it possible to copy and paste into to Query Analyzer to develop a tested solution. Also, by providing the desired result, we get a better idea of what you are really looking for. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pr...oads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodin...ions/books.mspx
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
