Can someone jump in here... seems like a simple problem made really complicated and I'm losing the will to live.

http://www.sqlservercentral.com/Forums/Topic1234021-1292-1.aspx

This won't include the encoding header

DECLARE @t TABLE( VARCHAR(20), value VARCHAR(20))

INSERT INTO @t(,value)

SELECT 'proyect','2901' UNION ALL

SELECT 'user','usrsession'

SELECT (

SELECT AS "@key",

...

Sorry, I still don't get it. Why only one landline value per datakey, surely you need to consider all landline values to count the number of distinct ones.

Here's a simple...

<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 by...

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 else...

bicky1980 (1/12/2012)

---

stephen99999 (1/12/2012)

---

in table format, bicky, please post what you are expecting your results to look like. Earlier you stated you wanted to see 9, 6, etc to be returned,...

Is this what you're after?

SELECT COUNT(*) AS Total,

COUNT(DISTINCT datakey) AS Uniquedatakey,

COUNT(DISTINCT CASE WHEN landline<>'' THEN landline...

Something like this?

WITH CTE AS (

SELECT [User],GWP,

ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY GWP DESC) AS rn

FROM MyTable)

SELECT [User],GWP

FROM CTE

WHERE rn=1;

Ninja's_RGR'us (1/10/2012)

---

Gianluca Sartori (1/10/2012)

---

Very annoying. I think the random words are meant to trick search engines.

I don't know which countermeasures can be taken, if any. I would be extremely upset...

Common table expressions (the 'WITH' bit) go at the start of the query.

WITH TempTable AS (

SELECT ConstraintBasedGroupId, Constraints

FROM ConstraintBasedGroupBase

),

ResourceGroupToTeam AS (

SELECT

TempTable.ConstraintBasedGroupId,

SUBSTRING(item,CHARINDEX('{',item)+1,CHARINDEX('}',item)-CHARINDEX('{',item)-1) AS TeamId

FROM TempTable

CROSS APPLY

DelimitedSplit8K(REPLACE(Constraints,'||','|'),'|') split

)

SELECT ...

SELECT t1.Name, t1.PDate, t1.Value, COALESCE(SUM(t2.Value),0) AS Value1

FROM #tTest t1

LEFT OUTER JOIN #tTest t2 ON t2.Name=t1.Name

...

Dev (12/20/2011)

---

GSquared (12/20/2011)

---

I'm assuming you're asking me, but I'm not clear on what you're asking. XML doesn't have columns, it has elements. Is that what you...

Change

SELECT *

FROM OPENXML (@idoc, 'NewDataSet/Sheet1', 1)

WITH ([Column1] [varchar](50),

[Column2] [varchar](50) )

to

SELECT *

FROM OPENXML (@idoc, 'NewDataSet/Sheet1', 1)

WITH ([Column1] [varchar](50) 'Column1',

[Column2] [varchar](50) 'Column2')

Interesting question, thanks!

Can you post the schema?