September 29, 2020 at 10:03 am
hi all, great day
i failed to convert the below scalar function to table valued function, i tried many times and always got error
appreciate if anybody assist in this issue
ALTER FUNCTION [dbo].[fn_GetLeaveDays]
(@DateFrom DATETIME2,@CalendarFunction INT,@DateTo AS DATETIME2,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN
IF @DateFrom>@DateTo BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
END
DECLARE @Count AS INT=0,@Date As DATETIME2=@DateFrom
WHILE @Date < @DateTo BEGIN
IF ((DATEPART(WEEKDAY,@Date)IN (6)AND @AdjustWeekEnds=1)
OR
EXISTS (select * from [C3DCalendar].[dbo].[PRIMAVERA_CALENDAR_HOLIDAYS] WHERE holiday_date=@Date AND calendar_key=@CalendarFunction AND @AdjustHolidays=1))
BEGIN
SELECT @Count = @Count + 1
END
SELECT @Date=DATEADD(DAY,1,@Date)
END
RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode
END
September 29, 2020 at 11:09 am
do you have or can you create a calendar table that contains the weekday for each date?
that would make giving you an easier example rather than having to build it as part of the function.
September 29, 2020 at 11:53 am
i've tried the below and got this error : Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_GetLeaveDays", or the name is ambiguous.
ALTER FUNCTION [dbo].[fn_GetLeaveDays]
(
@DateFrom DATETIME2,
@CalendarFunction INT,
@DateTo AS DATETIME2,
@AdjustMode BIT,
@AdjustWeekEnds BIT,
@AdjustHolidays BIT
)
RETURNS @tbl Table(totaldays int)
AS
BEGIN
IF @DateFrom>@DateTo BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
END
DECLARE @Count AS INT=0,@Date As DATETIME2=@DateFrom
WHILE @Date < @DateTo BEGIN
IF ((DATEPART(WEEKDAY,@Date)IN (6)AND @AdjustWeekEnds=1)
OR
EXISTS (select * from [C3DCalendar].[dbo].[PRIMAVERA_CALENDAR_HOLIDAYS] WHERE holiday_date=@Date AND calendar_key=@CalendarFunction AND @AdjustHolidays=1))
BEGIN
SELECT @Count = @Count + 1
END
SELECT @Date=DATEADD(DAY,1,@Date)
END
insert into @tbl
select (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode
RETURN
END
September 29, 2020 at 12:17 pm
Ok, that function needs a massive change anyway, it's a Multi-line table value function and it's using a WHILE; both are (with respect) absolutely terrible for performance. What is the actual goal of said function here, just to get the number of dates between 2 dates, excluding the dates specified; for example weekends and holidays?
As mentioned above, a Calendar table would make this far easier. Can you implement one?
Edit: For sanity (as left align, non-code formatted code is really difficult to read):
ALTER FUNCTION [dbo].[fn_GetLeaveDays] (@DateFrom datetime2,
@CalendarFunction int,
@DateTo AS datetime2,
@AdjustMode bit,
@AdjustWeekEnds bit,
@AdjustHolidays bit)
RETURNS @tbl table (totaldays int)
AS
BEGIN
IF @DateFrom > @DateTo
BEGIN
DECLARE @T datetime2 = @DateTo,
@F datetime2 = @DateFrom;
SELECT @DateFrom = @T,
@DateTo = @F;
END;
DECLARE @Count AS int = 0,
@Date AS datetime2 = @DateFrom;
WHILE @Date < @DateTo
BEGIN
IF ((DATEPART(WEEKDAY, @Date) IN (6)
AND @AdjustWeekEnds = 1)
OR EXISTS (SELECT *
FROM [C3DCalendar].[dbo].[PRIMAVERA_CALENDAR_HOLIDAYS]
WHERE holiday_date = @Date
AND calendar_key = @CalendarFunction
AND @AdjustHolidays = 1))
BEGIN
SELECT @Count = @Count + 1;
END;
SELECT @Date = DATEADD(DAY, 1, @Date);
END;
INSERT INTO @tbl
SELECT (DATEDIFF(DAY, @DateFrom, @DateTo) - (@Count)) + @AdjustMode;
RETURN;
END;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 29, 2020 at 1:11 pm
actually i can't make that calendar table and even if i can, my concern is : that table should be created for how many years? and how to define the holidays for each year? (in my case we don't have a fixed days per year, it changed from year to year) so i created a holiday_table to insert all holidays dates in it.
September 29, 2020 at 1:15 pm
Having a separate table for the holidays isn't a problem; I personally (at the office) use a separate table to store holidays; mainly as i store some additional meta data (such as if the business operated at reduced hours). Some prefer to have a column in the Calendar Table to denote if the day is a holiday or not but it's not required. A Calendar Table simply compliments to "Holiday Table"
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 29, 2020 at 1:16 pm
FINALLY IT WORKS 🙂
the issue was in how to call the function not in the mentioned above modified function
it works fine when i used this to call it
Select * from [dbo].[fn_GetLeaveDays] ('2020-08-19',1,'2020-08-22',1,1,1)instead of
Select [dbo].[fn_GetLeaveDays] ('2020-08-19',1,'2020-08-22',1,1,1) September 29, 2020 at 1:28 pm
My points about the TVF you have still apply though, tanehome. A Multi-line table value function, with a WHILE is going to be very poor for performance. You really want an iTVF, not an mlTVF. Just because the above works doesn't mean you should be implementing it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 29, 2020 at 1:41 pm
actually my knowledge with functions is very poor as this is first time to use function
appropriate if you guide me to how to convert my mlTVF to iTVF and what is the different between them
September 29, 2020 at 1:46 pm
FINALLY IT WORKS 🙂
the issue was in how to call the function not in the mentioned above modified function
it works fine when i used this to call it
Select * from [dbo].[fn_GetLeaveDays] ('2020-08-19',1,'2020-08-22',1,1,1)instead of
Select [dbo].[fn_GetLeaveDays] ('2020-08-19',1,'2020-08-22',1,1,1)
If the function still has the word BEGIN in it, then it may be working correctly but it is not working "fine". 😉 Read what Thom A has been trying to tell you on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2020 at 10:02 am
hi again
actually the function does not work properly after mant tests
for example i've tried
Select [dbo].[fn_GetLeaveDays_scalar] ('2020-09-24',1,'2020-09-25',1,1,1)and it should returns only 1 as friday is weekend but it returns 2
could you please assist to find whats wrong in the
ALTER FUNCTION [dbo].[fn_GetLeaveDays] (@DateFrom datetime2,
@CalendarFunction int,
@DateTo AS datetime2,
@AdjustMode bit,
@AdjustWeekEnds bit,
@AdjustHolidays bit)
RETURNS @tbl table (totaldays int)
AS
BEGIN
IF @DateFrom > @DateTo
BEGIN
DECLARE @T datetime2 = @DateTo,
@F datetime2 = @DateFrom;
SELECT @DateFrom = @T,
@DateTo = @F;
END;
DECLARE @Count AS int = 0,
@Date AS datetime2 = @DateFrom;
WHILE @Date < @DateTo
BEGIN
IF ((DATEPART(WEEKDAY, @Date) IN (6)
AND @AdjustWeekEnds = 1)
OR EXISTS (SELECT *
FROM [C3DCalendar].[dbo].[PRIMAVERA_CALENDAR_HOLIDAYS]
WHERE holiday_date = @Date
AND calendar_key = @CalendarFunction
AND @AdjustHolidays = 1))
BEGIN
SELECT @Count = @Count + 1;
END;
SELECT @Date = DATEADD(DAY, 1, @Date);
END;
INSERT INTO @tbl
SELECT (DATEDIFF(DAY, @DateFrom, @DateTo) - (@Count)) + @AdjustMode;
RETURN;
END;
function
September 30, 2020 at 10:25 am
hi again
actually the function does not work properly after mant tests
With respect, this does not surprise me; I did mention that the Function was flawed in my above posts. Perhaps now you would consider explaining the goal of said function this time, please? Then, if appropriate, you can then consider using a Calendar Table, and myself or another user can show you how to do this with an inline Table Value Funtionc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 10:40 am
this function is calculating the number of working days between two dates Excluding Weekend & Holiday
and it takes the holidays from table exist in another database (for attendance)
@DateFrom - The starting date for calculation
@CalendarFunction - The holiday type function you want to use as we have two groups type
@DateTo - The end of the date range you want to use
@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays - Excludes holidays if the holiday function matches
September 30, 2020 at 11:17 am
Ok, firstly, then, let's start by creating a Calendar Table. This is a fairly simple one that I grabbed from one of my Sandbox databases, but should be enough for what we want here. Obviously, however, feel free to add (or remove) from it:
CREATE TABLE [dbo].[CalendarTable]([CalendarDate] [date] NOT NULL,
[CalendarYear] AS (datepart(year,[CalendarDate])) PERSISTED,
[CalendarMonth] AS (datepart(month,[CalendarDate])) PERSISTED,
[CalendarDay] AS (datepart(day,[CalendarDate])) PERSISTED,
[CalendarMonthName] varchar(15) NOT NULL,
[CalendarWeekday] varchar(15) NOT NULL,
[CalendarWeekdayName] varchar(15) NOT NULL,
[Weekend] AS CONVERT(bit,CASE WHEN CalendarWeekday IN (1,7) THEN 1 ELSE 0 END) PERSISTED,
[JulianDate] AS ((DATEPART(YEAR,[CalendarDate]) * 1000 + DATEDIFF(DAY,DATEFROMPARTS(DATEPART(YEAR,[CalendarDate]),1,1),[CalendarDate]))+ 1 ) PERSISTED);
GO
ALTER TABLE dbo.CalendarTable ADD CONSTRAINT PK_CalendarDate PRIMARY KEY CLUSTERED (CalendarDate);(CalendarDate);
--I recommend making some indexes here too
GO
SET DATEFIRST 7; --Sunday
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP (DATEDIFF(DAY,'19000101','21000101'))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS I
FROM N N1, N N2, N N3, N N4, N N5),
Dates AS(
SELECT DATEADD(DAY, I, '19000101') AS D
FROM Tally)
INSERT INTO dbo.CalendarTable (CalendarDate, CalenderMonthName, CalenderWeekday,CalenderWeekdayName)
SELECT D,
DATENAME(MONTH,D),
DATEPART(WEEKDAY,D),
DATENAME(WEEKDAY,D)
FROM Dates;
OK, now we have a Calendar Table, we can start moving onto the function. I don't have a copy of your Holiday table, so I can't completely test this. As a result my tests excluded any references to the Holiday Table, and I uncommented the code for this post.
First, let's get the base logic down, counting the dates between 2 dates, and adding the inclusive/exclusive logic:
DECLARE @DateFrom date,
@DateTo date,
@AdjustMode bit;
SET @DateFrom = '20200117';
SET @DateTo = '20200930';
SET @AdjustMode = 0;
SELECT COUNT(*)
FROM dbo.CalendarTable CT
WHERE CT.CalendarDate >= @DateFrom
AND CT.CalendarDate <= @DateTo
AND ((CT.CalendarDate NOT IN (@DateFrom,@DateTo) AND @AdjustMode = 0)
OR @AdjustMode = 1);
For the 2 sample dates, that brings back 258 for 1 (inclusive mode) and 256 for 0 (exclusive mode). I didn't just subtract 2, as @DateFrom or @DateTo might be a holiday or weekend, and thus the result would be wrong.
Excluding weekends is trivial, just add another parameter, @AdjustWeekend bit and then to the WHERE add this extra clause:
AND ((@AdjustWeekend = 1 AND CT.Weekend = 0) OR @AdjustWeekend = 0)
The final part, which I can't test, would be adding the Holiday exclusion and the holidays to use. I would use an EXISTS for this:
AND (@AdjustHoliday = 0
OR (@AdjustHoliday = 1
AND NOT EXISTS(SELECT 1
FROM dbo.HolidayTable HT --Obviously replace with your holiday table's name
WHERE HT.HolidayDate = CT.CalendarDate
AND HT.HolidayFunction = @CalendarFunction)));
Finally, we then need to wrap this into an inline Table Value Function:
CREATE FUNCTION dbo.GetLeaveDays (@DateFrom date, @DateTo date, @AdjustMode bit, @AdjustWeekend bit, @AdjustHoliday bit, @CalendarFunction int)
RETURNS TABLE
AS RETURN
SELECT COUNT(*) AS LeaveDays
FROM dbo.CalendarTable CT
WHERE CT.CalendarDate >= @DateFrom
AND CT.CalendarDate <= @DateTo
AND ((@AdjustWeekend = 1 AND CT.Weekend = 0) OR @AdjustWeekend = 0)
--Commented out, as I cannot compile such a function
/*
AND (@AdjustHoliday = 0
OR (@AdjustHoliday = 1
AND NOT EXISTS(SELECT 1
FROM dbo.HolidayTable HT --Obviously replace with your holiday table's name
WHERE HT.HolidayDate = CT.CalendarDate
AND HT.HolidayFunction = @CalendarFunction)))
*/
AND ((CT.CalendarDate NOT IN (@DateFrom,@DateTo) AND @AdjustMode = 0)
OR @AdjustMode = 1);
Which we can then test with something like this (obviously my version does nothing with the Holiday logic):
SELECT GLD.LeaveDays
FROM dbo.GetLeaveDays('20200103','20200917',1,1,0,NULL) GLD;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply