Need help in a query which return a field base on calculation

Question

Need help in a query which return a field base on calculation

kate8tt

SSC Enthusiast

Points: 158
More actions
July 13, 2012 at 12:49 pm

#263844

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_idStatus
Id1N
Id1Y
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!!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

You need to provide ddl, sample data and desired output. Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kate8tt SSC Enthusiast Points: 158 More actions · Answer 2

Hi,

Here is the sample of my data:

Table Name: test_result

Fields: person_id code_iddate_completed

Dataid1123

Id145601/21/2011

Id1666

Since the first record meets condition: code_id = ‘123’ and no completed date, then this patient’s status should be Y. No matter how many testes a person has, if one of them meets the condition, then the query should return one record with a ‘Y’ status. In this case, I expect my query returns:

Person_idStatus

id1Y

Now it returned:

Person_idStatus

id1Y

id1N

Hope i explain it clear.

Thank you!

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Well it is obvious you didn't bother to read that article or you would realize that what you posted is not helpful. The point of ddl and sample data is two fold. One it minimizes the effort for the volunteers (like myself). And Second it means we are all on the same page with table structures, datatypes etc. Without this we have to assume the datatype based on somewhat legible data you posted.

The other benefit is that if you show that you put in some effort to explain your problem you will have a lot more people willing to help. What you posted looks like you put little to no effort into it.

Here is an example of how you should post your information in the future. The query at the end produces the desired results based on your sample data.

create table #Test

(

Person_id varchar(10),

code_id int,

Date_Completed datetime

)

insert #Test

select 'Data', 123, null union all

select 'id1', 456, '1/21/2011' union all

select 'id1', 666, null

select Person_id, case when MAX(date_completed) IS NULL then 'Y' else 'N' end as Status

from #Test

group by Person_id

drop table #Test