× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.