1

Topic: Monthly average residuals under the balance account of Oracle

All kind time of days. Faced here such task:
Monthly average residuals under the balance account.
Input data: an account mask, date with, date on.
Output data: an account number, the currency code, monthly average residual.
Sorting: monthly average residual on decrease.
The remark: at first obtaining of residuals to personal accounts for each calendar day of the period (13 digits), then mean value obtaining.

With subqueries meanwhile , I ask the help. At least on an example to look as should look .
Here that made that, but it in a root not how it is necessary in a condition.
SELECT
ACC.CODE "Account number", P_DIMENSION.GETDIMCODE (CURRENCY_ID) "Currency"
FROM
ACCOUNT ACC,
(SELECT-SUM (DEBET) AS DEBET
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010'
UNION
SELECT SUM (KREDIT) AS KREDIT
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010 ')
WHERE
ACC.CODE LIKE ' 3012200599 %'

2

Re: Monthly average residuals under the balance account of Oracle

Decided to transfer a subquery in , as a result deduces big minus value of residual.
SELECT
ACC.CODE "Account number", P_DIMENSION.GETDIMCODE (CURRENCY_ID) "Currency";
((SELECT-SUM (DEBET) AS DEBET
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010 ') +
(SELECT SUM (KREDIT) AS KREDIT
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010 ')) AS SALDO
FROM
ACCOUNT ACC
WHERE
ACC_TYPE_ID = P_ACCT.GETIDBYNAME (' Balance ') AND
ACC.CODE LIKE ' 3012200599 %'
select * from account where CODE LIKE ' 3012200599 %'

3

Re: Monthly average residuals under the balance account of Oracle

As check simply counted the totals under the debit and the credit, they identical under the given accounts, . the balance should be 0 (2).
In the request of balance resulted above it turns out-118330105739,22 and it identical to two accounts.
It is clear that there an error in the logician, but I try all variants.

4

Re: Monthly average residuals under the balance account of Oracle

Cursor777 wrote:

... But I try all variants.

Try to include a brain at first
Subqueries (which are not necessary) have no binding to accounts as which you want to count, and deduce the totals according to all available information tables ACCTURN.

5

Re: Monthly average residuals under the balance account of Oracle

MaximaXXL, added in subqueries a binding in a type ACCT.ACCOUNT_ID = ACC.ID , you meant it? Considers all the same not so.

6

Re: Monthly average residuals under the balance account of Oracle

Made without a subquery, like would consider correctly (checked the debit/credit and considered manually). In what a dirty trick?
SELECT
ACC.CODE AS "Account number", (SUM (ACCT.KREDIT) - SUM (ACCT.DEBET)) AS "Monthly average residual"
FROM
ACCOUNT ACC, ACCTURN ACCT
WHERE
ACC.ID = ACCT.ACCOUNT_ID AND
CODE LIKE ' 3012200 % ' AND
ACCT.DATE_TURN BETWEEN ' 31/01/2009 ' AND ' 31/01/2010'
GROUP BY ACC.CODE
ORDER BY 2 DESC;

7

Re: Monthly average residuals under the balance account of Oracle

Cursor777 wrote:

In what a dirty trick?

As well as in last subject - if under the account any turn it disappears from sampling yet was not same.
PS monthly average is not that you consider:
At first obtaining of residuals to personal accounts for each calendar day the period

8

Re: Monthly average residuals under the balance account of Oracle

j2k, it is possible to learn from you how it to write?

9

Re: Monthly average residuals under the balance account of Oracle

Cursor777;
Come into 408 office, we tell and we prompt.  you then?)

10

Re: Monthly average residuals under the balance account of Oracle

ramzestein, suspected a forum on thought push, that to distract nobody smile

11

Re: Monthly average residuals under the balance account of Oracle

Cursor777 wrote:

All kind time of days. Faced here such task:
Monthly average residuals under the balance account.
Input data: an account mask, date with, date on.
Output data: an account number, the currency code, monthly average residual.
Sorting: monthly average residual on decrease.
The remark: at first obtaining of residuals to personal accounts for each calendar day of the period (13 digits), then mean value obtaining.

With subqueries meanwhile , I ask the help. At least on an example to look as should look .
Here that made that, but it in a root not how it is necessary in a condition.
SELECT
ACC.CODE "Account number", P_DIMENSION.GETDIMCODE (CURRENCY_ID) "Currency"
FROM
ACCOUNT ACC,
(SELECT-SUM (DEBET) AS DEBET
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010'
UNION
SELECT SUM (KREDIT) AS KREDIT
FROM
ACCTURN ACC
WHERE
ACC.DATE_TURN BETWEEN ' 01/12/2009 ' AND ' 01/01/2010 ')
WHERE
ACC.CODE LIKE ' 3012200599 % '

At first it is necessary to you it will be defined that is stored in ACCTURN
Let's admit
1) CODE - an account number
2) DEBET - debit residual
3) KREDIT -  residual
4) DATE_TURN - date something, that?
-Residual on the beginning of day?
-Residual on the end of day?
-Residual on  day (in  days off) or when there was a turn?
After  start a spelling , for time all do not solve,
1) select the necessary accounts
2) receive residuals for the period (for each calendar day of the period (13 digits))
4) solve that means 13
5) count an average
6) sort
so
.....
stax

12

Re: Monthly average residuals under the balance account of Oracle

Stax;
1. Here to begin generally with residual obtaining as DEBET and KREDIT it is turns which to residual concern but no more.
2. For obtaining  residual, without having a field with residual on the end/beginning of day, not its most optimal idea  for each day, and then to divide on in days. There are other algorithms - faster.

13

Re: Monthly average residuals under the balance account of Oracle

MaximaXXL wrote:

Stax;
1. Here to begin generally with residual obtaining as DEBET and KREDIT it is turns which to residual concern but no more.
2. For obtaining  residual, without having a field with residual on the end/beginning of day, not its most optimal idea  for each day, and then to divide on in days. There are other algorithms - faster.

1) I consider that DEBET and KREDIT  residual on the account debit/kreditovyj,  that  a turn
But even if a turn it is necessary to count at first residuals during ACC.DATE_TURN <= ' 01/01/2010'
Then already to consider an average
2) certainly to consider  on a miscellaneous and  depends on model of storage (obtaining) of residual
If the period small ( there is more month) in days and accounts not million that is possible and on every day
....
stax

14

Re: Monthly average residuals under the balance account of Oracle

Stax, Hardly there millions records, it is very similar to the educational task smile

15

Re: Monthly average residuals under the balance account of Oracle

j2k;
I do not know, probably a lab
ACCOUNT - judging by a title  accounts, and it is necessary residuals
.....
stax

16

Re: Monthly average residuals under the balance account of Oracle

j2k wrote:

at first obtaining of residuals to personal accounts for each calendar day the period

Well it is possible, probably, and to weigh simply residuals by an interval, what for records  to produce...

17

Re: Monthly average residuals under the balance account of Oracle

andrey_anonymous wrote:

Well it is possible, probably, and to weigh simply residuals by an interval, what for records  to produce...

the Spherical horse can be weighed and so, and . The cubic mass all the same does not turn out.