I want my query to produce one single answer

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 2008 Forums

Transact-SQL (2008)

I want my query to produce one single answer

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-27 : 11:12:16

I have this query I've been working with that produces results like the following:

Pass?
-----
Yes
-----
No

What I need though is single answer, if one or two of the two answers produces a "no" then the final conclusion would be "no". And only if the two results produces yes's then the final would be a "yes"

Thanks in advance!

SELECT        CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND 
                         CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END AS Pass
FROM            TST INNER JOIN
                         CTL ON TST.PT = CTL.PT
WHERE        (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 11:38:53

;WITH cte1
AS
(
SELECT CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND
CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END AS Pass
FROM TST INNER JOIN
CTL ON TST.PT = CTL.PT
WHERE (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')
)
SELECT MAX(Pass)
FROM cte1;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-27 : 11:39:38

Here is one way:

SELECT TOP 1 
	Pass
FROM
	(
		SELECT        CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND 
								 CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END AS Pass
		FROM            TST INNER JOIN
								 CTL ON TST.PT = CTL.PT
		WHERE        (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')
	) AS T
ORDER BY
	Pass DESC

EDIT: Sould have been ASC not DESC

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-27 : 12:43:03

Thanks so much for responding, and excuse me for not being clear, except both suggestions offered are not producing a final "no" (in fact both produce a yes?) when one of the two results returns a "no". Basically, I need to flag all the students who failed either one or both tests. thanks again

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-08-27 : 12:53:09

Create a temporary table that has the ID plus a column for pass YES/NO. Then query this table to find min value. I might use a cte.

djj

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 13:01:17

Ooops, just change the MAX to MIN:

;WITH cte1
AS
(
SELECT CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND 
CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END AS Pass
FROM TST INNER JOIN
CTL ON TST.PT = CTL.PT
WHERE (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')
)
SELECT MIN(Pass)
FROM cte1;

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-27 : 13:12:20

No real need to mess with a CTE:



SELECT        MIN(
              CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' 
                   WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' 
                   ELSE 'No' 
              END) AS Pass
FROM            TST INNER JOIN
                         CTL ON TST.PT = CTL.PT
WHERE        (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 13:24:41

Scott, the performance is identical. The CTE could be argued as being easier to support/maintain as it separates the aggregation from the logic. It does add a few more characters though.

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-08-27 : 13:31:02

Thanks guys both solutions work!! Much appreciated!

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 07:19:49

SELECT
CASE WHEN

P.Kameswara rao

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 07:19:50

SELECT
CASE WHEN

P.Kameswara rao

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-08-28 : 07:22:56

SELECT
ROW_NUMBER () OVER (PARTITOIN BY 1 ORDER BY SS) AS 'Row Number',
CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes'
WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND CTL.ID = 'CAHSEE' THEN 'Yes'
ELSE 'No'
END) AS Pass
FROM TST INNER JOIN
CTL ON TST.PT = CTL.PT
WHERE (CTL.NM LIKE '%- total') AND (TST.PID = @PID) AND (TST.ID = 'CAHSEE')
AND [Row Number] = 1

P.Kameswara rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources