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) "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".
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy