Update Parent Table With Summation Of Its Child Records
May 24, 2013
I am trying to update a parent table with a summation of its child records. The child records are being deleted because the transaction has become invalid because payment was made with a bad check or there was a posting error. So a rollback of sorts is required.
Here are is the DDL for the tables and DML for the data:
Code:
DECLARE @t1 TABLE
(
[Year] int NOT NULL,
[Parcel] varchar(13) NOT NULL,
[InterestDateTime] datetime NULL,
[Principal] decimal(12, 2) NULL,
[Penalty] decimal(12, 2) NULL,
[Code] ....
I tried to use a Merge statement with an ON MATCH for each TransType, but it complained that I could not have multiple update statements. OK. So I tried a MERGE with single update statement with a case and it complained that I was updating the same parent multiple times, which I was and want to! So, I tried the following update statement and it still does not work, though no error message.
Code:
update t1 set
t1.Principal = t1.Principal + (case when t2.TransType = 'R' then t2.Payment else 0 end),
t1.Penalty = t1.Penalty + (case when t2.TransType = 'P' then t2.Payment else 0 end),
t1.Interest = t1.Interest + (case when t2.TransType = 'I' then t2.Payment else 0 end)
from @t1 t1
inner join @t2 t2 on t2.YEAR = t1.YEAR and t2.Parcel = t1.Parcel
so if i give input say categoryid=1[This falls under main category-boxing] i need to get result as 1 boxing [main category] 4 mayweather [sub category] 5 tyson [sub category] 6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson] result should be as 1 boxing [main category] 5 tyson [sub category]
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
declare @table table ( ParentID INT, ChildID INT, Value float ) INSERT INTO @table SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS ( SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table. 2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
We have a complex functionality of migrating data from a single record into multiple parent child tables.
To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.
Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.
As of now, we are using the following approach for achieving this functionality.
1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.
2. Use the recordset as the source to a foreachloop , using foreachADO enumerator
3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table
In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
Hi,I have a stored procedure that has to extract the child records forparticular parent records.The issue is that in some cases I do not want to extract all the childrecords only a certain number of them.Firstly I identify all the parent records that have the requird numberof child records and insert them into the result table.insert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select t1.BatchNumber,t1.EntryRecordID,t1.LN,t1.AdditionalQualCritPassedfrom(select BatchNumber,RecordType,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Temp) as t1inner join(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) = max(TotalNumbers)) as t2on t1.BatchNumber = t2.BatchNumberand t1.RecordType = t2.RecordTypeand t1.EntryRecordID = t2.EntryRecordID)then insert the remaining records into a temp table where the number ofrecords required does not equal the total number of child records, andthenloop through each record manipulating the ROWNUMBER to only selectthe number of child records needed.insert into @t_QualificationMismatchedAllocs([BatchNumber],[RecordType],[EntryRecordID],[AssignedNumbers],[TotalNumbers])(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) <max(TotalNumbers))SELECT @QualificationMismatched_RowCnt = 1SELECT @MaxQualificationMismatched = (select count(*) from@t_QualificationMismatchedAllocs)while @QualificationMismatched_RowCnt <= @MaxQualificationMismatchedbegin--## Get Prize Draw to extract numbers forselect @RecordType = RecordType,@EntryRecordID = EntryRecordID,@AssignedNumbers = AssignedNumbers,@TotalNumbers = TotalNumbersfrom @t_QualificationMismatchedAllocswhere QualMismatchedAllocsRowNum = @QualificationMismatched_RowCntSET ROWCOUNT @TotalNumbersinsert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select BatchNumber,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Tempwhere RecordType = @RecordTypeand EntryRecordID = @EntryRecordID)SET @QualificationMismatched_RowCnt =QualificationMismatched_RowCnt + 1SET ROWCOUNT 0endIs there a better methodology for doing this .....Is the use of a table variable here incorrect ?Should I be using a temporary table or indexed table if there are alarge number of parent records where the child records required doesnot match the total number of child records ?
I have a set of parent/child records that need to be exported to a space delimited Flat File. Each parent record must be followed by 3 child records, each on their own line with different format.
I have a prototype using the Derived Column component that concatinates the various fields of each record into one "wide" text column. This fools SSIS to think that each row has the same format. Then I merge them together using an artificial sort id. But this seems overly tedious and very brittle.
What would be the best approach to writing these records out? I'm hoping there is a better more maintainable method.
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT Cols FROM Table Names FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
Thanks for your time, How to calculate & save a Parent status [qcStatus varchar(30)] and Alert [alertFlag bit] in dbo.a1_qcParent based on comparison of its Child records in dbo.a3_qcItems2Fix columns [itemComplete bit] and [alertFlag bit] Where a1_qcParent[a1_id] = a3_qcItems2Fix[a1_ID]
- Parent CLOSED: if all children [itemComplete] are True - Parent OPEN: if any child [itemComplete] is False
- Parent ALERT: True if any child row [alertFlag bit] is True
Using sql_Datasource in webpage, but more comfortable in sql... After-Trigger? Can Parent columns have calculated formula referencing the child table? Please help.
Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.
Category ID Category Name Category E-mail
1 Beverages Beverages.com
2 Condiments Condiments.com
3 Confections Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.
Category ID Product Name Quantity Per Unit
1 Chang 24 - 12 oz bottles
1 Côte de Blaye 12 - 75 cl bottles
1 Ipoh Coffee 16 - 500 g tins
1 Outback Lager 24 - 355 ml bottles
2 Aniseed Syrup 12 - 550 ml bottles
2 Chef Anton's Gumbo Mix 36 boxes
2 Louisiana Hot Spiced Okra 24 - 8 oz jars
2 Northwoods Cranberry Sauce 12 - 12 oz jars
3 Chocolade 10 pkgs.
3 Gumbär Gummibärchen 100 - 250 g bags
3 Maxilaku 24 - 50 g pkgs.
3 Scottish Longbreads 10 boxes x 8 pieces
3 Sir Rodney's Scones 24 pkgs. x 4 pieces
3 Tarte au sucre 48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1), find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1
2 Northwoods Cranberry Sauce 12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record? Any Help or sample URL, Tutorial Page will be appreciated. Thanks
Below is my sample data of my table named "Groups"
Code: with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup ) select * from groups;
Expected result:
Code: with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all select 'Microsoft' as GroupName,'Nokia' as SubGroup union all select '' as GroupName,'EShop' as SubGroup union all select 'IBM' as GroupName,'Meiosys' as SubGroup union all select '' as GroupName,'UrbanCode' as SubGroup ) select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code: SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.[Parent ID] FROM People_tbl RIGHT OUTER JOIN Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID] WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
hi, i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this:
SELECT * FROM pipeline INNER JOIN pipelineStatus ON pipeline.id = pipelineStatus.parentID
i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table? (stored procedure ok)
Below is my sample data of my table named "Groups"
with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
[Code] ....
Expected result:
with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all
[Code] ....
How to achieve this parent-child has the same table.
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
insert into Hier select 'subramanium','Manickam' union all select 'subramanium','Munuswamy' union all select 'Munuswamy','senthil' union all select 'Munuswamy','sasi' union all select 'Munuswamy','uma' union all select 'manickam','vijay' union all select 'manickam','bhavani' union all select 'manickam','dhanam' union all select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier where parent not in(select Child from Hier) and parent <> 'subramanium' Getting resultset as: parent ====== uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
I have a table called "College". In a table, I have to create a structure for multilevel parent-child relationship
For Example,
1) State has number of colleges, Number of colleges has Number of dept. , Number of dept. has no. of subjects, no. of subject has number of chapters and the hierarchy goes on.
Expected Output is, College 1 Dept 1 subject 1 subject 2 subject 3 Dept 2 Dept 3
[Code] ....
I tried in so many ways, I do not know how to query in single table.
I have two table both say A and B.If i insert a record in A that record should be inserted in B.If i delete a record in A that record should be deleted from B.Is that possible.If yes please tell me.Thankyou in advance,vishnu
Hi, I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
Request ID Parent ID Account Name Addresss 1452 1254789 Wendy's Atlanta Georgia 1453 1254789 Wendy's Norcross Georgia 1456 1254789 Waffle House Atlanta Georgia
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
