troubleshooting Question

Need help writing an advanced MySQL statement

Avatar of Razzmataz73
Razzmataz73 asked on
28 Comments1 Solution912 ViewsLast Modified:
I need help (please) writing an advanced (for me at least) MySQL statement in PHP.

I need to compare weekly pricing (for 52 weeks) between 2 tables that have the common field names rate_basecode and yacht_rate_id.

I have 2 tables.
1 updates weekly by a 3rd party uploading a csv pricing file to our server and is called the “rates” table.

And one is our internal catalog which includes pricing information and is called “boatinformation”.

The Melody table shows pricing per week.
The rates table shows pricing per day (so I would need to times it by 7 before comparing it)

The Melody pricing is like 1850
The Rates pricing is like  415.000

The Melody table notes the week number in the field name (1 - 52)
The rates table notes the week as a date (like 20140109) so some logic will need to be done to figure out the week number.

The Melody table has the yacht_rate_id has associated with 1 record, that record has 52 pricing fields (1 for each week of the year)
The rates table has the yacht_rate_id has associated with 52 records, that record has 1 pricing fields (for 1 day)

So I basically need to write something like if in the Melody table where the rate_basecode & yacht_rate_id and Weeknumber are equal to the rate_basecode & yacht_rate_id and Weeknumber  in the rates table Then take the Melody response and compare it to the rates response times 7 and if it is equal do nothing and if it is not equal say it is not equal (and prepare to update the Melody table with the new pricing).

Does that make sense?

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 28 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 28 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros