Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE Statement multiple aurgument Expand / Collapse
Author
Message
Posted Thursday, February 6, 2014 6:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 12:12 PM
Points: 46, Visits: 197
Hello,

I searched online and this forum and I am not convinced that I find the solution.

I am trying to write a TSQL statement as follows:

SELECT DISTINCT cs.SITE
, CASE WHEN cv.accepted = 1 AND cv.StepNo = 1 then 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (ci.plannedinstalldt, -17))
FROM
dbo.Cy_SiteSurvey AS cs WITH (nolock)
INNER JOIN
dbo.Cy_Visit_Checklist AS cv WITH (nolock)
ON cv.SysSiteID = cs.SysSiteID
WHERE cv.Visit = 'V1'

This is returning two results. I am expecting only 'Y'. Is this a right way to write CASE statement? Thank you!
Post #1538610
Posted Thursday, February 6, 2014 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,789, Visits: 14,000
It is possible for a CASE block to change the number of rows returned by SELECT with DISTINCT.
Find a filter (a WHERE clause) which returns only rows which you think are affected, and test them using something like this:
SELECT --DISTINCT 
cs.SITE,

cv.accepted,
cv.StepNo,
ci.plannedinstalldt,

CASE
WHEN cv.accepted = 1 AND cv.StepNo = 1 then 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (ci.plannedinstalldt, -17))
END
FROM dbo.Cy_SiteSurvey AS cs WITH (nolock)
INNER JOIN dbo.Cy_Visit_Checklist AS cv WITH (nolock)
ON cv.SysSiteID = cs.SysSiteID
WHERE whatever



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1538637
Posted Monday, February 10, 2014 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 12:12 PM
Points: 46, Visits: 197
I apologize but that did not work. I am still getting two records. I may have to dig deep in my JOINS. I do appreciate you taking a look into it. Again, thank you for your time and effort. Much appreciated!!
Post #1539757
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse