COMPLEX JOINS
COMPLEX JOINS
(OP)
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
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
Talk To Other Members
RE: COMPLEX JOINS
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.
RE: COMPLEX JOINS
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