Solved

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

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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