Terminology Question - Set Of Related Records In Multiple Tables
Jul 20, 2005
Terminology question:
Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.
I'm thinking the term might be graph, but I'm not at all certain of this.
I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code: DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
Hi, I have 3 tables: Employees with the fields:idEmployee and employeeName Roles with the fields:idRole and roleName. An employee can have one or many roles. I created the table EmployeeRoles with the fields: id,idEmployee,idRole. idEmployee and idRole are foreign keys. I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields. Index_key = which is the patient common link (text) ur_episode = this wil change for each Hospital (text) Admission_datetime = patient admission date and time (datetime) Discharge_datetime = patient discharge date and time (datetime)
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code: INSERT INTO dbTarget.dbo.tblTarget (All fields) SELECT (All Fields) FROM dbSource.dbo.tblSource WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer b) tblCreditors c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID  CustomerName   VoucherType   Outbound   Inbound   CustomerType ---------------------------------------------------------------------------------------------- 1   ABC                Sales         10000        0          Dealer 2   MNC               Sales          9000        0          Dealer 3   MNC               Sales          4000        0          Dealer
Table 2 - Â tblCreditors
ID  Name   OpeningBalance ---------------------------------------------------------------------------------------------- 1   ABC      20000  2   MNC     15000 3   XBM     18000 4   XYZ      12000
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as UserID=1, FName = John, LName=Abraham and Industry = 2. I have a Industry table with data as ID=1 and Name= Sports, ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
Hi I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it. Thanks
Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6
This is closely related to this problem where Ryan & Peter helped me out loads (thanks again guys): http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989
I now have a slightly different problem. Instead of returning the cumulative counts of people related to a business unit in the hierarchy I now need to return the actual people records. I had some code, derived from the above, that worked fine until I loaded some new data. It took a while but eventually I figured out the (now obvious) reason for the error. I have tried several changes to the query and not got anywhere. Please could someone have a look and see if you can suggest the required alteration(s)?
USE tempdb go
--------------------------------------------------------------- --Set up the structure & data... --------------------------------------------------------------- CREATE TABLE dbo.org ( unit_codeVARCHAR(10)NOT NULL , parent_codeVARCHAR(10)NULL , CONSTRAINT pk_orgPRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) ) GO
CREATE VIEW dbo.org_peeps_parents --WITH SCHEMABINDING AS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROMdbo.org LEFT OUTER JOIN dbo.org_peeps ONorg_peeps.unit_code= org.unit_code GO
INSERT INTO dbo.org (unit_code, parent_code) SELECT 'a', 'y'UNION ALL SELECT 'b', 'y'UNION ALL SELECT 'c', 'z'UNION ALL SELECT 'y', 'z'UNION ALL SELECT 'z', NULL
--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only) INSERT INTO dbo.org_peeps (unit_code, person_code) SELECT 'a', 1UNION ALL SELECT 'c', 3
--------------------------------------------------------------- SELECT'This works great....' --------------------------------------------------------------- ;WITHmaterialised_paths AS ( SELECTunit_code , parent_code , person_code , unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents WHEREparent_code IS NULL UNION ALL SELECTall_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ONmp.unit_code= all_people.parent_code )
SELECTparents.unit_code , children.person_code , children.unit_path FROMmaterialised_paths AS children CROSS APPLY--Correlated derived table - get the child records per unit ( SELECTunit_code , parent_code FROMdbo.orgAS parents_sub WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parents WHEREchildren.person_code IS NOT NULL ORDER BY person_code , unit_code
--Add data to INTERMEDIATE levels of organisation INSERT INTO dbo.org_peeps (unit_code, person_code) SELECT 'y', 4UNION ALL SELECT 'y', 5UNION ALL SELECT 'y', 6
--------------------------------------------------------------- SELECT'Since there is now data in the intermediate tables there are ' + 'duplicate outputs....' --------------------------------------------------------------- ;WITHmaterialised_paths AS ( SELECTunit_code , parent_code , person_code , unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents WHEREparent_code IS NULL UNION ALL SELECTall_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ONmp.unit_code= all_people.parent_code ) SELECTparents.unit_code , children.person_code , children.unit_path FROMmaterialised_paths AS children CROSS APPLY--Correlated derived table - get the child records per unit ( SELECTunit_code , parent_code FROMdbo.orgAS parents_sub WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parents WHEREchildren.person_code IS NOT NULL ORDER BY person_code , unit_code
--------------------------------------------------------------- SELECT'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this' --------------------------------------------------------------- ;WITHmaterialised_paths AS ( SELECTunit_code , parent_code , person_code , unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents WHEREparent_code IS NULL UNION ALL SELECTall_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROMdbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ONmp.unit_code= all_people.parent_code ) SELECTDISTINCT parents.unit_code , children.person_code , children.unit_path FROMmaterialised_paths AS children CROSS APPLY--Correlated derived table - get the child records per unit ( SELECTunit_code , parent_code FROMdbo.orgAS parents_sub WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parents WHEREchildren.person_code IS NOT NULL ORDER BY person_code , unit_code
--Clean up IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parents END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peeps END IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.org ENDJust paste and run the code and hopefully it should be reasonably clear. If you need any further info please let me know
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table. Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted? How can I achieve this?
Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!
SQL Server 2005. Schema is not fixed at this stage - small project, schema can be adapted if neccessary. This needs to be produced in SQL Server - there is no client application.
Hi
I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.
I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".
There is a further table with a related FK declared which associates people with business units.
Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).
I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here: http://www.dbforums.com/showthread.php?t=1629366
To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.
Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ): DECLARE @table TABLE ( unit_codeTINYINT , parent_codeTINYINT , headcountINT , PRIMARY KEY CLUSTERED (unit_code) )
INSERT INTO @table (unit_code, parent_code, headcount) SELECT 1, 4, 10 UNION ALL SELECT 2, 4, 130 UNION ALL SELECT 3, 10, 93 UNION ALL SELECT 4, 10, 7 UNION ALL SELECT 10, NULL, 1
I need to find the total StatusDateTime for each TicketId I need to find the average StatusDateTime for all TicketIDs Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
This is an easy one, I hope. Terminology has changed over the years, and I'm not sure what the current nomenclature is with regards to ... I have a table contains State names and State codes. I have another that contains Product names and Product codes. I have another that contains Collateral type names, and Collateral type codes. At one time, I heard these referred to as domain tables, but I don't believe that was a correct label today. We do want to differentiate these tables from other tables that might refer to them. Other tables might be transaction tables, or represent data that will eventually be purged. However, Collateral types are somewhat static in a business environment. So are the names and number of states. Likewise with Countries. Even Products are somewhat static, and certain ProductTypes (or category) might be static.
So what is the name that refers to these types of data tables, as opposed to the more transactional data?
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
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.
