Re: SQL Question running totals

There is an OLAP function for that in i7.3. Here is a link to an article
on it, and a snippet from the article demonstrating the running total, in
case the link becomes invalid someday.

https://www.itjungle.com/2016/05/10/fhg051016-story01/


SELECT soh.SalesOrderId,
soh.OrderDate,
soh.CustomerId,
sod.ProductId,
sod.OrderQty,
sod.UnitPrice,
DEC(sod.OrderQty*UnitPrice,11,2) AS SubTotal,
SUM(OrderQty*UnitPrice)
OVER(PARTITION BY CustomerId
ORDER BY ProductId
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS Rolling_Customer_Total
FROM SalesOrderDetail sod
JOIN SalesOrderHeader soh ON soh.SalesOrderId=sod.SalesOrderId
WHERE OrderDate BETWEEN '2006-01-01' AND '2006-03-31'
ORDER BY soh.CustomerId,sod.ProductId
___________________________________
Darren Strong
Dekko





From: Don Brown <DBrown@xxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 12/12/2017 05:37 AM
Subject: SQL Question running totals
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Hi all,

Just wondering how I could do the following.

I have a file with Company, Account, Financial period and amount.

I want to get a period total and a running total by period.

I can get the period total easily using the group by Company Account and
period but how can I also get a running total Eg total to date by period ?

So this would effectively give me a total by period and a closing balance
by period.


Appreciate any suggestions



Don Brown