SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		RezaMosallanejad
-- Create date: 13990629
-- Description:	return all holidays between to  dates
-- =============================================
CREATE FUNCTION fn_returnHolidays 
(	
	-- Add the parameters for the function here
	@fromDate datetime, 
	@toDate datetime
)
RETURNS @result table (id int identity(1,1),holiday datetime) 
AS begin
------------------------
	-- Declare the return variable here
	declare @HolidayXML xml
	declare @HolidayTBL table(Y int,M int,D int)
	-- Add the T-SQL statements to compute the return value here
	select @HolidayXML=CalendarHolidayXML from Departments where IsReference=1

	insert into @HolidayTBL 
	select 
	[Year] = Node.Data.value('(@Year)[1]', 'int'), 
	[Month] = Node.Data.value('(@Month)[1]', 'int'), 
	[Day] = Node.Data.value('(@Day)[1]', 'int') 
	FROM @HolidayXML.nodes('/Holidays/Date') Node(Data)

	while(DATEDIFF(D,@fromDate,@toDate)>=0)
	begin
		if 
		DATEPART(dw,@fromDate)=6 --friday
		or exists (select * from CalendarOff where CalendarDate=cast(@fromDate as date) and ISOff=1)--for ver10
		or exists(
		select * from @HolidayTBL where 
		y=cast(substring(dbo.MiladiTOShamsi(@fromDate),1,4) as int) and
		m=cast(substring(dbo.MiladiTOShamsi(@fromDate),6,2) as int) and
		d=cast(substring(dbo.MiladiTOShamsi(@fromDate),9,2) as int)
		) --holiday
		insert into @result(holiday)values(@fromDate)
		set @fromDate=DATEADD(D,1,@fromDate)
	end
	-- Return the result of the function
	RETURN
end
GO
