Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

COMPLEX JOINS

Status
Not open for further replies.
Mikka07

Mikka07

Computer
Joined
Jun 22, 2007
Messages
2
Location
IE
Hi all

I have a problem with a complex query.

there is one price a day from every suplier in the table Prices.

PRICES
Product ID | Date | Suplier |Price

0001 | 01.01.2007 | SuplierA | 10
0001 | 01.01.2007 | SuplierB | 11
0001 | 01.01.2007 | SuplierC | 13
0001 | 02.01.2007 | SuplierA | 11
0001 | 02.01.2007 | SuplierB | 12
0001 | 02.01.2007 | SuplierC | 13
.
.
.
0002 | 01.01.2007 | SuplierA | 10
0002 | 01.01.2007 | SuplierB | 15
0002 | 01.01.2007 | SuplierB | 15
.
.
.




tab2 PRODUCTS

Product Id | Category | Quality
0001 | A | 1

0002 | A | 2

0003 | B | 1
.
.
.


i want to check price movement for the last two days and get only those records where the price changed more then 10%


The desired output format:

Product Id | Category (limited to cat A) | Quality (limited to quality 1) |Suplier | Date today | Price today | Date Yesterday | Price Yesterday | Prices Difference (>10%)



I was trying to join the table Prices and Products and on this basis create a self join, but unsuccessfully.

Any hint how to build such query would be appreciated very much.

Rgrds
 
Sort by date Sort by votes
select Products.[Product ID], Products.Category, Products.Quality, T.Suplier, T.Date as [Date Today], T.Price as [Price Today], Y.Date as [Date Yesterday], Y.Price as [Price Yesterday], (T.Price - Y.Price) as [Price Difference]
from Products,
(select [Product ID], Suplier, convert(varchar,Date,103) as Date, Price from Prices where convert(varchar,Date,101)=convert(varchar,Getdate(),101)) as T,
(select [Product ID], Suplier, convert(varchar,Date,103) as Date, Price from Prices where convert(varchar,Date,101)=convert(varchar,Getdate()-1,101)) as Y
where
Products.Category = 'A' and
Products.Quality = 1 and
Products.[Product ID] = T.[Product ID] and
Products.[Product ID] = Y.[Product ID] and
T.Suplier = Y.Suplier and
abs(T.Price - Y.Price)/Y.Price*100>0.1

I used Microsoft SQL 2005 Express. The "convert" function is used to eliminate the "time" part from Date.
Verified and worked :)
You can use also Firebird 2.0 SQL engine, where is no need for a "convert" function.
 
Upvote 0 Downvote
Correction !

select Products.[Product ID], Products.Category, Products.Quality, T.Suplier, T.Date as [Date Today], T.Price as [Price Today], Y.Date as [Date Yesterday], Y.Price as [Price Yesterday], (T.Price - Y.Price) as [Price Difference]
from Products,
(select [Product ID], Suplier, convert(varchar,Date,103) as Date, Price from Prices where convert(varchar,Date,101)=convert(varchar,Getdate(),101)) as T,
(select [Product ID], Suplier, convert(varchar,Date,103) as Date, Price from Prices where convert(varchar,Date,101)=convert(varchar,Getdate()-1,101)) as Y
where
Products.Category = 'A' and
Products.Quality = 1 and
Products.[Product ID] = T.[Product ID] and
Products.[Product ID] = Y.[Product ID] and
T.Suplier = Y.Suplier and
abs(T.Price - Y.Price)/Y.Price>=0.1
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

hypo
  • Locked
  • Question Question
joins and counts
Replies
1
Views
188
rtmpxr
rtmpxr
LD0921
  • Locked
  • Question Question
INNER JOINS
Replies
0
Views
164
LD0921
LD0921
Overflow
  • Locked
  • Question Question
Joins in SQL
Replies
1
Views
239
BML
BML

Part and Inventory Search

Sponsor

Back
Top