CASE Statement multiple aurgument

• Sanjeev Jha-475652

  Say Hey Kid

  Points: 704

  More actions

  February 6, 2014 at 6:04 am

  #99901

  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!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 6, 2014 at 7:04 am

  #1686585

  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

• Sanjeev Jha-475652

  Say Hey Kid

  Points: 704

  More actions

  February 10, 2014 at 7:26 am

  #1687536

  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!!