Solved

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

Posted on 2013-01-18
5
438 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now