Hi
I have one procedure and inside procedure call function. Which are take more time .
I want to remove finction and retrive function value with select statment with my query. So please suggest how can do this.
My function :
Create Function dbo.fn_GetWorkDays(
@startDate DateTime
,@endDate DateTime
,@excludeHolidays INT = 0
)
Returns int
BEGIN
-- DECLARE INTERNAL VARIABLES
DECLARE @fdow INTEGER
DECLARE @ldow INTEGER
DECLARE @wdays INTEGER
DECLARE @first_date DATETIME
DECLARE @last_date DATETIME
DECLARE @days INTEGER
-- Make sure the earlier date is assigned to @first_date
IF ( @startDate <= @endDate )
BEGIN
SET @first_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @startDate, 110))
SET @last_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @endDate, 110))
END
ELSE
BEGIN
SET @first_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @endDate, 110))
SET @last_date = CONVERT(DATETIME, CONVERT(VARCHAR(10), @startDate, 110))
END
-- Obtain the day of week for the first and last date
SET @fdow = DATEPART( DW, @first_date )
SET @ldow = DATEPART( DW, @last_date )
SET @days = DATEDIFF( DD, @first_date, @last_date ) + 1
SET @wdays = @days - (((@days + (@fdow - 3)) / 7) * 2) -
((1 - (@fdow / 7)) * ((9 - @fdow) % 7 % 6 % 5 % 4 % 3)) -
((@ldow + 1) % 7 % 6 % 5 % 4 % 3)
-- This section will be implemented when we decide to exclude Holidays
IF (@excludeHolidays = 1)
BEGIN
SET @wdays = @wdays - ISNULL((Select count(holiday_date) from Holidays (nolock)
Where holiday_date between @startdate and @enddate
and DatePart(dw, holiday_date) between 2 and 6), 0)
END
RETURN Case When @wdays < 0 Then 0 Else @wdays End
END
Any one update please.....
Tapan PatelPosted May 25, 2017, 11:53 AM
Nilesh ShahPosted May 20, 2017, 8:30 PM