Microsoft SQL Server forum - sql query help join

Author

sql query help join

uspensky@gmail.com

2006-02-17, 11:23 am

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)

Erland Sommarskog

2006-02-17, 8:23 pm

(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

Sponsored Links