-- Use a full outer join

WITH CTE(ID,Kind,Need,Score) AS (

SELECT COALESCE(s.ID,n.ID),

COALESCE(s.Kind,n.Kind),

n.Need,s.Score

FROM

( -- Test Data amount scored

select ID...

Try this

WITH CTE AS (

SELECT p1_id,p2_id,

ROW_NUMBER() OVER(PARTITION BY p1_id ORDER BY p2_id) AS rn1,

ROW_NUMBER() OVER(PARTITION BY...

See if this helps

SELECT USER,

SUM(CASE WHEN (NOT (STATUS= '90R')) AND (NOT (STATUS= '99I')) THEN 1 ELSE 0 END) AS Col1,

...

Maybe this?

DECLARE @BDate DATETIME

DECLARE @EDate DATETIME

SET @BDate = '20080101'

SET @EDate = '20080401'

SELECT u.UserName,

...

declare @mytable table (

[Group] varchar(30),

Node varchar(30),

Parent varchar(30))

insert into @mytable

SELECT 'AAA','POS2122941','POS2065860' UNION ALL

SELECT 'BBB','POS2081642','POS2081641' UNION ALL

SELECT 'BBB','POS2084810','POS2114958' UNION ALL

SELECT 'BBB','POS2092572','POS2081642' UNION ALL

SELECT 'BBB','POS2089937','POS2114958' UNION ALL

SELECT 'BBB','POS1008747','POS2081642' UNION ALL

SELECT 'BBB','POS2114958','POS2081641' UNION ALL

SELECT...

Can you post some sample...

select a.[Group],a.Parent as Root

from mytable a

where not exists (select * from mytable b where b.[Group]=a.[Group] and a.Parent=b.Node)

virgo (4/21/2009)

---

thanks brother it works.

But can you please let me know how can i do the same with out using CTE feature of sql server , like may be...

Anye Mercy (4/21/2009)

---

Thanks again, this idea worked perfectly and gave me ideas for sprucing up some other processes as well. I had used CTEs for paging but not for...

Untested, but try this

with cte(ID,MsgCount,StartDate,EndDate) as (

select Table1ID,count(*),min(SomeDate),max(SomeDate)

from Table2

group by Table1ID)

update t1

set MsgCount=c.MsgCount, StartDate=c.StartDate, EndDate=c.EndDate

from Table1 t1

inner join cte c on t1.ID = c.ID

declare @id int

set @id=2;

with cte(id,lft,rgt) as (

select id,0,0

from user_registration_tbl

where id=@id

union all

select t.id,1-t.Node, t.Node

from user_registration_tbl t

inner join cte c on c.id = t.parentid)

select sum(lft) as lft,

...

Try this

declare @x xml

set @x='

...your XML here

'

SELECT

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:OpportunityId[1]', 'nvarchar(10)') AS OpportunityId,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:Created[1]', 'nvarchar(10)') AS Created,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:Modified[1]', 'nvarchar(10)') AS Modified,

x.y.value('declare namespace s="http://schemas.microsoft.it.relationshipmanagement/2007/siebel/opportunity"; s:AccountId[1]', 'nvarchar(10)') AS...

select city,

sum(case when code=251 then round(payment,2) else 0 end) as dectp,

sum(case when code=252 then round(payment,2) else...

SELECT Year,Zip,

SUM(CASE WHEN Groupp='A' THEN 1 ELSE 0 END) AS 'A',

SUM(CASE WHEN Groupp='B' THEN 1 ELSE 0 END) AS 'B'

FROM #Table1

GROUP BY Year,Zip

ORDER BY...

WITH CTE AS (

SELECT CUSTOMER_NO,

CURRENT_BALANCE,

ACTUAL_DATE,

ROW_NUMBER() OVER(PARTITION BY CUSTOMER_NO ORDER...