September 30, 2020 at 12:50 pm
Thank you Thom A appreciate your assist
I've made the calendar table and added my holiday table and it seems works good except the
@AdjustMode -
it should Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the (first day only).
but actually it excludes for both first day and end day
September 30, 2020 at 12:53 pm
@AdjustMode -
it should Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the (first day only).
but actually it excludes for both first day and end day
I take the time to explain the logic in my post, including the implementation of the @AdjustMode parameter, so you can see from my post explicitly what clause that is and amend it accordingly.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2020 at 7:10 pm
>> failed to convert the below scalar function to table valued function, i tried many times and always got error <<
Actually, your problem is that you're not writing SQL yet! We meant this to be a declarative language, but your writing it as it was still BASIC or Fortran or COBOL or some other procedural language. In declarative languages, we don't write with While loops or any loops at all! We don't use if – then – else flow control because declarative languages do not have any flow to control. In fact, you're so locked into the Fortran/BASIC family, you actually use the original "FN_" prefix on function names! The reason we did that 50 something years ago was that the compilers were simple One Pass things and had to have such prefixes for the parsers. Then on top of all that you use bit data types. We don't use bit flags in a declarative language; that was assembly language; instead of marking a status with the flag, we discovered with the predicate.
You seem to feel that a holiday is totally different from any other calendar date. In a relational model being a holiday would be an attribute of a calendar date, and therefore would not need its own separate table. If you can explain what you want, we can try and design a table declaration that will include the attributes of the dates that you want. We can then go in, record this attribute, and not have to do computations the way you're doing it.
I've got some sample code and discussions of this in one of my books, if you want to look at more details.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 1, 2020 at 9:50 am
You don't need a calendar table for this. Also, note that the Friday check below works under any/all DATEFIRST settings. DATEFIRST is not something you want to change in your code(!). Safer to always use DATEFIRST-neutral methods of determining day of week and doing other date calcs/adjustments.
This code does include the date swap if they pass in a DateFrom that is greater than the DateTo.
The code below assumes Fridays won't be in the holidays table (because it's very late here and I'm tired). If that's a possibility -- that a Friday(s) will be in the holidays table -- let me know and I will adjust the code accordingly.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
--SELECT * FROM dbo.fn_GetLeaveDays('2020-09-24',1,'2020-09-25',1,1,1) --sample use
GO
CREATE FUNCTION dbo.fn_GetLeaveDays
(
@DateFrom datetime2,
@CalendarFunction int,
@DateTo datetime2,
@AdjustMode bit,
@AdjustWeekEnds bit,
@AdjustHolidays bit
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT DATEDIFF(DAY, DateFrom, DateTo) + 1 - (1 - @AdjustMode) -
/* subtract holiday days, if specified */
ISNULL((SELECT COUNT(*)
FROM C3DCalendar.dbo.PRIMAVERA_CALENDAR_HOLIDAYS PCH
WHERE
@AdjustHolidays = 1 AND
PCH.holiday_date >= DATEADD(DAY, 1 - @AdjustMode, check_dates.DateFrom) AND
PCH.holiday_date <= check_dates.DateTo AND
PCH.calendar_key = @CalendarFunction
), 0) -
/* subtract Fridays, if specified */
ISNULL((SELECT COUNT(*)
FROM cte_tally1000 t
WHERE
@AdjustWeekEnds = 1 AND
t.number BETWEEN (1 - @AdjustMode) AND DATEDIFF(DAY, DateFrom, DateTo) AND
DATEDIFF(DAY, 0, DATEADD(DAY, t.number, DateFrom)) % 7 = 4 /*Friday*/
), 0) AS totaldays
FROM (
SELECT CASE WHEN @DateFrom > @DateTo THEN @DateTo ELSE @DateFrom END AS DateFrom,
CASE WHEN @DateFrom > @DateTo THEN @DateFrom ELSE @DateTo END AS DateTo
) AS check_dates
)
;
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".
October 1, 2020 at 4:00 pm
Thank you ScottPletcher so much, appreciate your effort and assist.
that's exactly what i need, but i forced to remove "WITH SCHEMABINDING" because it gives me an error as i mentioned table in another database (is it important to keep it?)
regarding Fridays, it will not be in holiday table.
October 1, 2020 at 4:06 pm
>> failed to convert the below scalar function to table valued function, i tried many times and always got error <<
Actually, your problem is that you're not writing SQL yet! We meant this to be a declarative language, but your writing it as it was still BASIC or Fortran or COBOL or some other procedural language. In declarative languages, we don't write with While loops or any loops at all! We don't use if – then – else flow control because declarative languages do not have any flow to control. In fact, you're so locked into the Fortran/BASIC family, you actually use the original "FN_" prefix on function names! The reason we did that 50 something years ago was that the compilers were simple One Pass things and had to have such prefixes for the parsers. Then on top of all that you use bit data types. We don't use bit flags in a declarative language; that was assembly language; instead of marking a status with the flag, we discovered with the predicate.
You seem to feel that a holiday is totally different from any other calendar date. In a relational model being a holiday would be an attribute of a calendar date, and therefore would not need its own separate table. If you can explain what you want, we can try and design a table declaration that will include the attributes of the dates that you want. We can then go in, record this attribute, and not have to do computations the way you're doing it.
I've got some sample code and discussions of this in one of my books, if you want to look at more details.
you are totally right, as i like visual basic and my early learning was VB
regarding sample and discussions, i appreciate if u share them.
October 1, 2020 at 4:11 pm
OOPS, yeah, I forgot about the cross-db reference. I should not have put SCHEMABINDING in there.
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".
October 1, 2020 at 4:38 pm
This code does include the date swap if they pass in a DateFrom that is greater than the DateTo.
if i need it to does not include date swap, means give me zero when DateFrom is greater than Dateto
does it will affect the calculation? and how to made it without affects?
October 1, 2020 at 4:50 pm
ScottPletcher wrote:This code does include the date swap if they pass in a DateFrom that is greater than the DateTo.
if i need it to does not include date swap, means give me zero when DateFrom is greater than Dateto
does it will affect the calculation? and how to made it without affects?
Your original code had that in it.
You'd have to put specific code in there to automatically return 0 if DateFrom > DateTo.
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".
October 22, 2020 at 4:47 pm
Hi all again
the function works fine for two weeks as long as the entered DateFrom is not a Holiday (it counts it as a working day) but I think it's not a big issue as by logic the user shouldn't enter a holiday as a DateFrom to calculate.>>>
the most important issue that I need to modify the same function to use it in another task to check if the (DateFrom) is a Friday or is in the Holiday table by returns '0' if not found or '1' if found (and removing the (DateTo))
I've almost spent two days to change it with no luck 🙁
appreciate if anybody can advise what to do...
ALTER FUNCTION [dbo].[fn_GetLeaveDays2]
(
@DateFrom DATETIME, --@DateFrom - The entered date for your calculation
@CalendarFunction NVARCHAR(1),--@CalendarFunction - The holiday type function you want to use
--@DateTo AS DATETIME,--@DateTo - The end of the date range you want to use
@AdjustMode BIT,--@AdjustMode - Calculate the days between the dates as 0 Exclusive or 1 Inclusive of the first day.
@AdjustWeekEnds BIT,--@AdjustWeekend - Excludes weekends from your calculations
@AdjustHolidays BIT--@AdjustHolidays - Excludes holidays if the holiday function matches
)/*@AdjustMode 0=Count whole days only,1=Any day counts as 1*/
RETURNS TABLE
--WITH SCHEMABINDING
AS
RETURN (
WITH
cte_tally10 AS (
SELECT number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT 0 AS number UNION ALL SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT DATEDIFF(DAY, DateFrom, DateTo) + 1 - (1 - @AdjustMode) -
/* subtract holiday days, if specified */
ISNULL((SELECT COUNT(*)
FROM [DWH_Biostar].[dbo].[HOLIDAYS_LR] HT
WHERE
@AdjustHolidays = 1 AND
HT.hDate >= DATEADD(DAY, 1 - @AdjustMode, check_dates.DateFrom) AND
HT.hDate <= check_dates.DateTo AND
HT.nHoliday = @CalendarFunction
), 0) -
/* subtract Fridays, if specified */
ISNULL((SELECT COUNT(*)
FROM cte_tally1000 t
WHERE
@AdjustWeekEnds = 1 AND
t.number BETWEEN (1 - @AdjustMode) AND DATEDIFF(DAY, DateFrom, DateTo) AND
DATEDIFF(DAY, 0, DATEADD(DAY, t.number, DateFrom)) % 7 = 4 /*Friday*/
), 0) AS totaldays
FROM (
SELECT CASE WHEN @DateFrom > @DateTo THEN @DateTo ELSE @DateFrom END AS DateFrom,
CASE WHEN @DateFrom > @DateTo THEN @DateFrom ELSE @DateTo END AS DateTo
) AS check_dates
)
October 23, 2020 at 2:51 am
I'd need directly usable sample data for the holiday table -- CREATE TABLE and INSERT statement(s) -- to correct this for you.
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".
October 23, 2020 at 11:03 am
sorry, I forgot to put sample data for holiday table, here it's
CREATE TABLE [dbo].[HOLIDAYS_LR](
[nHoliday] [varchar](1) NULL,
[hDate] [date] NULL,
[hEnddate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-08','2020-10-08 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-10','2020-10-10 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A', '2020-10-24','2020-10-24 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('A','2020-11-21','2020-11-21 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-03','2020-10-03 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-08','2020-10-08 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B', '2020-10-17','2020-10-17 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-10-31','2020-10-31 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-11-14','2020-11-14 22:00:00.000');
INSERT INTO HOLIDAYS_LR (nHoliday, hDate, hEnddate) values ('B','2020-11-28','2020-11-28 22:00:00.000');
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply