Complex SQL query for current shared column

Question

I am trying to work out a rather complicated query in SQL Server 2008. I would like to get SQL experts here.

Imagine that I had a payment table with these fields:

PaymentID int, CustomerID int, Date and time of sending PaymentDate, Decimal amount

In fact, this is a table of payments made by the client on specific dates. It is important to note that in some cases the payment amount may be negative. Thus, over time, the total amount paid by any client may increase or decrease.

What we are trying to figure out is SQL to calculate the maximum amount of the total amount paid for the client.

So, if Fred made 3 payments: first for $ 5, the second for $ 5, the third for $ 3. The report will show that the total amount paid for the Fred peak was $ 10 (on his second payment), and his final amount paid was $ 7.

We need to run this report for hundreds of thousands of customers (who could potentially make from one to a thousand payments each), so it should be fast.

Is there a good way to structure this query without saving the current totals in db? We would like to avoid storing previously calculated values, if at all possible.

Answer 1

Your question is as follows:

SELECT CustomerID, SUM(Ammount) FROM table WHERE Amount > 0 GROUP BY CustomerID SELECT CustomerID, SUM(Ammount) FROM table GROUP BY CustomerID 

However, I think you mean that you need a table that looks like this:

 Customer Payment HighPoint RunningTotal 123 5 5 5 123 5 10 10 123 -3 10 7 

In this case, I would create a view with the two selected above so that the view is something like.

 SELECT CusotmerID, PaymentDate, Ammount, (SELECT SUM(Ammount) FROM table as ALIAS WHERE ALIAS.Amount > 0 AND ALIAS.PaymentDate <= PaymentDate AND ALIAS.CustomerID = CustomerID), (SELECT SUM(Ammount) FROM table as ALIAS WHERE ALIAS.CustomerID = CustomerID AND ALIAS.PaymentDate <= PaymentDate) FROM table 

In addition, you may want to consider a non-unique index in the Sum column of the table to speed up browsing.

Answer 2

The operation is linear in the number of payments for each client. Thus, you will have to switch to each payment, maintaining the total amount and a high mark with water, and at the end of all payments you will receive a response. Regardless of whether you are doing this in a CLR stored procedure (immediately in response to me) or using a cursor or tempo table or something else, this is probably not going to be fast.

If you need to run this report again and again, you should seriously consider maintaining a high water field and updating it (or not) whenever a payment occurs. Thus, your report will be trivial, but this is what these storefronts belong to.

Answer 3

As an alternative to subqueries, you can use the current general query. Here is how I installed it for this case. First create some test data:

 create table #payments ( paymentid int identity, customerid int, paymentdate datetime, amount decimal ) insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-01',1.00) insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-02',2.00) insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-03',-1.00) insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-04',2.00) insert into #payments (customerid,paymentdate,amount) values (1,'2009-01-05',-3.00) insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-01',10.00) insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-02',-5.00) insert into #payments (customerid,paymentdate,amount) values (2,'2009-01-03',7.00) 

Now you can fulfill the current general request, which calculates the balance for each client after each payment:

 select cur.customerid, cur.paymentdate, sum(prev.amount) from #payments cur inner join #payments prev on cur.customerid = prev.customerid and cur.paymentdate >= prev.paymentdate group by cur.customerid, cur.paymentdate 

This generates data:

 Customer Paymentdate Balance after payment 1 2009.01.01 1 1 2009.01.02 3 1 2009.01.03 2 1 2009.01.04 4 1 2009.01.05 1 2 2009.01.01 10 2 2009.01.02 5 2 2009.01.03 12 

To view the maximum, you can run the group according to the current general request:

 select customerid, max(balance) from ( select cur.customerid, cur.paymentdate, balance = sum(prev.amount) from #payments cur inner join #payments prev on cur.customerid = prev.customerid and cur.paymentdate >= prev.paymentdate group by cur.customerid, cur.paymentdate ) runningtotal group by customerid 

What gives:

 Customer Max balance 1 4 2 12 

Hope this is helpful.

Answer 4

 list = list of amounts ordered by date foreach in list as amount running += amount if running >= high high = running 

To maintain this quickly, you will need a total amount with the added amount on the trigger and a high value for each client (can also be updated using the trigger to make re-requesting even easier).

I don’t think you can do such things without code (stored procedures are code)

Answer 5

as the answer of Andomar. You can complete the total amount for each payment. Then find the maximum peak payment ...

 with rt as ( select Payments.*, isnull(sum(p.Amount), 0) + Payments.Amount as running from Payments left outer join Payments p on Payments.CustomerID = p.CustomerID and p.PaymentDate <= Payments.PaymentDate and p.PaymentID < Payments.PaymentID ), highest as ( select CustomerID, PaymentID, running as peak_paid from rt where PaymentID = (select top 1 rt2.PaymentID from rt rt2 where rt2.CustomerID = rt.CustomerID order by rt2.running desc, rt2.PaymentDate, rt2.PaymentID) ) select *, (select sum(amount) from Payments where Payments.CustomerID = highest.CustomerID) as total_paid from highest; 

however, since you have about 1 million payments, this can be rather slow. As others say, you would like to keep CustomerID, PaymentID, and peak_paid in a separate table. This table can be updated in each payment insert or in the form of sqljob.

Updated query to use join instead of subqueries. Since PaymentDate does not have time, I am filtering multiple payments on the same day using PaymentId.