Friday, March 23, 2012

Help with Select statement

I need help creating a select statement that will show users who have
attempted to fill out a form more than once and have not completed it
at least once.
Here is the basic table layout.
ID int PK
PersonID int
Completed bit
Date DateTime
The data looks like this
ID Person ID Completed Date
1 101 True 5/1/06
2 102 True 5/2/06
3 103 False 5/3/06
4 104 True 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
9 106 True 5/4/06
10 104 True 5/4/06
I need to select all users who have attempted to fill out the form
multiple times and have failed to complete the form(a False in the
Completed column) at least one of those times. So for the above data
I would want the results of the select to be
ID Person ID Completed Date
3 103 False 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
Can anyone help me with this.
Thanksselect ID,
PersonID,
Completed,
Date
from mytable
where PersonID in (
select PersonID
from mytable
group by PersonID
having count(*)>1
and min(cast(Completed as int))=0)|||It worked great. Thank You.

No comments:

Post a Comment