Case Select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Case Select

Author

Topic

dalikdik
Starting Member

2 Posts

Posted - 2010-10-04 : 07:23:23

SELECT P.OWNERNAME as Name, PROJECTNAME as [Programme Name], (CONVERT(VARCHAR(20), PROJECTSTART , 106 ) + ' - ' +CONVERT(VARCHAR(20), PROJECTEND , 106 ) ) AS Duration,
CASE WHEN FLAGS = 3 THEN 'COMPLETED' ELSE 'PENDING' END AS Step1,
CASE WHEN FLAGS = 4 THEN 'COMPLETED' ELSE 'PENDING' END AS Step2,
CASE WHEN FLAGS = 5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step3

FROM [TEST].[DBO].[DATA] C

WRONG RESULT:

Name Programme Duration Step1 Step2 Step3
name1 project 1 COMPLETED Pending Pending

name2 project 2 Pending Pending COMPLETED

name3 project 3 Pending COMPLETED Pending

CORRECT RESULT:

Name Programme Duration Step1 Step2 Step3

name1 project 1 COMPLETED Pending Pending

name2 project 2 COMPLETED COMPLETED COMPLETED

name3 project 3 COMPLETED COMPLETED Pending

My logic is wrong can anybody help me

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-04 : 07:28:46

Your result isn't wrong!
How should the result COMPLETED COMPLETED COMPLETED be generated?
Your column FLAGS then has to be 3 and 4 and 5 at the same time and that's not possible!

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-04 : 08:18:31

What i understand is that when Flag = 4 then it implicitly means that Flag 3 is also complete.

Something like
Loading -> Transport -> Unloading

If my understanding is correct then try the below statement:

SELECT P.OWNERNAME as Name, PROJECTNAME as [Programme Name], (CONVERT(VARCHAR(20), PROJECTSTART , 106 ) + ' - ' +CONVERT(VARCHAR(20), PROJECTEND , 106 ) ) AS Duration,
CASE WHEN FLAGS>=3 and FLAGS <=5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step1,
CASE WHEN FLAGS>=4 and FLAGS <=5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step2,
CASE WHEN FLAGS= 5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step3
FROM [TEST].[DBO].[DATA] C

-- I have not carried out any testing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 09:33:49

whats your rule for making a task as completed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dalikdik
Starting Member

2 Posts

Posted - 2010-10-04 : 10:10:48

quote:
Originally posted by pk_bohra

What i understand is that when Flag = 4 then it implicitly means that Flag 3 is also complete.

Something like
Loading -> Transport -> Unloading

If my understanding is correct then try the below statement:

SELECT P.OWNERNAME as Name, PROJECTNAME as [Programme Name], (CONVERT(VARCHAR(20), PROJECTSTART , 106 ) + ' - ' +CONVERT(VARCHAR(20), PROJECTEND , 106 ) ) AS Duration,
CASE WHEN FLAGS>=3 and FLAGS <=5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step1,
CASE WHEN FLAGS>=4 and FLAGS <=5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step2,
CASE WHEN FLAGS= 5 THEN 'COMPLETED' ELSE 'PENDING' END AS Step3
FROM [TEST].[DBO].[DATA] C

-- I have not carried out any testing

This is what im searching thanks a lot.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-04 : 10:30:59

You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources