Need help with SELECT 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-11-15 : 14:49:48

Hi,

Give the business rule below, how can I return desire results below. If there is a combination Yes/No for a particular LoanId then return
0 otherwise return 1. SQL 2008

Thank you for your help in advance.

IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
DROP TABLE #LoanInfo
GO

CREATE TABLE #LoanInfo
(
LoanId INT NULL,
Response VARCHAR(5) NULL
)
GO

INSERT #LoanInfo VALUES (123456, 'NO'), (123456, 'Yes'), (123456, 'NO'), (456123, 'NO'), (456123, 'NO');
go

SELECT *
FROM #LoanInfo;
GO

-- Business rules: 1) Return 1 only all the response answer NO for a particular LoanId else return 0

-- Desire results:

LoanId ReturnStatus
------ -------------
123456 0 -- There is Yes
456123 1

--Testing.

SELECT CASE
WHEN Response = 'No' THEN 1
ELSE 0
END, *
FROM #LoanInfo
--WHERE LoanId= 123456
--AND LoanModAnswerID = 2314
ORDER BY 1 DESC
GO

SELECT Response
FROM #LoanInfo
WHERE LoanId = 123456
GROUP BY Response
HAVING COUNT(DISTINCT Response) >= 1
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-15 : 15:33:31

Not clear which you want but this should handle both cases:


--only 'NO' reponses
select LoanID
       ,case when min(response) = 'NO' and count(distinct Response) = 1 then 1 else 0 end 
from   #loanInfo
group by loanid

--only 1 distinct response
select LoanID
       ,case when count(distinct Response) = 1 then 1 else 0 end 
from   #loanInfo
group by loanid

Be One with the Optimizer
TG

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-11-15 : 16:11:01

TG,
Thanks so much for your help. Now, I try to understand your queries.

LN

quote:

Originally posted by TG

Not clear which you want but this should handle both cases:


--only 'NO' reponses
select LoanID
       ,case when min(response) = 'NO' and count(distinct Response) = 1 then 1 else 0 end 
from   #loanInfo
group by loanid

--only 1 distinct response
select LoanID
       ,case when count(distinct Response) = 1 then 1 else 0 end 
from   #loanInfo
group by loanid

Be One with the Optimizer
TG

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-11-16 : 14:30:18

Although TG's answer works and this probably doesn't matter, here is a solution for "only 'NO' responses" that is marginally faster (less aggregates, no COUNT distinct) and cleaner (IMHO):

SELECT LoanID
,MIN(CASE response WHEN 'NO' THEN 1 ELSE 0 END) AS ReturnStatus
FROM #loanInfo
GROUP BY loanid

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-18 : 11:47:10

SELECT loan_id, 'no'
FROM LoanInfo
GROUP BY loan_id
HAVING MAX(something_response) = MIN(something_response)
AND MAX(something_response) = 'NO';

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources