CODEWITHSUNDEEP
sql-serverstored-procedures
user213029
user213029

Reputation:

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

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.:

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

Upvotes: 2

Views: 711

Answers (4)

DForck42
DForck42

Reputation: 20347

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.

Upvotes: 0

Andomar
Andomar

Reputation: 238076

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.

Upvotes: 1

tster
tster

Reputation: 18237

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425361

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

Upvotes: 2

Related Questions