troubleshooting Question

SQL Case in WHERE clause

Avatar of pzozulka
pzozulka asked on
Microsoft SQL ServerSQL
4 Comments2 Solutions340 ViewsLast Modified:
Need help with the case statement below. Why is it not working, and what can be done to make it work:

My goal is to return records where QuoteLastModifiedUser is IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteLastModifiedUser)

If QuoteLastModifiedUser  is either NULL or Empty String, then return the record if QuoteCreator  is IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteCreator)

SELECT         cq.QuoteCreator,
        case when cq.QuoteCreator in (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteCreator) then 'T'
             else 'F' 
        end as QuoteCreatedByFullUser,
        cq.QuoteLastModifiedUser
             
        FROM  
        CustomerQuote cq
        INNER JOIN Entity a ON (cq.AgentId=a.PartyId) 
        LEFT OUTER JOIN	CustomerAccount c ON (cq.QuoteNumber = c.QuoteNumber)
        LEFT OUTER JOIN Entity s ON (cq.QuoteOwnerId=s.PartyId) 
        LEFT JOIN Entity sp ON s.[SalespersonId] = sp.[PartyId]
        
        WHERE cq.QuoteCreateDate between '10/25/2001' AND '10/25/2017'   And (cq.QuoteTypeId = '1' Or cq.QuoteTypeId = '3') 

		AND CASE WHEN cq.QuoteLastModifiedUser IS NOT NULL
					THEN cq.QuoteLastModifiedUser IN (select UserAccount.FullName from UserAccount where UserAccount.FullName = cq.QuoteLastModifiedUser)
			END
			     
        order by cq.QuoteNumber
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros