|
Posted by uspensky on 02/17/06 18:29
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]
|