February 13, 2015 at 5:37 pm
Sangeeth878787 (2/13/2015)
This is the exact requirement I need to do:We have a requirement to show 'Business Day / Working Day ' as a output for the below input parameters
Input
a) Input Date (Date format)
b) Factory Calendar (2 digit char)(Country Code like GB,Inr,JP,US..)
c) Days (Either minus or plus in days)
Output
Date (Date format)
?Functionality?
?The purpose of this function/stored procedure will be used globally across projects to derive working date from the given date and calculate backward or forward using 'Days'.
Example: ?
Case-1) If we pass 12.Feb.2015 - Calendar GB - Days as '2' Expected Output = 16.Feb.2015 because 14th and 15th are holidays.
Case-2) If we pass 12.Feb.2015 - Calendar GB - Days as -2' Expected Output = 10.Feb.2015
We have Common Dimension Table : DimDate table for calendar.
Like I said previously, "understood on the requirements". Also as previously said, please post the CREATE TABLE statement for your calendar and holiday table(s) and the code for the function you currently have. Those things will give me some important clues on how to best help you.
--Jeff Moden
February 13, 2015 at 5:59 pm
Ok, I've put together a sample of how I would solve it. Jeff or someone else may have a more efficient way.
Because you posted no DDL I don't have your Dim_date calendar table, so I will be using Dwain Camps' GenerateCalendar function from here[/url] to simulate it. Where you see it, replace with your table.
Once you have that function the following code will build a sample Holidays table, and build an inline table valued function that is essentially the calendar table with holiday logic applied for whichever country you pass in.
Then I build the function you ask for, which basically queries and numbers non holiday dates, so that you can find the date with the correct offset in days.
-- First, we need a holidays table
CREATE TABLE Holidays (
Country varchar(25),
HolidayDatedate,
HolidayName varchar(50) null
)
-- Put some sample holidays in for a couple different countries
INSERT Holidays (Country,HolidayDate,HolidayName)
SELECT 'UNITED STATES','2014-12-25','Christmas Day'
UNION ALL
SELECT 'CANADA','2014-12-25','Christmas Day'
UNION ALL
SELECT 'CANADA','2014-12-26','Boxing Day'
GO
-- Now, an inline table valued function that joins our calendar table to the new holidays table based on the country, and tells us what working days are
CREATE FUNCTION CalendarWithHolidays
(
@Country varchar(25)
)
RETURNS TABLE
AS
RETURN
(
SELECT d.*,
CASE WHEN d.WkDNo IN (1,7) THEN 0
WHEN h.HolidayDate IS NOT NULL THEN 0
ELSE 1
END IsWorkingDay,
CASE WHEN h.HolidayDate IS NOT NULL THEN 1
ELSE 0
END IsHoliday,
h.HolidayName
FROM GenerateCalendar('2014-12-01',31) d
LEFT JOIN Holidays h
ON h.HolidayDate = d.[Date]
AND h.Country = @Country
)
GO
-- Some sample output. Its like we have separate date dimensions for each country
SELECT * FROM CalendarWithHolidays ('CANADA')
SELECT * FROM CalendarWithHolidays ('UNITED STATES')
GO
-- Now, your 'dateadd working days' function. I made it table valued too. You can easily switch it to scalar, but this is more flexible
CREATE FUNCTION AddWorkingDays
(
@StartDate date,
@Country varchar(25),
@offset int
)
RETURNS TABLE
AS
RETURN
(
WITH d1 AS (
SELECT [date], ROW_NUMBER() OVER (ORDER BY [Date]) rn --I could have used SeqNo here, but your calendar table may not have one
FROM CalendarWithHolidays(@Country)
WHERE IsWorkingDay = 1
)
SELECT d2.[date]
FROM d1
JOIN d1 d2
ON d1.rn + @offset = d2.rn
WHERE d1.[Date] = @StartDate
)
GO
-- Some test runs
--US add a day to christmas eve
SELECT *,'US Add a day to Dec 24' FROM AddWorkingDays('2014-12-24','UNITED STATES',1)
--Canada add a day to christmas eve
SELECT *,'Can Add a day to Dec 24' FROM AddWorkingDays('2014-12-24','CANADA',1)
--US no offset
SELECT *, 'US No Offset' FROM AddWorkingDays('2014-12-24','CANADA',0)
--US subtract a day from Dec 29
SELECT *,'US subtract a day from Dec 29' FROM AddWorkingDays('2014-12-29','UNITED STATES',-1)
--Canada subtract a day from Dec 29
SELECT *,'Can subtract a day from Dec 29' FROM AddWorkingDays('2014-12-29','CANADA',-1)
DROP FUNCTION CalendarWithHolidays
DROP FUNCTION AddWorkingDays
DROP TABLE Holidays
February 16, 2015 at 2:36 am
Hi,
I am glad for your detailed explanation, I will try to implement asap and let you know the success rate on this.
Many Thanks
February 16, 2015 at 2:45 am
Hi,
We haven't created standard holiday table yet,
The function
create function [dbo].[ufn_NextBusinessDay]
(
@startdate date,
@numdays int
)
returns date
as
begin
declare
@nextBusday date,
@weekday int
set @nextBusday = @startdate
declare @dayloop int
set @dayloop = 0
while @dayloop < @numdays
begin
set @nextBusday = DATEADD(d,1,@nextbusday)
set @weekday = ((@@DATEFIRST + DATEPART(dw,@nextbusday)-2)%7) + 1
if @weekday = 6
set @nextBusday = dateadd(d,2,@nextBusday)
select @nextBusday = [dbo].[ufn_NextBusinessDay](@nextBusday,1)
where
exists (select holidaydate from [dbo].[Holiday_Test] where holidaydate = @nextBusday)
set @dayloop = @dayloop + 1;
end
return @nextbusday
end
But this function fail to check past date. Eg : (getdate(),-4), Then Its not checking 4 days prior to 16/02/2015.
If Possible Could you help, Many Thanks
February 16, 2015 at 5:49 am
The Table at the moment we had in the database.
CREATE TABLE [dbo].[Dim_Date](
[DateKey] [date] NULL,
[CalendarDate] [date] NULL,
[CalendarYear] [int] NULL,
[CalendarQuarter] [varchar](10) NULL,
[CalendarMonth] [varchar](10) NULL,
[CalendarWeek] [varchar](10) NULL,
[CalendarQuarterStartDate] [date] NULL,
[CalendarQuarterEndDate] [date] NULL,
[CalendarHalfYearlyStartdate] [date] NULL,
[CalendarHalfYearlyEndDate] [date] NULL,
[MonthFullName] [varchar](30) NULL,
[MonthShortName] [varchar](15) NULL,
[DayFullName] [varchar](30) NULL,
[DayShortName] [varchar](15) NULL,
[FiscalDate] [date] NULL,
[FiscalYear] [int] NULL
GO
February 18, 2015 at 12:28 am
Joe's suggestion is spot on for this. The only difference that I'd take is that I'd use the underlying date serial numbers for the dates so that you don't have to make two trips to the database to come up with the next business day.
--Jeff Moden
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply