You probably want a case sensitive collation as well

Select ID, COUNT(DISTINCT NOTES COLLATE Latin1_General_100_BIN2) ADDPr

from ...

You can also use nodes

SELECT x1.r1.value('@ActName','NVARCHAR(50)') AS ActName,

x2.r2.value('@Name','NVARCHAR(10)') AS Name,

x2.r2.value('@Value','NVARCHAR(10)') AS Value

FROM @XML.nodes('/InputColl/WorkItemInputInfo') AS x1(r1)

CROSS APPLY...

Interesting stuff, thanks!

Here's another to try

WITH XMLNAMESPACES ( 'http://www.w3.org/2001/XMLSchema' AS dev )

SELECT

a.c.value('local-name(.)', 'VARCHAR(100)') Id,

a.c.value('@Number', 'INT') [Number],

a2.c2.value('@Name', 'VARCHAR(100)') Visitor,

a2.c2.value('@Location', 'VARCHAR(100)') Location

FROM XmlTable AS X

CROSS APPLY X.Xmldocument.nodes('dev:Doc/dev:Functions/dev:A1') a(c)

CROSS APPLY a.c.nodes('dev:Visitors/dev:Visitor') a2(c2);

DECLARE @table1 TABLE(component VARCHAR(10), bin INT, prd INT)

INSERT INTO @table1(component, bin, prd)

VALUES('2.1' , 1, 217),

('6.5' , 4, 217),

('7.1' ,3, 217),

('7.6' ,5, 217),

('7.7' ,5, 217),

('1.3' ,2, 217),

('1.1' ,1, 298),

('3.1' ,1, 298),

('6.2'...

Without sample data it's not easy to tell, however, you're using ROW_NUMBER which can be non-deterministic and can give inconsistent results.

Try using RANK or DENSE_RANK instead of ROW_NUMBER

DECLARE @x XML ='<MatchKeys>

<fuzzyKeys>

<key key1="MatchKey_PostOut" key2="MatchKey_Name1" />

<key key1="MatchKey_Name1" key2="MatchKey_PhoneticStreet" key3="MatchKey_PhoneticCompanyName"/>

...

SELECT Receivers FROM myTable

UNION

SELECT Payers FROM myTable

dwain.c (1/20/2013)

---

Mark - I'm not sure why you used two window functions:

WITH SourceRecordset AS (

SELECT [index], workcode, value

FROM (

VALUES(1, 'CL', 5),

(2, 'CL',...

Using the technique here http://www.sqlservercentral.com/articles/T-SQL/71550/

WITH SourceRecordset AS (

SELECT [index], workcode, value

FROM (

VALUES(1, 'CL', 5),

(2, 'CL', 10),

(3, 'CL', 10),

...

Change

CONSTRAINT fk_Contact13_Sid FOREIGN KEY(ContactID) REFERENCES Contact12(ContactID)

to

CONSTRAINT fk_Contact13_Sid FOREIGN KEY(ContactID) REFERENCES Contact12(ContactID) ON UPDATE CASCADE

Change

IF OBJECT_ID('ClientInfoCollection') IS NOT NULL

to

IF EXISTS(SELECT * FROM sys.xml_schema_collections WHERE name='ClientInfoCollection')

Have a look here

DECLARE @t TABLE(ID INT, Name VARCHAR(10), Mode VARCHAR(10))

INSERT INTO @t(ID,Name,Mode)

VALUES(1, 'AAAAAAA','Phone'),

(2, 'AAAAAAA','Phone'),

(3, 'AAAAAAA','Phone'),

(4, 'BBBBB','SMS'),

(5, 'BBBBB','SMS'),

(6, 'CCCCC','Email'),

(7, 'AAAAAAA','SMS');

SELECT Name,

SUM(CASE WHEN Mode='Phone' THEN 1 ELSE...

Without DDL and sample data, this is just a guess

select Table1.NameField,count(Table2.LinkField) as recordcount

from Table1

left outer join Table2 on Table1.LinkField = Table2.LinkField

and Table2.OtherField = 'Somthing'

...