ChrisM@home (1/16/2012)

---

SQLKnowItAll (1/16/2012)

---

bicky1980 (1/16/2012)

---

SQLKnowItAll (1/16/2012)

---

You have this:

insert into test2

values ('18','0014','01234567125','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

Then this is 1 unique landline?

You have this:

insert into test2

values ('18','0014','01234567126','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

This is 1 unique landline?

You...

SQLKnowItAll (1/16/2012)

---

bicky1980 (1/16/2012)

---

SQLKnowItAll (1/16/2012)

---

You have this:

insert into test2

values ('18','0014','01234567125','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

Then this is 1 unique landline?

You have this:

insert into test2

values ('18','0014','01234567126','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

This is 1 unique landline?

You have...

SQLKnowItAll (1/16/2012)

---

You have this:

insert into test2

values ('18','0014','01234567125','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

Then this is 1 unique landline?

You have this:

insert into test2

values ('18','0014','01234567126','','9@test.co.uk')

insert into test2

values ('19','0014','01234567126','','9@test.co.uk')

This is 1 unique landline?

You have this:

insert into...

SQLKnowItAll (1/16/2012)

---

This statement contradicts your counts. This would...

ChrisM@home (1/16/2012)

---

Can you give this a whirl please Bicky, using a decent-sized dataset rather than the somewhat diminutive sample data set? Cheers.

DROP TABLE #test2

CREATE TABLE #test2 (indkey...

Mark-101232 (1/16/2012)

---

bicky1980 (1/16/2012)

---

Hi Everyone, Thanks for all your help with this query.

With ChrisM's help, I think I now have the solution:

CREATE TABLE #test2 (indkey NVARCHAR(2), ...

Hi Everyone, Thanks for all your help with this query.

With ChrisM's help, I think I now have the solution:

CREATE TABLE #test2 (indkey NVARCHAR(2), ...

ChrisM@home (1/13/2012)

---

bicky1980 (1/13/2012)

---

...

Thanks for the reply, if there were 2 duplicate datakeys with different landline values, I would only want this to be counted as one record, as I dont...

GSquared (1/13/2012)

---

The order of the rows in a dataset doesn't really matter to set-based SQL operations on it.

I just need a clarification on this statement, "Total number of populated Landline...

ChrisM@home (1/13/2012)

---

bicky1980 (1/13/2012)

---

Total number of populated Landline fields (Needs to have a unique datakey as well as be unique itself - If the same landline is anywhere else in the...

L' Eomot Inversé (1/13/2012)

---

That's quite clear as far as it goes, but it leaves some questions open. You seem to think that it's something that can be written as...

ChrisM@home (1/13/2012)

---

Hi Bicky

Some very experienced and talented folks are having real difficulty figuring out exactly what you want here, and it looks to me that you might still be figuring...

OK, Yes you are right my query does not work correctly...

I have edited the data slightly using the following:

create table test2(

indkey nvarchar(2),

datakey nvarchar(4),

landline nvarchar(11),

mobile nvarchar(11),

email nvarchar(20))

insert into test2

values ('1','0001','01234567890','0712345679','1@test.co.uk')

insert into...

Mark-101232 (1/13/2012)

---

<snip>

I'm not convinced these queries work at all even though they apparently give desired results.

This one

select count(distinct landline) as Unique_landlines

from (select *, row_number() over(partition by datakey order...

Mark-101232 (1/13/2012)

---

stephen99999 (1/12/2012)

---

I see your point now. In all actuality, your prior code:

select count(distinct datakey) as Unique_Dataset

from (select *, row_number() over(partition by datakey order by case when Datakey!='' then 0...