The issue is the

CONVERT(VARCHAR,DATEADD(DAY,2, @fec),112),

part of your GROUP BY statement.

Since there is no column reference, it'll throw the error you see.

You should get the expected result set result by either...

Craig Farrell (3/2/2011)

---

...

By Jove! You've forgotten about taking care of your SOX while you're at it! In dealing with a rainforest, you'll catch some nasty things...

Mark Derryberry (3/1/2011)

---

Over 50% from my initial attempts! I was pushing about 6 hours and the methods you all proposed and some additional things I had to do given...

Glad I could help 😀

I used the MASTERID/RELATEDID comparison as shown above...

I'd start with listing the installations/DB's I'm aware of and try to identify the data owner.

Then I'd try to categorize the DB's: owner, type of usage (maintenance, dev, prod, reporting,...

I added a check for MASTERID>RELATEDID rsp. MASTERID<RELATEDID.

Since this returned duplicate values, I also had to change UNION ALL to UNION.

WITH

related_CTE

(RelationshipTableID,

MASTERID,

RELATEDID,

RELATIONSHIP) AS

(

SELECT RelationshipTableID, MasterID, RelatedID, Relationship

FROM RelationshipTable

WHERE (RelationshipTable.MasterID = 502)...

Sorry for missing the requirement to search the family tree up and down for various members somewhere in the middle of the structure. 😉

There might be easier solutions available but...

The issue is caused by your table design:

Since the same information is inserted twice you're forcing a referential loop.

Example:

insert into RelationshipTable (MasterID,RelatedID,Relationship) values (200,201,'Parent')

insert into RelationshipTable (MasterID,RelatedID,Relationship) values (201,200,'Child')

insert into...

Each of the options I posted in my previous post will meet that requirement.

Maybe you should show us some sample...

Please clarify the contradiction between...

Please post table def and sample data in a ready to use format as described in the first link in my signature. Also, please include your expected result basd on...

daveriya,

this is the 3rd or 4th post regarding your homework / school project.

Did you ever consider the option that those questions are there to verify what you've learned instead of...

Another part of your homework assignment?

Since SQL doesn't have arrays, this section most probably is about a frontend dev.

In case your tutor is teaching you that arrays are part of...

Something like this?

SELECT 123 * 1000 + number + 1

FROM master..spt_values

WHERE TYPE='P' AND number < 999

Yes, it's possible:

Option 1:

Find the logic that will cause an error and exclude those rows before you perform the insert.

Option 2:

Since it's a staging table, you could remove the...