mister.magoo (4/9/2013)
Excellent, glad you got it working.I would like to say I understood what you are doing with it and that it looked like a good solution, but I don't, so instead I'll just say congratulations on solving your own problem and thanks for sharing your solution 🙂
😀
i've attempted to create some test data to show what i was doing!
the main jist, is that i only want to bring back the first instance of the Code being entered, based on the Date Recieved.
so although there are multiple records for the same CLient, who have code id 76 filled, i only bring back 'T' which is the one completed on 2013-03-02 (the first instance)
declare @clientid int = 1234;
with cte_table1(DateReceived,[state],OriginalSchema,active) as (
select '2013-04-01','Y', cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="1" /><code id="76">O</code></Codes></Form>' as xml),1
union all
select '2013-03-02','Y', cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>1</AssessmentNumber></Header><Codes><Code id="1" /><code id="76">T</code></Codes></Form>'as xml),1
union all
select '2013-01-01','Y',cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>2</AssessmentNumber></Header><Codes><Code id="2" /><code id="7">R</code></Codes></Form>'as xml),1
union all
select '2013-02-27','Y',cast('<Form><Header><ClientId>1234</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="3" /><code id="167">S</code></Codes></Form>'as xml),1
union all
select '2012-12-12','Y',cast('<Form><Header><ClientId>3214</ClientId><AssessmentNumber>0</AssessmentNumber></Header><Codes><Code id="300" /><code id="76">Z</code></Codes></Form>'as xml),1
)
select IndividualCode,RecNum,codeTagPosition,DateReceived from (
select *
, ROW_NUMBER() over (partition by codeTagPosition order by DateReceived,codeTagPosition) as myRowNum
from (
select
j.value('@id','integer') as codeTagPosition
, j.value('(./text())[1]','char(1)') as IndividualCode
, OriginalSchema.value('Form[1]/Header[1]/AssessmentNumber[1]','INT') as RecNum
, xm.DateReceived
from
cte_table1 xm
cross apply xm.originalschema.nodes('Form/Codes/code') x(j)
where
xm.active = 1
and xm.state = 'Y'
and OriginalSchema.value('Form[1]/Header[1]/ClientId[1]','INT') = @ClientId
)t
where
not t.IndividualCode is null
)t2
where t2.myRowNum = 1