Atul Thakor - All Comments

re: Performance problems loading XML with SSIS, an alternative way!

AtulThakor — Thu, 03 Feb 2011 12:27:50 GMT

between 1k and 6k

re: Performance problems loading XML with SSIS, an alternative way!

jagt70 — Thu, 03 Feb 2011 11:49:20 GMT

Hi,

what is the size of XML you are loading into the table?

Thanks

Jag

re: Multiple Phone Number Matching

GrumpyOldDBA — Thu, 28 Oct 2010 10:18:34 GMT

I use livewriter with a plugin which pastes visual studio code - it preserves formatting and colours correctly. Out of interest the annoying double spaces put in by office 2007 is resolved in office 2010.

You stand a much better chance of getting a post somewhere near how you want it with livewriter. I've not tried any other products

re: Multiple Phone Number Matching

AtulThakor — Wed, 27 Oct 2010 17:17:18 GMT

Thanks for the alternative solution eeStimpson, I like the second version where you can get the results on a single line.

The other thing.... you can run your version regardless of the sql version too!

re: Multiple Phone Number Matching

eeStimpson — Wed, 27 Oct 2010 15:18:12 GMT

or:

select

IDa = n1.ID

, PhoneNoa = coalesce

(

case when n1.PhoneNo1 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then n1.PhoneNo1 end

, case when n1.PhoneNo2 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then n1.PhoneNo2 end

, case when n1.PhoneNo3 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then n1.PhoneNo3 end

)

, PhoneTypea = coalesce

(

case when n1.PhoneNo1 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then 'PhoneNo1' end

, case when n1.PhoneNo2 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then 'PhoneNo2' end

, case when n1.PhoneNo3 in (n2.PhoneNo1, n2.PhoneNo2, n2.PhoneNo3) then 'PhoneNo3' end

)

, IDb = n2.ID

, PhoneNob = coalesce

(

case when n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo1 end

, case when n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo2 end

, case when n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo3 end

)

, PhoneTypeb = coalesce

(

case when n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo1' end

, case when n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo2' end

, case when n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo3' end

)

from

Numbers n1

join Numbers n2 on

n2.ID > n1.ID

and

(

n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

)

re: Multiple Phone Number Matching

eeStimpson — Wed, 27 Oct 2010 15:10:06 GMT

select

ID = n2.ID

, PhoneNo = coalesce

(

case when n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo1 end

, case when n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo2 end

, case when n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then n2.PhoneNo3 end

)

, PhoneType = coalesce

(

case when n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo1' end

, case when n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo2' end

, case when n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3) then 'PhoneNo3' end

)

from

Numbers n1

join Numbers n2 on

n2.ID != n1.ID

and

(

n2.PhoneNo1 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

n2.PhoneNo2 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

n2.PhoneNo3 in (n1.PhoneNo1, n1.PhoneNo2, n1.PhoneNo3)

)