Solved

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

Posted on 2013-01-18
5
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

717 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