COMPLEX JOINS

[Mikka07]

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

 

[rtmpxr]

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.

 

[rtmpxr]

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