WITH dd(code, aname, bname, ROWNUMBER) as

(

select a.code, a.aname, b.bname, ROW_NUMBER() OVER(PARTITION BY a.code order BY a.code )

from @a as a

left outer join

Yes, very interesting point of view, but syntax is looks very complicated.

old style do the same and looks much clearer

DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))

INSERT into @Table

SELECT...

or you can do something really simple, like this

DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))

INSERT into @Table

SELECT 1234, 'I', 2.30 UNION ALL

SELECT 1234, 'J', 1.55 UNION ALL

SELECT 1234,...

declare @Tmp table (

[name] varchar(100),

[rows] int,

[reserved] varchar(20),

[data] varchar(20),

[index size] varchar(20),

[unused] varchar(20)

)

insert into @Tmp

exec sp_msforeachtable'

exec sp_spaceused ''?'''

select * from @Tmp

that means that you have a lot "bad" de dates in table. in reality difference probably will be more significant

yes sorry, my mistake. if you don't mind you can try this

select a.PersonID, a.Version, a.DEDate,

case when b.PersonID is null then 0 else 1 end AS [Is ImProper]

from PersonRecord...

that supposed to be faster, if clustered index applied on (PersonID, Version )

select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is ImProper]...

yes whatever,

select distinct a.PersonID,a.Version, a.DEDate , case when b.PersonID is null then 0 else 1 end AS [Is ImProper]

from PersonRecord a

left join (

select a.PersonID

from PersonRecord a

inner join PersonRecord...

Am I missing something ?

select a.PersonID,a.Version, a.DEDate , 1 AS [Is ImProper]

from PersonRecord a

inner join PersonRecord b on a.PersonID = b.PersonID and a.Version>b.Version and a.DEDate<b.DEDate

Nice article.

Just another way to do this in sql 2005

with h(SomeID) as (SELECT DISTINCT SomeID from dbo.TestData )

select h.SomeID, dbo.fnConcatTest(h.SomeID) from h