Query return a field base on the calculation 1

[kate8]

Hi All,

I need write a query that will return a filed which base on the calculation of the data. Here is how I did it, but I didn’t get what I want.

select Distinct t1.person_id, case when( sum(t1.Result)>0 ) then 'Y' else 'N' end AS 'Satus'
from
(
select Distinct person_id,
case when (t.code_id in ('123', '456', '789') and date_completed ='')
then 1
else 0 end AS 'Result'
from dbo.test_result t
)t1
GROUP By t1.person_id,t1.Result

Here is the query return for one particular person, which has three test, one meets condition, others not. I want it to return”id1 and Y”, but it return:

Person_id Status
Id1 N
Id1 Y

What I want is the query check each patient’s tests. if the test meet the condition, then set it to 1. If the patient has 3 test, one meet the codition which is 1, other two is 0, the sum for this patient’s test then is 1. His status should be ‘Y’. Now I got two records back, Y and N. I should have only one record back with status Y.
How can I make my query return one record per person.
Thank you for any helps and suggestions!!

 

[imex]

Hi,

Try:

select 
    person_id,
    case when sum( case when (code_id in ('123', '456', '789') and date_completed = '')
                       then 1 else 0 end ) > 0 then 'Y' else 'N' end as STATUS
from dbo.test_result 
GROUP By person_id

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[kate8]

imex,

Thank you so much!!! It works perfetly.