Multiple conditions in case statement

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

Multiple conditions in case statement

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2009-12-03 : 11:02:44

Hi All,

Can i give multiple conditons in one statement ? Actually in my SQL i have two columns like

Provider AltProvider
P1
P1 NP
P1 P1

I want to count only where Provider is P1 and AltProvider is empty. I don't know why following SQL is not returning correct result. When I give only one condition i get correct value from the table.

[Code]
Count(Case When Provider Like '%P1%' and AltProvider <> '' Then A)
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:31:41

not sure what you are trying to achieve here. Did you want something like this:


DECLARE @foo TABLE (
	[provider] VARCHAR(2)
	, [altProvider] VARCHAR(2)
	)

INSERT @foo ([provider], [altProvider])
      SELECT 'P1', ''
UNION SELECT 'P1', 'NP'
UNION SELECT 'P1', 'P1'

SELECT * FROM @foo

SELECT
	f.[provider]
	, f.[altProvider]
	, CASE
		WHEN (f.[provider] LIKE '%P1%' AND f.[altProvider] <> '') THEN 'A'
		ELSE NULL
	END AS [check]
FROM
	@foo f

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shahid09
Starting Member

35 Posts

Posted - 2009-12-03 : 11:41:58

I want to count total providers where provider is P1 and AltProvider is empty from the above 2 columns.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 11:44:25

Assuming that AltProvider is NULL if not populated. If it is the empty string then replace the IS NULL check with = '' maybe this:


SELECT
    COUNT(*)
FROM
    <your_table>
WHERE
    Provider = 'P1'
    AND AltProvider IS NULL

If that's not it can you post the table structure or query that you are using to generate the sample data.

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shahid09
Starting Member

35 Posts

Posted - 2009-12-03 : 12:07:26

Thanks Charlie,
Here is my queury


SELECT F.Requirements
, Count(F.TS_NAME)AS TotalTestCases
, COUNT(CASE WHEN TS_USER_18  Like '%P1%' And TS_USER_11 not Like '' Then TS_USER_18  END) AS P1
, COUNT(CASE WHEN TS_USER_18  Like '%P2%' Then TS_USER_18  END) AS P2
, COUNT(CASE WHEN TS_USER_18  Like '%NP%' Then TS_USER_18  END) AS NP
, COUNT(CASE WHEN TS_USER_11  <> '' Then TS_USER_11  END) AS AltProvider
From  (
	
SELECT Distinct TEST.TS_NAME,
(
SELECT TESTCYCL.TC_STATUS from TESTCYCL where TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID and TESTCYCL.TC_CYCLE_ID = 
(Select max(TESTCYCL.TC_CYCLE_ID) from TESTCYCL WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID)) AS STATUS
,TEST.TS_USER_24 AS TC_VALIDATED, Requirements =
Case
   WHEN RQ_REQ_PATH Like 'AAAAADAARAADAAC%' Then 'Medi'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAN%' THEN 'MHS'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAPAAB%' THEN 'OB90'
Else
   'Unknown'
END, TEST.TS_USER_18, TEST.TS_USER_11, TEST.TS_USER_14, TEST.TS_USER_22
FROM  TEST,  REQ_COVER, REQ
WHERE TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID AND REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
) F
Group by F.Requirements

shahid09
Starting Member

35 Posts

Posted - 2009-12-03 : 12:08:37

Table F has two columns

TS_USER_18 TS_USER_11
P1
P1 NP
P2 P1
P1

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 12:18:01

Maybe this?


SELECT F.Requirements
, Count(F.TS_NAME)AS TotalTestCases
, COUNT(CASE WHEN TS_USER_18  Like '%P1%' And TS_USER_11 <> '' Then TS_USER_18  END) AS P1
, COUNT(CASE WHEN TS_USER_18  Like '%P2%' Then TS_USER_18  END) AS P2
, COUNT(CASE WHEN TS_USER_18  Like '%NP%' Then TS_USER_18  END) AS NP
, COUNT(CASE WHEN TS_USER_11  <> '' Then TS_USER_11  END) AS AltProvider
From  (
	
SELECT Distinct TEST.TS_NAME,
(
SELECT TESTCYCL.TC_STATUS from TESTCYCL where TEST.TS_TEST_ID = TESTCYCL.TC_TEST_ID and TESTCYCL.TC_CYCLE_ID = 
(Select max(TESTCYCL.TC_CYCLE_ID) from TESTCYCL WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID)) AS STATUS
,TEST.TS_USER_24 AS TC_VALIDATED, Requirements =
Case
   WHEN RQ_REQ_PATH Like 'AAAAADAARAADAAC%' Then 'Medi'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAN%' THEN 'MHS'
   WHEN RQ_REQ_PATH Like 'AAAAADAALAAPAAB%' THEN 'OB90'
Else
   'Unknown'
END, TEST.TS_USER_18, TEST.TS_USER_11, TEST.TS_USER_14, TEST.TS_USER_22
FROM  TEST,  REQ_COVER, REQ
WHERE TEST.TS_TEST_ID = REQ_COVER.RC_ENTITY_ID AND REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
) F
Group by F.Requirements

shahid09
Starting Member

35 Posts

Posted - 2009-12-03 : 12:19:40

I tried with <> also, but I am getting unexpected result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources