We help IT Professionals succeed at work.

Join 3 tables to update Prices based on last Ship Dates

158 Views
Last Modified: 2014-08-26
I'm having a hard time wrapping my head around this one. I need to find out which products have not shipped in the last 2 years, 1 year and 6 months (three queries) and then update the prices based on a percentage.

The tables I am using are:

BX
BXCNT
STOCK


BX tabele has SHIPDATE and ORDERNO

BXCNT table has ORDERNO and STOCKNUMBER

STOCK table has STOCKNUMBER and PRICE

The BXCNT table is like an order detail that contains all the stock numbers inside the order. That is the way I tie the shipdate to the number

The date format looks like this: 1999-11-18 00:00:00.000

So I want to select STOCKNUMBER and update the PRICE (price * .75) if SHIPDATE > 2 years

Hope that makes sense. Thank you for any help and information.

-S
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
The correct ways for handling DATETIME values in MySQL and PHP are given in this article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Your date format is good.  Do you have PHP available to you?

Author

Commented:
Hi, Thank you for your quick reply. No, I am on a Microsoft server (remote) using Microsoft SQL Server Management Studio. Version SQL Server 2008 R2
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the quick reply. Your example is great learning information.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
No problem.

Do consider providing sample data and expected results with questions. The process of creating it really helps focus you on the issues and the combination of data  results will do a lot of the explanation for you too.
sqlfiddle.com is very useful for this also
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.