How to do it in SQL?

Question

Post reply

How to do it in SQL?

SqlUser-369680

Old Hand

Points: 384
More actions
March 29, 2010 at 11:19 pm

#222564

Guys,
Most of the time there are two/three record in the table.
Example: I have 2 record with the value below in 3 diff. scenario
Create table TableID
(
TabID varchar(10) PRIMARY KEY,
Status int,
Remarks varchar(1000)
)
My SQL is not correct, kindly enhance.
select tabid, GroupDesc = case when groupstatus = '1' or groupstatus = '0' then 'In Progress'
else 'Ok'
end
FROM TableID
Were Tabid = T1
IF you RUN the SELECT with the value/criteria below you should get
VALUE
IF Status = 1 or status = 0 THEN Desc = "In Progress"
TabID Status
T1 1
T1 0
RESULT:
TabID Desc
T1 In Progress
VALUE
If Status = 1 or status = 1 THEN Desc = "Ok"
TabID Status
T1 1
T1 1
RESULT:
TabID Desc
T1 Ok
VALUE:
If status = 0 or status = 0 THEN Desc = "failed"
TabID Status
T1 0
T1 0
RESULT:
TabID Desc
T1 failed
Select should give me 1 row only even if the record is more than 1
Best Regards

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Please post ur table structure, sample data...

Go thro the following article to see how to post them

http://www.sqlservercentral.com/articles/Best+Practices/61537/

SqlUser-369680 Old Hand Points: 384 More actions · Answer 2

SqlUser-369680

Old Hand

Points: 384

March 30, 2010 at 12:07 am

#1141964

Thanks I updated my post. 🙂

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

I appreciate your time for going thro the article and posting a table structure. Thanks 🙂

Whilst you were editing your post i was creating the table structure and sample data.

Table and Sample Data

IF OBJECT_ID('tempdb..#StatusReport') IS NOT NULL

DROP TABLE #StatusReport

CREATE TABLE #StatusReport

(

TabID varchar(8),

Status INT

)

INSERT INTO #StatusReport

SELECT 'T1', 1

UNION ALL

SELECT 'T1', 0

UNION ALL

SELECT 'T2', 0

UNION ALL

SELECT 'T2', 0

UNION ALL

SELECT 'T3', 1

UNION ALL

SELECT 'T3', 1

And here is the query you had asked for

SELECT TabID,

CASE SUM(Status)

WHEN 0 THEN 'FAILED'

WHEN 1 THEN 'InProcess'

ELSE 'OK'

END AS GroupDesc

FROM #StatusReport

WHERE TabID = 'T2' -- Pass your TabID value here

GROUP BY TabID

Please inform us if this is what u requested!!

Cheers,

C'est Pras!

SqlUser-369680 Old Hand Points: 384 More actions · Answer 4

Perfect..:-)

But I just received minor changes from our requester that there could be other codes in the future aside from 1 and 0, it will increased as the number of person in a group increase.

could be 2,3,4,5, etc..

The objective is to see the performance of the group. Either "Success" or "Failed"

Thanks