I want to identify when a running total for a given customer is repeated over time.
I have 3 tables
Table 1 - list of customer and status
Table 2 - fact table with the fields:
Table 3 - Same as Table 2 plus a couple of fields
Running total 1
Running total 2 (is same as Running total 1 but rounded)
I want to identify when a running total (Running total 2) for a given customer is repeated over time.
I need a stored procedure with following steps:
1 read table 1
2 for each [customer id] (one at a time)
3 delete records in table 3 for the selected customer
4 calculate running total
a) if the table 2 has 1000 records, then table 3 will have same records (1000) with 3 more fields (running total 1, running total 2 and status)
b) IF the last [running total 2] is zero or between -1 and +1, then go to next customer, ELSE go to “c”
c) status will be calculated by SP. When you calculate a running total, you must compare the value with the running total calculated for the same customer but prior to the date you are working with
d) If the [running total 2] repeats, exact same value, then you fill status with value: “review”, and also fill the same value “review” in table 1. If running total does not repeat, leave status empty and go next
Go to next customer
If running total is zero, do not compare to former running total, skip it.
The SP must calculate all running totals for a given customer before calculating the status.
In case the last running total is zero or between -1 and +1 (almost zero), then DO NOT calculate status for this customer, Skip and go to next customer.
We need some tolerance, so we will make running total calc this way:
Running total 1 is exact, with 2 decimals.
[Running total 2] will be calculated after [running total 1] and will be rounded:
[running total 2]=round([running total 1]; 0).
When you compare running total to get “STATUS”, you must compare the values in “Running total 2”.