Use a case statement within a function parameter.

• paul.stevens 24552

  SSC Veteran

  Points: 249

  More actions

  February 10, 2018 at 9:37 am

  #403107

  Hi, 
  I have a problem which is as follows: I have a function that calculates working days between 2 dates (it also excludes bank hols) 
  This works fine - [dbo].[fnc_CalcWorkDaysBetween] ('2017-04-01', '2018-04-01') = 251 working days.

  The problem i have is that in the data-set i need to apply this to, the dates are calculated based on the ISNULL function to pick out dates where the user has not entered a date. 

  I have the following case statement which only selects certain rows to apply  the ISNULL function to:

          CASE
      WHEN (CASE
         WHEN CONVERT(VARCHAR(50), All_SIRI.Icd_num2) IS NULL
           OR CONVERT(VARCHAR(50), All_SIRI.Icd_num2) = '' THEN
           'N'
         ELSE
           'Y'
        END
        ) = 'Y' THEN 
                           
           ISNULL(ISNULL(ISNULL (Stg22.STAGE_022_DATE_COMPLETED, Stg22.STAGE_022_START_DATE), Stg22.STAGE_022_TARGET_DATE),dbo.INCID_ALL_INCIDENTS.INCIDENT_DATE)

           ELSE NULL

           end

        AS

  STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE,

  The above gives me a date that i wish to include in the function but i just cant seem to get it to work.

  What I'm trying to achieve is something like

  [dbo].[fnc_CalcWorkDaysBetween] (STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE, '2018-04-01')

  Any suggestions would be appreciated.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  February 10, 2018 at 1:03 pm

  #1979354

  You could try using a CROSS APPLY to assign the alias name, then use that alias in the SELECT clause:

  SELECT ...,
      ca1.STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE,
      [dbo].[fnc_CalcWorkDaysBetween] (ca1.STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE, '2018-04-01')
  FROM ...
  JOIN ...
  CROSS APPLY (
  SELECT 
  CASE
  WHEN (CASE
  WHEN CONVERT(VARCHAR(50), All_SIRI.Icd_num2) IS NULL
  OR CONVERT(VARCHAR(50), All_SIRI.Icd_num2) = '' THEN
  'N'
  ELSE
  'Y'
  END
  ) = 'Y' THEN 
  ISNULL(ISNULL(ISNULL (Stg22.STAGE_022_DATE_COMPLETED, Stg22.STAGE_022_START_DATE), Stg22.STAGE_022_TARGET_DATE),dbo.INCID_ALL_INCIDENTS.INCIDENT_DATE)
  ELSE NULL 
  end
  AS 
  STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE
  ) AS ca1

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".