Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
SQL query helpkind 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 helpHi Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example data for your tables, along with the expected output from your example data it will be a lot cleared than any long description. In general you will need to JOIN your tables on date/date range and product ID. To get the top 6 records per product ID you can use a subquery. John <uspensky@gmail.com> wrote in message news:1140132717.183081.289280@g47g2000cwa.googlegroups.com... > kind 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
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
