| Roy Harvey 2006-02-17, 8:23 pm |
| You requested "please try to stay away from T-SQL". Since you posted
this in microsoft.public.sqlserver.server I have to assume the data is
in Microsoft SQL Server. If there is an alternative to T-SQL I have
not heard about it.
I think these might do what you asked, but they are T-SQL.
SELECT *
FROM WTGlobal.dbo.Earnings_Dates as D
WHERE Expected_Report_Dt IN
(select top 6 D2.Expected_Report_Dt
from WTGlobal.dbo.Earnings_Dates as D2
where D.Product_id = D2.Product_id
order by Expected_Report_Dt desc)
SELECT *,
ClosingPrice =
(select Price_Close
from [wtchi-sqldb].pnl.dbo.undprices) as P
where D.Product_id = P.Product_id
and D.Expected_Report_Dt = P.Trade_Date)
FROM WTGlobal.dbo.Earnings_Dates as D
WHERE Expected_Report_Dt IN
(select top 6 D2.Expected_Report_Dt
from WTGlobal.dbo.Earnings_Dates as D2
where D.Product_id = D2.Product_id
order by Expected_Report_Dt desc)
Roy
On 17 Feb 2006 08:28:38 -0800, uspensky@gmail.com wrote:
>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)
|