Solved

Recursive cte works great for sum but does not work for rest

Posted on 2013-01-18
5
441 Views
Last Modified: 2013-01-26
Hi,

I am using this structure
CUENTAS ------
IDCuenta             Nombre                               IDCuentaPadre
       1                    ACTIVO                           0  
      11                   CIRCULANTE                        1

SALDOS ---------
IDCuenta             Inicial        Entrada        Salida           SaldoAcumulado

Open in new window


When I save values in a child, this values must insert in the parent values, this is ok with this sql code
;
	WITH CTE
	AS (
	    SELECT cc.idempresa,
	           cc.Nombre,
	           ca.IDCuenta,
	           cc.IDCuentaPadre,
	           ca.Inicial,
	           ca.Entrada,
	           ca.Salida,
	           ca.Saldo,
	           ca.SaldoAcumulado
	    FROM   CON_SALDOS ca
	           INNER JOIN CON_CUENTASCONTABLES cc
	                ON  cc.IDEmpresa = ca.IDEmpresa
	                AND cc.IDCuenta = ca.IDCuenta
	    UNION ALL
	    SELECT cc.IDEmpresa,
	           cc.Nombre,
	           cc.IDCuenta,
	           cc.IDCuentaPadre,
	           H.Inicial,
	           H.Entrada,
	           H.Salida,
	           H.Saldo,
	           H.SaldoAcumulado
	    FROM   CON_CUENTASCONTABLES cc
	           INNER JOIN CTE H
	                ON  H.IDCuentaPadre = cc.IDCuenta
	),
	temporal
	AS (
	    SELECT c.idempresa,
	           c.Nombre,
	           c.IDCuenta,
	           SUM(Inicial)         AS Inicial,
	           SUM(Entrada)         AS Entrada,
	           SUM(Salida)          AS Salida,
	           SUM(Saldo)           AS Saldo,
	           SUM(SaldoAcumulado)  AS SaldoAcumulado
	    FROM   CTE                     c
	    WHERE  Inicial IS NOT NULL
	    GROUP BY
	           c.Nombre,
	           c.IDCuenta,
	           c.idempresa
	)
	UPDATE x
	SET    x.inicial = t.inicial,
	       x.entrada = t.entrada,
	       x.salida = t.salida,
	       x.saldo = t.saldo,
	       x.saldoacumulado = t.saldoacumulado
	FROM   CON_SALDOS x
	       INNER JOIN temporal t
	            ON  x.idempresa = t.idempresa
	            AND x.IDCuenta = t.IDCuenta

Open in new window


The problem is when the user update child with 0 this code does not update parents to 0
0
Comment
Question by:Crashman
  • 4
5 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 38795366
Hi Crashman,

My knowledge of Spanish is zero, so I can't follow your code very well, but I do understand recursive SQL quite well.  Maybe I can help anyway.

CTE appears to select your child records.  temporal creates totals for the child records.  Nowhere do you select the parent records for update.

My guess is that you'll want to sum all of the child values for every parent.  If you have only 1 level of parent/child, this is pretty easy.  You'll also want to sum the child records by parent.  The "cleanest" way is another sub-query that sums the results of temporal, grouped by idpadre.  UNION ALL of these results with temporal, then join these results in the final query where you join temporal.


Hope this makes sense.  If not, I'll be glad to explain further.

Kent
0
 
LVL 8

Author Comment

by:Crashman
ID: 38797524
Thanks kdo
attached is data example,
one line can have many parents.
if you need more details let me know.

;
	WITH CTE
	AS (

	           ca.IDAccount,
	           cc.IDParent,
	           ca.Initial,
	           ca.In,
	           ca.Out,
	           ca.Total,
	           ca.TotalAccumulated
	    FROM   CON_TotalS ca
	           INNER JOIN CATALOG cc
	                AND cc.IDAccount = ca.IDAccount
	    UNION ALL
	    SELECT cc.IDAccount,
	           cc.IDParent,
	           H.Initial,
	           H.In,
	           H.Out,
	           H.Total,
	           H.TotalAccumulated
	    FROM   CATALOG cc
	           INNER JOIN CTE H
	                ON  H.IDParent = cc.IDAccount
	),
	temporal
	AS (
	    	   c.IDAccount,
	           SUM(Initial)         AS Initial,
	           SUM(In)         AS In,
	           SUM(Out)          AS Out,
	           SUM(Total)           AS Total,
	           SUM(TotalAccumulated)  AS TotalAccumulated
	    FROM   CTE                     c
	    WHERE  Initial IS NOT NULL
	    GROUP BY
	            c.IDAccount
	)
	UPDATE x
	SET    x.Initial = t.Initial,
	       x.In = t.In,
	       x.Out = t.Out,
	       x.Total = t.Total,
	       x.TotalAccumulated = t.TotalAccumulated
	FROM   CON_TotalS x
	       INNER JOIN temporal t
	            ON x.IDAccount = t.IDAccount

Open in new window

Example.xls
0
 
LVL 8

Author Comment

by:Crashman
ID: 38801177
Update , complete example

DECLARE @tbl AS TABLE (
	IDaccount BIGINT,	NAME VARCHAR(10),IDParent INT 
)

