INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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*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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close