42 lines
894 B
Transact-SQL
42 lines
894 B
Transact-SQL
Use [EnVisage]
|
|
|
|
IF EXISTS (SELECT *
|
|
FROM sys.objects
|
|
WHERE object_id = OBJECT_ID(N'[dbo].[GetFCWeekStartForDate]')
|
|
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
|
|
DROP FUNCTION [dbo].[GetFCWeekStartForDate]
|
|
GO
|
|
|
|
CREATE FUNCTION GetFCWeekStartForDate (@dt date)
|
|
RETURNS date
|
|
AS
|
|
BEGIN
|
|
DECLARE @ret date
|
|
DECLARE @minDate date
|
|
DECLARE @weekStart date
|
|
DECLARE @weekEnd date
|
|
|
|
select @minDate = MIN(StartDate) from FiscalCalendar
|
|
where ([Type] = 0)
|
|
|
|
if (@dt <= @minDate)
|
|
SET @ret = @minDate
|
|
else
|
|
begin
|
|
select top 1 @weekStart = StartDate, @weekEnd = EndDate from FiscalCalendar
|
|
where ([Type] = 0) and (StartDate <= @dt) and (EndDate >= @dt)
|
|
|
|
if not (@weekStart is null)
|
|
begin
|
|
if (@dt > @weekStart)
|
|
set @ret = DATEADD(day, 1, @weekEnd)
|
|
else
|
|
set @ret = @weekStart
|
|
end
|
|
else
|
|
set @ret = @dt
|
|
end
|
|
|
|
RETURN @ret
|
|
END
|
|
GO |