Find out if field contains 1's, 0's, or both...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find out if field contains 1's, 0's, or both...

Author

Topic

bgeveritt
Starting Member

2 Posts

Posted - 2009-12-03 : 16:06:49

Hi everyone,

I have a table with a field that contains either 1's, 0's, or both 1's and 0's for a particular key value. If the fields are all just 1's, then I need to return "Completed", if all 0's, then return "Not Started" and if it contains both 1's and 0's, I need to return "In Progress"

I'm trying to figure out out to do this without the use of a cursor, anyone have any ideas? If a cursor is the only method, I'd appreciate some help with that too. Thanks!

bgeveritt
Starting Member

2 Posts

Posted - 2009-12-03 : 16:23:42

Think I figured it out, anyone want to let me know if this logic is not sound:

SELECT
CASE
WHEN COUNT(DISTINCT [FIELDNAME]) = 1 AND SUM(DISTINCT [FIELDNAME]) = 1 THEN 'Completed'
WHEN COUNT(DISTINCT [FIELDNAME]) = 1 AND SUM(DISTINCT [FIELDNAME]) = 0 THEN 'Not Started'
WHEN COUNT(DISTINCT [FIELDNAME]) = 2 THEN 'In Progress'
END
FROM [TABLENAME]
WHERE [FIELDKEY] = 1

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-03 : 16:29:35

You need to show us what some sample data looks like

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-04 : 07:20:19

This?

declare @TABLENAME table (FIELDKEY int not null, FIELDNAME int not null)
insert @TABLENAME
          select 1, 1
union all select 1, 1
union all select 2, 0
union all select 2, 0
union all select 3, 0
union all select 3, 1

select FIELDKEY,
    CASE SUM(FIELDNAME)
        WHEN 0 THEN 'Not Started'
        WHEN COUNT(*) THEN 'Completed'
        ELSE 'In Progress'
    END as Status
from @TABLENAME
group by FIELDKEY

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:33:23

To expand it

select FIELDKEY,
    CASE 
		when min(FIELDNAME)=max(FIELDNAME) and max(FIELDNAME)=1 then 'Completed'
		when min(FIELDNAME)=max(FIELDNAME) and max(FIELDNAME)=0 then 'Not Started'
        ELSE 'In Progress'
    END as Status
from @TABLENAME
group by FIELDKEY

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources