Looping for value in a function

[varadha72]

Hi

Can someone help me here, I am not a DB guy, I am BA and need some assistance for data mining.

I have a function- fn_nonworkingdays (input- start date, calendar_id) returns int (0-working day, 1-nonworking day).

Now I need to create another function that actually checks if
1. it takes the input date and calculates the month_end_date. (eg input date-9/10/2014, then month_end_date=9/30/2014)
2. Now it should validate the month_end_date from this with the above function (fn_nonworkingdays) to check if 9/30/2014 is a working or nonworking day,
a. if working calculate no of days in that month.
b. if not working day, then step over to next day i.e. 10/1/2014 and check if that is a working day, it needs to check in the above function only. if it is working then do same as #a, if not repeat step#b until the working day is reached.

Note the calendar is not specific to USA, I pass calendar_ID which is particular to a country.

This is what I created-

alter FUNCTION dbo.fn_MTD_V1
(
@REPORT_DATE VARCHAR (20) ,
@calendar_id smallINT,
)
RETURNS INT
AS
BEGIN

DECLARE @STARTDATE AS DATETIME ;
DECLARE @REPORTDATE AS DATETIME ;
DECLARE @DayAfterReportDate AS DATETIME ;
DECLARE @TOTALDAYCOUNT INT
DECLARE @ENDDATE as DATETIME;

SELECT @REPORTDATE=@REPORT_DATE
SELECT @StartDate=CONVERT(DATETIME,DATEADD(dd,-(DAY(@REPORTDATE)-1),@REPORTDATE),111)
SELECT @ENDDATE=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@REPORTDATE)+1,0))
SELECT @DayAfterReportDate=DATEADD(day, 1, @ENDDATE)

IF (dbo.ABfn_IsNonWorkingDay(@ENDDATE, @calendar_id)=0) --0=working and 1=non-working
BEGIN
SELECT @TotalDayCount= DATEDIFF(DAY, @StartDate, @ENDDATE)

END
-----------------------NEXT DAY-----------------------------------------------------------------------------------------
ELSE IF (dbo.ABfn_IsNonWorkingDay(@REPORTDATE, @calendar_id)=1 AND (dbo.ABfn_IsNonWorkingDay(@DayAfterReportDate, @calendar_id)=0))

BEGIN
SELECT @DayAfterReportDate=@DayAfterReportDate+1
SELECT @TotalDayCount= DATEDIFF(DAY, @StartDate, @DayAfterReportDate)

END
-----------------------DAY AFTER NEXT DAY-------------------------------------------------------------------------------

ELSE IF (dbo.ABfn_IsNonWorkingDay(@REPORTDATE, @calendar_id)=1 AND (dbo.ABfn_IsNonWorkingDay(@DayAfterReportDate, @calendar_id)=1))

BEGIN
SELECT @DayAfterReportDate=@DayAfterReportDate+2
SELECT @TotalDayCount= DATEDIFF(DAY, @StartDate, @DayAfterReportDate)

END

Return @TotalDayCount

I am not sure how to put the above case in loop, because sometimes there can be several days which can be holidays at a stretch. Say an entire week can be holiday, in which case I need some kind of looping query.
Please help.

 

[RyanEK]

Hi,

How about the following?

create function fn_MTD_V1 
(
	@report_date datetime,
	@calendar_id smallint
)
returns int 
as
begin
	declare @days int	
	if (dbo.ABfn_IsNonWorkingDay(dateadd(d,-1,dateadd(mm, datediff(m,0,@report_date)+1,0)),@calendar_id) = 0) 
	begin
		set @days = datediff(day, @report_date, dateadd(month, 1, @report_date))
	end
	else
	begin	
		set @report_date = dateadd(day,1,@report_date)
		while (dbo.ABfn_IsNonWorkingDay(@report_date,@calendar_id) != 0)	
			set @report_date = dateadd(day,1,@report_date)
		set @days = datediff(day, @report_date, dateadd(month, 1, @report_date))
	end
	return @days
end

This function assumes that eventually your ABfn_IsNonWorkingDay function will return a working day, otherwise you will hit an overflow error.

Ryan

 

[varadha72]

Hi Ryan,

The above query is working when the last day is working. The value is correct. But please consider the second case mentioned below.

Case#1:
@Report_date=5/31/2014 (Saturday)
Holidays- 5/31/2014 (Sat), 6/1/2014 (Sun)
Expected output for Day count- 5/1/2014:5/31/2014 + 6/1/2014 since the next day is holiday. Hence @Days should be 32 days.

Case#2:
@Report_date=2/28/2013 (Thursday, but is a holiday in Korea hence nonworking day)
Holidays- 3/1/2013 (Fri- Holiday in Korea), 3/2/2013 (Sat), 3/3/2013 (Sun)
Expected output for Day count- 2/1/2013:2/28/2013 + 3/1 + 3/2 +3/3 since the subsequent days are holidays. Hence @Days should be 31 days.

Please help

 

[RyanEK]

I think I understand you now. Does this work for you?

create function [dbo].[fn_MTD_V1] 
(
	@report_date datetime
)
returns int 
as
begin
	declare @days int
	declare @endmonth datetime
	set @endmonth = dateadd(d,-1,dateadd(mm,datediff(m,0,@report_Date)+1,0))
	set @days = day(@endmonth)
	if (dbo.ABfn_IsNonWorkingDay(@endmonth) != 0) 
	begin		
		set @report_date = dateadd(day,1,@report_date)
		while (dbo.ABfn_IsNonWorkingDay(@report_date) != 0)	
		begin
			set @report_date = dateadd(day,1,@report_date)
			set @days = @days + 1
		end
	end
	return @days
end