Can I do it only with SQL Queries or do I need a stored procedure?

Question

Hi guys.

I came across with a problem in which I need some advice/help.

Background: I'm programming a map in an accounting software which requires special formatting and a tree-like hierarchy between the account codes, i.e.:

• Account 1 is the father of accounts 11, 12 and 13;
• On the other hand, account 11 is the father of accounts 111, 112 and 113;
• There can also be an account relationship like this one: account 12 is the father of account 12111, because there is no 121 nor 1211, which means that the hierarchy is not a "one digit plus hierarchy" if I make my self clear;
• And this logic continues till the end of the map.

Database table background: There are 3 tables involved in all process.

1. A general ledger table (named ContasPoc) which I can relate with table number 2 (general ledger entries accounts) by each account code, in other words, the account code in table 1 is equal to the account code in table 2 (this field is named CodigoConta in both tables).
2. The general ledger entries accounts (named LancamentosContas) which I can relate with table number 1 (general ledger) as I said. This table is also related with table number 3 (general ledger entries) by an Id.
3. Table number 3 is as I said general ledger entries (named Lancamentos) and the field name that relates this table with number 2 is IdLancamento. I need this table for filtering purposes.

A real example: I want all the accounts from 24 till 26 that have values/movements but I also want their parents. Moreover, their parents will have to contain the sums of their children accounts just like the example output bellow:

Account Code | Debit Sum | Credit Sum | Balance  /*Balance = Debit Sum - Credit Sum*/
24           | 12,000    | 184,000    | -172,000 /*Sum of all children (242+243)   */
242          | 12,000    | 48,000     | -36,000  /*Sum of all children (2423+2424) */
2423         | 12,000    | 0,000      |  12,000  /*Sum of all children (24231)     */
24231        | 0,000     | 0,000      |  12,000  /*Account with values/movements   */
2424         | 0,000     | 48,000     | -48,000  /*Sum of all children (24241)     */
24241        | 0,000     | 48,000     | -48,000  /*Account with values/movements   */
243          | 0,000     | 136,000    | -136,000 /*Sum of all children (2433)      */
2433         | 0,000     | 136,000    | -136,000 /*Sum of all children (24331)     */
24331        | 0,000     | 136,000    | -136,000 /*Sum of all children (243313)    */
243313       | 0,000     | 136,000    | -136,000 /*Account with values/movements   */

As you can see there are lots of accounts but only the ones that have the following comment /*Account with values/movements*/ have values, the others are calculated by me, which brings me to the real problem:

Can I do it? Of course, but at the moment I'm using recursive methods and several queries to do it and this approach takes a awful lot of time so I definitely need a new one because most of the customers use old computers.

I've tried to get this kind of structure/data using a query with INNER JOINS. Didn't work because I don't get all the data I need, only the lines with movements.

I also and tried with LEFT JOINS. Didn't work so well as well because I get all the data I need plus a lot of which I don't need. But using a LEFT JOIN has one more problem, I only get this extra data (also the one I need) if I do not include any fields from table number 3 in the WHERE clause. This obviously has an answer but because I'm not such an expert in SQL Server I'm not seeing what is the reason for that.

Here are the query I built (the other one has only 1 difference, a LEFT JOIN instead of the first INNER JOIN):

SELECT IdConta, Min(IdContaPai) AS IdContaPai, Min(ContasPoc.CodigoConta) AS CodigoConta
FROM ContasPoc INNER JOIN (LancamentosContas INNER JOIN Lancamentos ON 
Lancamentos.IdLancamento = LancamentosContas.IdLancamento) ON ContasPoc.CodigoConta = 
LancamentosContas.CodigoConta
WHERE Lancamentos.IdEmpresa=17 AND ContasPoc.IdEmpresa=17 AND Lancamentos.IdLancamento = 
LancamentosContas.IdLancamento AND ContasPoc.CodigoConta>='24' AND ContasPoc.CodigoConta<='26'
GROUP BY IdConta 
ORDER BY Min(ContasPoc.CodigoConta)

After this long explanation (sorry about that) my 3 questions are:

1. Is it possible to get such results (as I demonstrated above) only with database queries, in other words, without using recursive methods?
2. If not (most probable) are stored procedures the way to go and if so how can I do it (never used them before)?
3. Is there another method I'm not seeing?

Can someone help me on this one?

I hope I got all the relevant data in, but if not please tell me what I forgot and I will gladly answer back. Thanks in advance! Miguel

Answer 1

WITH    q (AccountCode, Balance) AS (
        SELECT  24231, 12000
        UNION ALL
        SELECT  243313, -136000
        UNION ALL
        SELECT  24241, -48000
        UNION ALL
        SELECT  24, NULL
        UNION ALL
        SELECT  242, NULL
        UNION ALL
        SELECT  2423, NULL
        )
SELECT  qp.AccountCode, SUM(qc.Balance)
FROM    q qp
JOIN    q qc
ON      SUBSTRING(CAST(qc.AccountCode AS VARCHAR), 1, LEN(qp.AccountCode)) = qp.AccountCode
GROUP BY
        qp.AccountCode
ORDER BY
        AccountCode

Answer 2

You never "need" stored procedures. Think of it this way, a stored procedure is just code with DB queries inside it which runs entirely on the server. You could always do that code in your client language and have the DB queries return result sets which you operate on on the client side.

Answer 3

Unless I'm mistaken, this becomes easy if you think of Account Code as a string. You could query like:

select *,
    (select sum(vw2.DebitSum)
     from SomeView vw2
     where vw.code < vw2.code and 
           vw2.code < cast(cast(vw.code as int) + 1 as varchar)
    ) as DebitSum
from SomeView vw
where '24' <= vw.code and vw.code < '27'

The subquery sums the debits for all children.

Answer 4

i don't understand why you don't just have an account table with an id for the primary key, and for every child account have a parent account field. then you just reference back to the account table and get your hierarchy in a looping cte.