DECLARE @tblD AS TABLE (
	IDaccount BIGINT, Initial FLOAT,[IN] FLOAT,[OUT] FLOAT, total FLOAT, TotalAccumulated FLOAT)

INSERT INTO @tbl (IDaccount, NAME, IDParent) values (1,'a',0)
INSERT INTO @tbl (IDaccount, NAME, IDParent) values (11,'a',1)
INSERT INTO @tbl (IDaccount, NAME, IDParent) values (1102,'a',11)
INSERT INTO @tbl (IDaccount, NAME, IDParent) values (1102005,'a',1102)
INSERT INTO @tbl (IDaccount, NAME, IDParent) values (1102005001,'a',1102005)

insert into @tblD (IDaccount, Initial, [IN], [OUT], total, TotalAccumulated) VALUES ( 1,	0,	0,	0	,0	,0)
insert into @tblD (IDaccount, Initial, [IN], [OUT], total, TotalAccumulated) VALUES ( 11	,0,	0,	0,	0,	0)
insert into @tblD (IDaccount, Initial, [IN], [OUT], total, TotalAccumulated) VALUES (1102,	0,	0,	0,	0,	0)
insert into @tblD (IDaccount, Initial, [IN], [OUT], total, TotalAccumulated) VALUES (1102005,	0,	0,	0,	0,	0)
insert into @tblD (IDaccount, Initial, [IN], [OUT], total, TotalAccumulated) VALUES (1102005001,	0,	31.07,	0,	31.07,	31.07)


SELECT * FROM @tblD

;WITH CTE
	AS (
	    SELECT ca.IDAccount,
	           cc.IDParent,
	           ca.Initial,
	           ca.[In],
	           ca.[Out],
	           ca.Total,
	           ca.TotalAccumulated
	    FROM   @tblD ca
	           INNER JOIN @tbl cc
	                ON cc.IDAccount = ca.IDAccount
	    UNION ALL
	    SELECT cc.IDAccount,
	           cc.IDParent,
	           H.Initial,
	           H.[In],
	           H.[Out],
	           H.Total,
	           H.TotalAccumulated
	    FROM   @tbl cc
	           INNER JOIN CTE H
	                ON  H.IDParent = cc.IDAccount
	),
	temporal
	AS (
	    SELECT c.IDAccount,
	           SUM(Initial)         AS Initial,
	           SUM([In])         AS [In],
	           SUM([Out])          AS [Out],
	           SUM(Total)           AS Total,
	           SUM(TotalAccumulated)  AS TotalAccumulated
	    FROM   CTE                     c
	    WHERE  Initial IS NOT NULL
	    GROUP BY
	           c.IDAccount
	)
	UPDATE x
	SET    x.Initial = t.Initial,
	       x.[IN] = t.[In],
	       x.[Out]= t.[Out],
	       x.Total = t.Total,
	       x.TotalAccumulated= t.TotalAccumulated
	FROM   @tblD x
	       INNER JOIN temporal t
				ON x.IDAccount = t.IDAccount

SELECT * FROM @tblD				

UPDATE @tblD SET Initial = 0,[IN] = 0, [OUT] = 0, total = 0, TotalAccumulated =0 
WHERE IDaccount = 1102005001;

;WITH CTE
	AS (
	    SELECT ca.IDAccount,
	           cc.IDParent,
	           ca.Initial,
	           ca.[In],
	           ca.[Out],
	           ca.Total,
	           ca.TotalAccumulated
	    FROM   @tblD ca
	           INNER JOIN @tbl cc
	                ON cc.IDAccount = ca.IDAccount
	    UNION ALL
	    SELECT cc.IDAccount,
	           cc.IDParent,
	           H.Initial,
	           H.[In],
	           H.[Out],
	           H.Total,
	           H.TotalAccumulated
	    FROM   @tbl cc
	           INNER JOIN CTE H
	                ON  H.IDParent = cc.IDAccount
	),
	temporal
	AS (
	    SELECT c.IDAccount,
	           SUM(Initial)         AS Initial,
	           SUM([In])         AS [In],
	           SUM([Out])          AS [Out],
	           SUM(Total)           AS Total,
	           SUM(TotalAccumulated)  AS TotalAccumulated
	    FROM   CTE                     c
	    WHERE  Initial IS NOT NULL
	    GROUP BY
	           c.IDAccount
	)
	UPDATE x
	SET    x.Initial = t.Initial,
	       x.[IN] = t.[In],
	       x.[Out]= t.[Out],
	       x.Total = t.Total,
	       x.TotalAccumulated= t.TotalAccumulated
	FROM   @tblD x
	       INNER JOIN temporal t
				ON x.IDAccount = t.IDAccount
				
				SELECT * FROM @tblD								

Open in new window

0
 
LVL 8

Accepted Solution

by:
Crashman earned 0 total points
ID: 38801553
fixed, what I do , is use a mark for parent account, is not used for details , update this parent to 0 and the CTE works correctly because only details are summed
0
 
LVL 8

Author Closing Comment

by:Crashman
ID: 38821798
solved by my self
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 40
Ssis not sending failure message 2 26
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1 2 25
sql 2008 how to table join 2 17
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question