Need help writing an advanced MySQL statement
Posted on 2014-01-09
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?