February 10, 2018 at 9:37 am
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.
February 10, 2018 at 1:03 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply