cancel
Showing results for 
Search instead for 
Did you mean: 

Running Total in QLD

Former Member
0 Kudos

Hi,

Is there a way in QLD can have a running total like the Crystal Report capabilities? Is this possible in the Repetative Area1.

Example :

DocTotal Drawsum Result

500000 200000 300000

300000 100000 200000

200000 200000 0

The purpose of this is for Cash Advance Monitoring from AP Downpayment Invoice.

Is this possible by running difference / Balance ?

Thank you very much.

Regards,

clint

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Query for Running Total

former_member583013
Active Contributor
0 Kudos

Based on your sample table and data, this is what I came out with...

SELECT T0.DocEntry, T0.DocDate, T0.CardName, 
T0.LineTotal+(SELECT T0.DocTotal1-SUM(LineTotal) FROM  Query_Result WHERE DocEntry <=T0.DocEntry) [DocTotal], 
T0.LineTotal, (SELECT T0.DocTotal1-SUM(LineTotal) FROM  Query_Result WHERE DocEntry <=T0.DocEntry) [Running total]
FROM Query_Result T0

Former Member
0 Kudos

Hi Suda,

You got me! You solve my problem again, What else you cant do?

Hope that others will make this a very good reference.

Thank you very much.

Clint

former_member583013
Active Contributor
0 Kudos

Hello Clint,

I am glad I was able to help.

Best wishes

Suda

Former Member
0 Kudos

Hi Suda,

I have a problem when adding a total information from the query above :

INSERT INTO [Query_Result] ([DocEntry], [DocDate], [CardName], [DocTotal], [LineTotal], [BaseAbs], [DrawnSum], [DocTotal1], [Bal], [Running])

VALUES (1474, '20080107', N'SAADA PASIGAN', N'0', N'683628.26', 129, N'683628.26', N'700000', N'16371.74', N'16371.74')

GO

INSERT INTO [Query_Result] ([DocEntry], [DocDate], [CardName], [DocTotal], [LineTotal], [BaseAbs], [DrawnSum], [DocTotal1], [Bal], [Running])

VALUES (1479, '20080108', N'SAADA PASIGAN', N'42424.76', N'58796.5', 129, N'16371.74', N'700000', N'683628.26', N'683628.26')

GO

INSERT INTO [Query_Result] ([DocEntry], [DocDate], [CardName], [DocTotal], [LineTotal], [BaseAbs], [DrawnSum], [DocTotal1], [Bal], [Running])

VALUES (1500, '20080108', N'SAADA PASIGAN', N'34481.36', N'718109.62', 129, N'683628.26', N'700000', N'16371.74', N'16371.74')

GO

It seems that the Running Query will not set to zero if a new BaseAbs or New AP Downpayment was added, it will compute continuously.

Is there anything we miss on the query? or a condition perhaps.

Thank you very much.

Clint

former_member583013
Active Contributor
0 Kudos

Hi Clint,

I don't think I quite understand your question. Could you please explain it with the example of your data set.

Suda

Former Member
0 Kudos

Hi Suda,

Sorry for making hard to understand.

We involve a running query and I the result must be per Document not the whole data.

Example from the first sample we have :

DocEntry BaseAbs DocTotal     LineTotal RunningTotal

1575       135        500000.00 165308.83   334691.17

1592 135   334691.17 79563.60   255127.57

1599 135   255127.57 53102.95   202024.62

1604 135   202024.62 38415.60   163609.02

This is correct, Because we deduct the 500000 from its withdrawal.

The new scenario is what if we added and combine a new BaseAbs entry and with AP Downpayment of 700000.

Like below :

DocEntry BaseAbs DocTotal LineTotal RunningTotal

1474 129 700000.00 683628.26 16371.74

1479 129          16371.74 58796.50 -42424.76

1500 129 -42424.76 718109.62 -760534.38

1575 135 -960534.38 165308.83 -1125843.21

1592 135 -1125843.21 79563.60 -1205406.81

1599 135 -1205406.81 53102.95 -1258509.76

1604 135 -1258509.76 38415.60 -1296925.36

If you can observe the running balance is continuous and the BaseAbs 129 total amount is 700000 and BabeAbs 135 amount is 500000, the result must be it will reset to zero per 1 new BaseAbs is encountered and start running the balance.

Hope this will clear all.

Thank you very much,

Clint

former_member583013
Active Contributor
0 Kudos

Check these two queries. The Second one is what you could use and the first one is for reference.

SELECT T0.DocEntry, T0.BaseAbs, T0.DocDate, T0.CardName, T0.LineTotal, T0.DocTotal1 FROM Query_Result T0
ORDER BY  T0.BaseAbs, T0.DocEntry

SELECT T0.BaseAbs,T0.DocEntry, T0.DocDate, T0.CardName, T0.DocTotal1,
T0.LineTotal+(SELECT T0.DocTotal1-SUM(LineTotal) FROM  Query_Result WHERE DocEntry <=T0.DocEntry AND BaseAbs = T0.BaseAbs) [DocTotal], 
T0.LineTotal, (SELECT T0.DocTotal1-SUM(LineTotal) FROM  Query_Result WHERE DocEntry <=T0.DocEntry AND BaseAbs = T0.BaseAbs) [Running total]
FROM Query_Result T0
ORDER BY  T0.BaseAbs, T0.DocEntry

Former Member
0 Kudos

Hi Suda,

The result are working now, I never anticipated such situation! Thats what im missing from where statement, Thank you very much for unending support.

Godbless!

Regards

Clint

Answers (2)

Answers (2)

former_member583013
Active Contributor
0 Kudos

Would you be able to share your query here. There may be options to do it within the query iteself.

Try this sample for a concept of what I mentioned above..

SELECT T0.DocNum AS 'Document Number', T0.DocDate AS 'Posting Date', T0.DocTotal AS 'Document Total', 
(SELECT SUM(DocTotal) FROM OINV WHERE DocNum <= T0.DocNum) [Running Total]
 FROM  [dbo].[OINV] T0

Former Member
0 Kudos

Hi Suda,

Heres my query, Although I said Gordon that I can set it manually but if you have better suggestion your much appreciated.

SELECT

T0.DocEntry,

T0.DocDate,

T0.CardName,

T0.DocTotal,

SUM(T1.LineTotal) AS LineTotal,

T2.BaseAbs,

T2.DrawnSum,

T3.DocTotal as DocTotal1

T3.DocTotal - T2.DrawnSum AS Bal,

'RunningTotal' = (select MIN(T3.DocTotal) - MIN(T2.DrawnSum) from dbo.PCH9 T2

FROM

dbo.OPCH T0

INNER JOIN dbo.PCH1 T1 ON (T0.DocEntry = T1.DocEntry)

INNER JOIN dbo.PCH9 T2 ON (T1.DocEntry = T2.DocEntry)

INNER JOIN dbo.ODPO T3 ON (T2.BaseAbs = T3.DocEntry)

GROUP BY

T0.DocEntry,

T0.DocDate,

T0.CardName,

T0.DocTotal,

T2.BaseAbs,

T2.DrawnSum,

T3.DocTotal,

T3.DocEntry

Thank you.

Regards,

Clint

Edited by: clint pow on Oct 9, 2008 3:38 AM

former_member583013
Active Contributor
0 Kudos

Try this

SELECT  T0.DocEntry, T0.DocDate,  T0.CardName,  T0.DocTotal, SUM(T1.LineTotal) AS LineTotal, T2.BaseAbs, 
T2.DrawnSum, T3.DocTotal as DocTotal1, T3.DocTotal - T2.DrawnSum AS Bal, 
(SELECT T8.DocTotal - T9.DrawnSum FROM dbo.PCH9 T9 INNER JOIN dbo.ODPO T8 ON T9.BaseAbs = T8.DocEntry 
WHERE T9.DocEntry = T0.DocEntry) [Running]
FROM dbo.OPCH T0
INNER JOIN dbo.PCH1 T1 ON (T0.DocEntry = T1.DocEntry)
INNER JOIN dbo.PCH9 T2 ON (T1.DocEntry = T2.DocEntry)
INNER JOIN dbo.ODPO T3 ON (T2.BaseAbs = T3.DocEntry)
GROUP BY T0.DocEntry, T0.DocDate,  T0.CardName, T0.DocTotal, T2.BaseAbs, 
T2.DrawnSum, T3.DocTotal, T3.DocEntry
ORDER BY T0.DocEntry

Former Member
0 Kudos

Hi Suda,

The above query has an error, "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

This is the error.

Thank you very much.

Regards,

Clint

former_member583013
Active Contributor
0 Kudos

I do not have the right data to test the query so syntactically the query was correct but I know where the issue is.

I have changed the sub query part. Please take a look.

SELECT  T0.DocEntry, T0.DocDate,  T0.CardName,  T0.DocTotal, SUM(T1.LineTotal) AS LineTotal, T2.BaseAbs, 
T2.DrawnSum, T3.DocTotal as DocTotal1, T3.DocTotal - T2.DrawnSum AS Bal, 
(SELECT SUM(T8.DocTotal - T9.DrawnSum) FROM dbo.PCH9 T9 INNER JOIN dbo.ODPO T8 ON T9.BaseAbs = T8.DocEntry 
WHERE T9.DocEntry = T0.DocEntry) [Running]
FROM dbo.OPCH T0
INNER JOIN dbo.PCH1 T1 ON (T0.DocEntry = T1.DocEntry)
INNER JOIN dbo.PCH9 T2 ON (T1.DocEntry = T2.DocEntry)
INNER JOIN dbo.ODPO T3 ON (T2.BaseAbs = T3.DocEntry)
GROUP BY T0.DocEntry, T0.DocDate,  T0.CardName, T0.DocTotal, T2.BaseAbs, 
T2.DrawnSum, T3.DocTotal, T3.DocEntry
ORDER BY T0.DocEntry

Former Member
0 Kudos

Hi Suda,

this is the data for you to refer :

CREATE TABLE (

INT,

DATETIME,

NVARCHAR(100),

NUMERIC(15,2),

NUMERIC(15,2),

INT,

NUMERIC(15,2),

NUMERIC(15,2),

NUMERIC(15,2),

NUMERIC(15,2)

)

GO

/* Data for the `Query_Result` table (Records 1 - 😎 */

INSERT INTO (, , , , , , , , , )

VALUES (1575, '20080110', N'SAADA PASIGAN', N'0', N'165308.83', 135, N'165308.83', N'500000', N'334691.17', N'334691.17')

GO

INSERT INTO (, , , , , , , , , )

VALUES (1592, '20080111', N'SAADA PASIGAN', N'0', N'79563.6', 135, N'79563.6', N'500000', N'420436.4', N'420436.4')

GO

INSERT INTO (, , , , , , , , , )

VALUES (1599, '20080111', N'SAADA PASIGAN', N'0', N'53102.95', 135, N'53102.95', N'500000', N'446897.05', N'446897.05')

GO

INSERT INTO (, , , , , , , , , )

VALUES (1604, '20080111', N'SAADA PASIGAN', N'0', N'38415.6', 135, N'38415.6', N'500000', N'461584.4', N'461584.4')

GO

It compute for the difference of the DocTotal1 and Drawsum, that is correct but I want to have a running diffrence betwenn MIN(Drawsum) only.

Example from the above data :

The total Cash Advance is 500000 only. The Drawsum will deduct the 500000 until it become zero. Refer Below :

Doctotal DrawSum The Remaining Total of 500000

500000 165308.83 334691.17

the remaining amt shall be carry on which is the 334691.17 and not the 500000.

DocTotal    DrawSum    The Remaining Total of 500000

334691.17    79563.6       255127.57

255127.57     53102.95     202024.62

202024.62     38415.6       163609.2

and so onn...

Hope that you got me.

Thank you.

Clint

Former Member
0 Kudos

Hi Clint,

As far as I know, it is not possible. You have to use some other tools like Crystal Report.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Its been 5 days from know thinking how to Monitor the Cash Advances and I have an idea to get it,

First Total all the Drawsum Column in PCH9 table then deduct or subtract the Cash Advance original amount. Im thinking of searching a running difference query but it seems to hard hard for me.

Thank you Gordon.

Regards,

Clint