Trying to create a script to only load the most recent rating for each customer.
I have a number of tables pulling in from a database.
One of the tables is a customer rating, for the example of this post, let's say each customer has a rating from 1-4. Customer ratings can be updated, in which case the unique rating_id will be larger for the newer rating, but the older rating will be included.
In the example above, Customer A was rated a 1, then 3, then 4. Customer B was rated a 3, then a 2. Customer C was rated a 1.
So I need the load script to output a 1 for A, a 2 for B and a 1 for C which I would then map or join to the existing customer table.
How could I do this?
I was considering a loop in the load script which would, for every customer, go through each row of that customer and set the rating_id to a variable (X) and set the rating as another variable (Y). Then it goes to the next record and compares the rating_id to the rating_id variable (X), and if it's smaller it does nothing, if it's larger, it sets the new rating_id as (X) and the rating for that record as (Y). At the end of the loop, it uses the (Y) variable to set the rating for a customer.
However, before I figure out how to do this, I wanted to know if there's an easier and more efficient way given the number of customers in the table (it's large).