/*
Function created by this script:
	dbo.F_END_OF_WEEK( @DATE, @WEEK_START_DAY )
*/
if objectproperty(object_id('dbo.F_END_OF_WEEK'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_WEEK end
go
create function dbo.F_END_OF_WEEK
(
	@DATE			datetime,
	-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
	-- Thu = 5, Fri = 6, Sat = 7
	-- Default to Sunday
	@WEEK_START_DAY		int	= 1	
)
/*
Function: F_END_OF_WEEK
	Finds start of last day of week at 00:00:00.000
	for input datetime, @DAY, for a week that started
	on the day of week of @WEEK_START_DAY.
	Returns a null if the end of week date would
	be after 9999-12-31.
*/
returns		datetime
as
begin
declare	 @END_OF_WEEK_DATE	datetime
declare	 @FIRST_BOW		datetime
declare	 @LAST_EOW		datetime

-- Check for valid day of week, and return null if invalid
if not @WEEK_START_DAY between 1 and 7 return null

-- Find the last end of week for the passed day of week
select @LAST_EOW =convert(datetime,2958457+((@WEEK_START_DAY+6)%7))

-- Return null if end of week for date passed is after 9999-12-31
if @DATE > @LAST_EOW return null

-- Find the first valid beginning of week for the date passed.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))

-- If date is before the first beginning of week for the passed day of week
-- return the day before the first beginning of week
if @DATE < @FIRST_BOW
	begin
	set @END_OF_WEEK_DATE = dateadd(dd,-1,@FIRST_BOW)
	return @END_OF_WEEK_DATE
	end

-- Find end of week for the normal case as 6 days after the beginning of week
select @END_OF_WEEK_DATE = 
	dateadd(dd,((datediff(dd,@FIRST_BOW,@DATE)/7)*7)+6,@FIRST_BOW)

return @END_OF_WEEK_DATE

end
go




/*
Start of test script
*/

select
	[DATE] = convert(varchar(10),a.DT,121),
	--WEEK_START_DAY = convert(varchar(2),b.number),
	FUNC_DW =
		case b.number
		when 1 then 'Sun'
		when 2 then 'Mon'
		when 3 then 'Tue'
		when 4 then 'Wed'
		when 5 then 'Thu'
		when 6 then 'Fri'
		when 7 then 'Sat'
		else null
		end,
	START_OF_WEEK =
	convert(varchar(10),dbo.F_START_OF_WEEK( a.DT, b.number ),121),
	START_DW = 
	left(datename(dw,dbo.F_START_OF_WEEK( a.DT, b.number )),3),
	END_OF_WEEK =
	convert(varchar(10),dbo.F_END_OF_WEEK( a.DT, b.number ),121),
	EOW_DW =
	left(datename(dw,dbo.F_END_OF_WEEK( a.DT, b.number )),3)
from
	(
	-- Get dates from end of datetime range
	select
		DT = dateadd(dd,a1.number,'9999-12-25')
	from
		dbo.F_TABLE_NUMBER_RANGE(0,6) a1
	union all
	-- Get some normal dates, +/- 10 day from current date
	select
		DT = dateadd(dd,a3.number,getdate())
	from
		dbo.F_TABLE_NUMBER_RANGE(-10,10) a3
	union all
	-- Get dates from beginning of datetime range
	select
		DT = dateadd(dd,a2.number,'17530101')
	from
		dbo.F_TABLE_NUMBER_RANGE(0,6) a2
	) a
	cross join
	dbo.F_TABLE_NUMBER_RANGE(1,7) b
order by
	a.dt,
	b.number

/*
End of test script
*/

select
	a.DT,
	EOWSun = dateadd(dd,((datediff(dd,-53690,a.DT)/7)*7)+6,-53690),
	EOWMon = dateadd(dd,((datediff(dd,-53689,a.DT)/7)*7)+6,-53689),
	EOWTue = dateadd(dd,((datediff(dd,-53688,a.DT)/7)*7)+6,-53688),
	EOWWed = dateadd(dd,((datediff(dd,-53687,a.DT)/7)*7)+6,-53687),
	EOWThu = dateadd(dd,((datediff(dd,-53686,a.DT)/7)*7)+6,-53686),
	EOWFri = dateadd(dd,((datediff(dd,-53685,a.DT)/7)*7)+6,-53685),
	EOWSat = dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)+6,-53684)
from
	( -- Generate test data dates
	select
		DT = dateadd(dd,aa.number,'20100101')
	from
		-- Number Table Function available here
		-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
		dbo.F_TABLE_NUMBER_RANGE(0,365) aa
	) a
order by
	a.DT

select
	a.DT,
	EOWSun = dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7)+6,'17530101'),
	EOWMon = dateadd(dd,((datediff(dd,'17530102',a.DT)/7)*7)+6,'17530102'),
	EOWTue = dateadd(dd,((datediff(dd,'17530103',a.DT)/7)*7)+6,'17530103'),
	EOWWed = dateadd(dd,((datediff(dd,'17530104',a.DT)/7)*7)+6,'17530104'),
	EOWThu = dateadd(dd,((datediff(dd,'17530105',a.DT)/7)*7)+6,'17530105'),
	EOWFri = dateadd(dd,((datediff(dd,'17530106',a.DT)/7)*7)+6,'17530106'),
	EOWSat = dateadd(dd,((datediff(dd,'17530107',a.DT)/7)*7)+6,'17530107')
from
	( -- Generate test data dates
	select
		DT = dateadd(dd,aa.number,'20100101')
	from
		-- Number Table Function available here
		-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
		dbo.F_TABLE_NUMBER_RANGE(0,365) aa
	) a
order by
	a.DT

select
	a.DT,
	EOWSun = dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7),'17530107'),
	EOWMon = dateadd(dd,((datediff(dd,'17530102',a.DT)/7)*7),'17530108'),
	EOWTue = dateadd(dd,((datediff(dd,'17530103',a.DT)/7)*7),'17530109'),
	EOWWed = dateadd(dd,((datediff(dd,'17530104',a.DT)/7)*7),'17530110'),
	EOWThu = dateadd(dd,((datediff(dd,'17530105',a.DT)/7)*7),'17530111'),
	EOWFri = dateadd(dd,((datediff(dd,'17530106',a.DT)/7)*7),'17530112'),
	EOWSat = dateadd(dd,((datediff(dd,'17530107',a.DT)/7)*7),'17530113')
from
	( -- Generate test data dates
	select
		DT = dateadd(dd,aa.number,'20100101')
	from
		-- Number Table Function available here
		-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
		dbo.F_TABLE_NUMBER_RANGE(0,365) aa
	) a
order by
	a.DT