CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Molloch
Molloch

Reputation: 2381

SQL Server Find highest balance from transactions

Using SQL Server 2008 R2, I have a view, vwBASECustomerTransactions, with a list of transactions, which sum the aggregate data for each transaction type for each day:

Create View vwBASECustomerTransactions AS
Select CustomerID, 0 as TransType, Sum(Amount) as TransAmount, Cast(ChargeDate as Date) as TransDate
from Charge 
Group by CustomerID, Cast(ChargeDate as Date)

UNION ALL

Select CustomerID, 1, Sum(Amount), Cast(AdjustDate as Date)
from Adjustment
Group by CustomerID, Cast(AdjustDate as Date)

UNION ALL

Select CustomerID, 2, Sum(Amount), Cast(PaymentDate as Date)
from Payment
Group by CustomerID, Cast(PaymentDate as Date)

The result is:

CustomerID |  TransType  |  TransAmount  |  TransDate
-----------------------------------------------
120        |  0          |  100          |  1/1/2014
120        |  2          | -100          |  1/1/2014
120        |  0          |  50           |  17/2/2014

There are over 200,000 CustomerIDs and around 2 million Transactions.

I need to find the date where the Balance was the greatest for each CustomerID.

I have created a set of views with a balance calculated at each transaction date, which simply sums the transactions to that point in time, but it is very slow:

--Get the Net Daily Change
Create View vwBASECustomerNetBalChange as
Select CustomerID, TransDate, Sum(TransAmount) as Amount
from vwBASECustomerTransactions
Group by CustomerID, TransDate

--Get the Running Balance for any date
Create View vwRPTCustomerDailyBalance as
Select *, (Select Sum(Amount) from vwBASECustomerNetBalChange Where TransDate <= a.TransDate and CustomerID = a.CustomerID ) as Balance
from vwBASECustomerNetBalChange

--Get the Max Balance for any Customer (join back to get date)
--Takes > 10 minutes to run
Select CustomerID, Max(Balance) 
from vwRPTCustomerDailyBalance
group by CustomerID

Is there a better, more efficient way?

Upvotes: 1

Views: 1918

Answers (4)

Ewash - Tech-Huh
Ewash - Tech-Huh

Reputation: 1

How to get cumulative sum

This was the solution I used. 

set transaction isolation level read uncommitted /*prevents table locking*/

select top 1000 SUM(ad.TRN_RECPT_AMT+ad.TRN_DISC_TAKEN_AMT) OVER(PARTITION BY ad.INVC_ID ORDER BY ad.TIME_STAMP ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)[pay_td],
ah.INVC_AMT - 
SUM(ad.TRN_RECPT_AMT+ad.TRN_DISC_TAKEN_AMT) OVER(PARTITION BY ad.INVC_ID ORDER BY ad.TIME_STAMP ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)[bal_td],
ah.CUST_ID ,ah.PROJ_ID, ah.INVC_ID, ah.INVC_AMT,ad.INVC_AMT,
ad.TRN_DT,
ad.S_JNL_CD,
ad.RECPT_DT ,ad.TRN_RECPT_AMT,
ad.TRN_DISC_TAKEN_AMT, 
ad.TIME_STAMP

FROM DELTEKCP.DELTEK.AR_HDR_HS ah
join DELTEK.AR_DETL_HS AS ad on ah.INVC_ID = ad.INVC_ID
order by ah.CUST_ID,ah.INVC_ID,ad.TIME_STAMP desc
;

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

You can try this one to see if it runs any better that what you are seeing. After I finally figured out what it was exactly you were trying to do it is pretty much similar to your version :( The Union in the first subquery is going to basically select distinct values which will slow things down.

SELECT DistinctDays.CustomerID,DistinctDays.TransDate,TotalAmount=SUM(TotalAmount.TransAmount)
FROM
(
    Select CustomerID, Cast(ChargeDate as Date) as TransDate from Charge
    UNION 
    Select CustomerID, Cast(AdjustDate as Date) from Adjustment     
    UNION 
    Select CustomerID, Cast(PaymentDate as Date) from Payment

)AS DistinctDays
INNER JOIN
(
    Select CustomerID, Amount as TransAmount, ChargeDate as TransDate from Charge         
    UNION ALL
    Select CustomerID,  Amount, AdjustDate from Adjustment     
    UNION ALL
    Select CustomerID,Amount, PaymentDate from Payment
) 
AS TotalAmount ON TotalAmount.CustomerID=DistinctDays.CustomerID AND TotalAmount.TransDate<=DistinctDays.TransDate 
GROUP BY DistinctDays.CustomerID,DistinctDays.TransDate
ORDER BY DistinctDays.CustomerID,DistinctDays.TransDate

Upvotes: 1

Recursive
Recursive

Reputation: 952

Will this give you the result ? its using direct table joins.

SELECT * FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY BALANCE DESC) AS ID,*
FROM  
(SELECT A.CUSTOMERID,A.TRANSDATE,SUM(A.AMOUNT+B.AMOUNT+C.AMOUNT) AS BALANCE
FROM CHARGE  A
JOIN ADJUSTMENT B
ON A.CUSTOMERID=B.CUSTOMERID
AND CAST(A.TRANSDATE AS DATE)= CAST(A.TRANSDATE AS DATE)
JOIN PAYMENT C
ON A.CUSTOMERID=C.CUSTOMERID
AND CAST(A.TRANSDATE AS DATE)= CAST(C.TRANSDATE AS DATE)
GROUP BY A.CUSTOMERID,A.TRANSDATE)
)STAB
 WHERE ID=1

we can use where conditions if needed.Since i don't understand the running balance i skipped the where clause.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

This is too long for a comment.

Unfortunately, SQL Server 2008 doesn't directly support cumulative sum. I suspect, though, that there might be more efficient ways to get what you want. SQL Server 2012 does support cumulative sum.

I think the best approach would be to use a temporary table rather than a view. On the temporary table, you can add indexes for CustomerId, date, which should be a big help for the correlated subquery.

Another approach would be to do the cumulative sum at the table level, inside the view. This can take advantage of indexes at the table level. However, I think the temporary table approach is probably the best solution.

Upvotes: 0

Related Questions