Another way...

WITH ns(n) AS (

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL

SELECT 7 UNION ALL...

More of a conjecture I think

Suggest you look at EXCEPT (and maybe INTERSECT) in BOL

CrazyMan (6/5/2009)

---

Thanks Guys

I cant really get the numbers table, can you please throw some light...

robert_daniel (6/4/2009)

---

I am developing a view that will eventually be used as the source for a nightly extraction to a flat file. This flat file requires that each field...

Using a numbers/tally table

--Pre-populate with characters of length > 1

DECLARE @Lengths TABLE(Ch CHAR(1) PRIMARY KEY, Length INT)

INSERT INTO @Lengths(Ch,Length)

SELECT 'A',2 UNION ALL

SELECT 'E',2

Declare @Text1 Varchar(max)

Set @Text1 ='Test Message'

SELECT SUM(COALESCE(l.Length,1))

FROM Numbers...

lxz20 (6/4/2009)

---

Yep, NodeIDs are repeated here as examples to show infinite loops in 0, 1, or 2 steps. These loops are actually easier to catch and removed. There...

lxz20 (6/4/2009)

---

Old Hand,

Your 2nd CTE still blew up with the following sample data:

insert into NodeItem

select 1 NodeID, -1 ParentNodeID -- 1st root

union all select 2 , NULL -- the 2nd...

Jackal (6/4/2009)

---

Hi all,

I was just looking for some alternative means of acheiving something;

Lets assume we have a customer table, each customer has a unique id and additional data.

We have procedure...

Mark (6/4/2009)

---

lxz20 (6/4/2009)

---

Old Hand,

Your CTE will not work in my instance because Root Nodes may not have ParentNodeID = 0. As I said, there might be many root nodes....

lxz20 (6/4/2009)

---

Old Hand,

Your CTE will not work in my instance because Root Nodes may not have ParentNodeID = 0. As I said, there might be many root nodes. ...

This should give you all of the invalid nodes

WITH HierarchyTree (NodeID, ParentNodeID, Step) AS

(

SELECT NodeID, ParentNodeID, 1

FROM NodeItem

WHERE ParentNodeID = 0

UNION ALL

SELECT r.NodeID, r.ParentNodeID, t.Step + 1...

See if this helps

DECLARE @d1 DATETIME

DECLARE @d2 DATETIME

SELECT @d1='20090602 10:23', @d2='20090602 14:24'

SELECT DATEDIFF(minute,@d1,@d2)/60 AS Hours,DATEDIFF(minute,@d1,@d2)%60 AS Minutes

Suggest you try both approaches, in my experience "count(*) over()" doesn't perform particularly well.

order by replace(MyColumn 1,'(','')