• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

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
Crashman
Asked:
Crashman
  • 4
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
CrashmanAuthor Commented:
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
 
CrashmanAuthor Commented:
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
 
CrashmanAuthor Commented:
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
 
CrashmanAuthor Commented:
solved by my self
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now