|
Posted by John Bell on 02/19/06 14:13
Hi
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_Period
> -------------------------------- -----------
> ------------------------------------------------------
> --------------------------------
> 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)
>
[Back to original message]
|