Matt Miller (#4) (4/17/2012)

---

Mark Fitzgerald-331224 (4/17/2012)

---

Matt, agreed there should be no presumption of a single ultimate parent in a hierarchy.

Answering you questions

Is it:

- node(s) which have the longest lineage ->...

Matt, agreed there should be no presumption of a single ultimate parent in a hierarchy.

Answering you questions

Is it:

- node(s) which have the longest lineage -> in this case it does...

Matt,

This was not so much a problem of not having an out node (null parent) but more of creating closed loops within the structure.

Example:

Node 1-> Node 2-> Node 3->...

As requested by Jeff and FL here is a script that can find a closed loop in a hierarchy.

It would not be too efficient with a large data set. ...

Sorry Divya, Jill and Jim seems to have been confused in the code selected. (Jill during the inserts, Jim during the CTE).;-)

Fitz

Sorry Jeff, this was only a demonstration of the closed loop idea that may occur.;-)

"Oh... be really, really careful here. Checking for loops like that may look fine on paper...

Let me take a wild guess Inserted and Deleted are the table names. These are always available within triggers (and other code in SQL 2005 onwards) and allow you...

To hbtkp,

You probably don't want to here this but how about learning some SQL before asking increasingly frustrating questions.

Try this...

Introduction to SQL 2008

http://www.microsoft.com/learning/en/us/course.aspx?id=2778a

If your boss thinks you...

In the sample data given the recursive part ends when the selected parent (either 10 or 11) is not found. The chosen sample data does not have a loop...

If I understand what you require :

1) work out the number of passes (rules merit <= results merit)

2) work out the number of results possible

3) for each row in the...

If the datetime column only ever contains date if the time being 00:00:00.000 always then...

create table dbo.SampleData(

ID int primary key,

SampleName varchar(30),

...

Vews by default do not take up space (over the space used to store the definition) but can take up space if they are clustered indexed views.

create table tEmp(EmpID int,...

Two demonstrations as below.

DECLARE @EmploymentHistory TABLE (

CompanyName VARCHAR (100), EmployeeName VARCHAR (100),

EmploymentBeginDate DateTime, EmploymentEndDate DateTime)

INSERT @EmploymentHistory (CompanyName, EmployeeName, EmploymentBeginDate, EmploymentEndDate)

SELECT 'Jack and Jill Company', 'John Doe', '1/2/2001',...

It depends. To help us to help you it would be best to ask yourself and give us the answers to the following questions:

1) do you want this run...

The query required is listed below. I have included a query to show the result set but this is not required in the final code.

declare @tApplyProgram table (myID varchar(50),...