I need to do recurring totals in SQL Server. I want a stored procedure where I can pass in a parent ID I will then return a total amount for all the children (and children of children) associated with that original ID.
Even I have so far
object_id IF ('tempdb .. # average') No NULL start drop table #Average end table #Average (ID INT primary key cluster identification (1,1), name VARCHAR (255), ParentID integer, value iNT) INSERT INTO #Average (name, ParentID, value) values ('Fred', zero, 1), #Average Name, ParentID, value) values (zero in the 'Bats', 1) INSERT INTO # Average (name, ParentID, value) (SELECT 'Wynand', ID, # to where average #name = 'Fred') # in average Enter (name, ParentID, value) (SELECT 'D Ewald ', ID, 27 FROM # average where name =' Fred ') INSERT INTO #Average (name, ParentID, value) (select' Katelynn ', ID, 1 #Average name =' Dewald ') Where to put #Average (name , ParentID, from value) (# Bio 'Select, ID, 28 to # average where name =' bets') # Enter in the average (name, parent ID, value) (select 'luon', id, 4th from) # Name as a name = 'jacks') # Enter in the average (name, parent ID, value) (Select 'Reuben', id, # to # average where name = 'jacks'); With Private AS (SELECT N = 1, ID, Name, ParentID, #Available Value from ParentID Zero Unions Select All N 1, Av.ID, Av.Name, Av.ParentID, Av.Value #Average Av Inner Let's select P.ID = Av.ParentID) from the individual P. Name, SM (value) as individually as total; Name = 3 group by name
One way to get what you want here However, this is slightly different from the perspective given to you:
create a table # ancestors (ID int, name VARCHAR (255), ParentID integer, AncestryCompleteTF tinyint, ancestor varchar (max), TotalValue integer) in the #Ancestors Selection ID, Name, ParentID, Case IsNull (ParentID, 0) when the 0 and 0 end changes (vARCHAR, IsNull INSERT (parent ID, '')), the average of the expense is the value (choose SELECT * FROM #Ancestors WHERE ANces TryCompleteTF = 0) Start the update C. Set C.Ancestors = P.Ancestors + ',' + CONVERT (VARCHAR, P.ID), C.AncestryCompleteTF = 1, C, CTV, TotalValue = P TotalValue + C, CTV, #Ancestors Join #Ancestors from P On (CPIRIDID = PIID) and P. Encryption compositify = 1 ed select name, from Kulaval # organs Actually I am creating a temporary table, and use a little time to keep updating the yoga for those lines Where parents are already being calculated (since all these rows have been calculated, this is a total matter in case of adding total to the current row in the total row). The lines in which the PRIIRID is empty are being done to start them, so their direct lineage will be calculated first, and then the descendants of those lines etc.
Comments
Post a Comment