En cours

SQL Server Stored Procedure running total compare values

Hello

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

Customer ID

Status

Table 2 - fact table with the fields:

Customer

customer ID

Date

Value (Amount)

Table 3 - Same as Table 2 plus a couple of fields

Customer ID

Date

Value

Running total 1

Running total 2 (is same as Running total 1 but rounded)

Status

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”.

Thanks

Compétences : Serveur SQL Microsoft, SQL

Voir plus : sql get date, sql query copy stored procedure server, sql server stored procedure longitude latitude, sql server stored procedure zip code radius search, sql server stored procedure zip proximity, stored procedure copy data sql server, sql server stored procedure copy data, transaction sql server stored procedure, sql server stored procedure copy data table, sql server stored procedure copy table, sql server stored procedure resize image

Concernant l'employeur :
( 67 commentaires ) Silla, Spain

N° du projet : #8521963