I have a function that I developed to return the week number of a date passed in from a query; the week numbers are custom to us and not any fixed standard week ranges. The function takes the date passed in and checks it against a range of dates created in a temp table within function, these date ranges are determined by a week number associated to a certain range of dates, it then returns the associated week number of the date.
Initially I was looking to create the week number look up range using the previous Saturday date as reference - most recent from date function is run - as the date to count 4 weeks back and create the look up. This worked fine but I now want to modify this and make it more dynamic.
I want to be able to pass in the same date as before but also want to pass in the number of look up weeks I want and also want to pass in the day name of the day to be used as last day in look up. So for example I pass in (Column.date,5,'Saturday')
This would give me 5 rows in temp table with range like this:
Week first_date Last_Date
==== ========== ==========
1 22/06/2014 28/06/2014
2 29/06/2014 05/07/2014
3 06/07/2014 12/07/2014
4 13/07/2014 19/07/2014
5 20/07/2014 26/07/2014
Sunday - Saturday being the range of dates, where the 26/07/2014 date is the last saturday before the date the function was run. If I was to change the day passed in to 'Sunday' then would obviously want range to change to Monday to Sunday date range:
Week first_date Last_Date
==== ========== ==========
1 23/06/2014 29/06/2014
2 30/06/2014 06/07/2014
3 07/07/2014 13/07/2014
4 14/07/2014 20/07/2014
5 21/07/2014 27/07/2014
The current function is fixed to only return range for up to last Saturday but am not sure how to make a dynamic date statement that works off of new parameters passed into function, which I have declared in the function but am not using yet. Function is below:
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@NoWeeksforRange INT, -- New variable not yet used
@dayName Varchar(30) -- New variable not yet used
)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @weeknumber INT
--declare other variables here
DECLARE @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME)
DECLARE @lastsaturdaydate DATETIME
--Set last day date value using dayname value passed in.
set @lastsaturdaydate = DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')
-- Add the T-SQL statements to compute the return value here
INSERT INTO @mytemptablevariable
values (1, DATEADD(DAY, -27, @lastsaturdaydate), DATEADD(DAY, -21, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (2, DATEADD(DAY, -20, @lastsaturdaydate), DATEADD(DAY, -14, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (3, DATEADD(DAY, -13, @lastsaturdaydate), DATEADD(DAY, -07, @lastsaturdaydate))
INSERT INTO @mytemptablevariable
values (4, DATEADD(DAY, -6, @lastsaturdaydate), @lastsaturdaydate)
select @weeknumber = weeknumber
from @mytemptablevariable
where @refdate >= fromdate AND @refdate <= todate
-- Return the result of the function
RETURN @weeknumber
END
Any feedback on how to determine work I need to do to make this more dynamic would be much appreciated.
Many thanks
Andrew
Edit:
This is the function that was create for this:
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@day_parameter varchar(4)
)
--Define what data type the function returns
RETURNS INT
AS
BEGIN
--Declare variables to be used in function
DECLARE @weeknumber INT
DECLARE @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME)
DECLARE @i INT
DECLARE @result_date as datetime
DECLARE @offset_day as int
DECLARE @weekfromdatedays INT
DECLARE @weektodatedays INT
DECLARE @today AS datetime
--Set any predefined variable values
--Make days number negative value
--Multiply weeks variable number by 7 to get number of days to end of first week in range
--Multiply weeks variable number by 7 and then substract 7 to get number of days to start of first week in range
set @today = DateAdd(Day, Datediff(Day,0, GetDate()), 0)
set @i = 1
set @weekfromdatedays = -(@Weeks * 7)
set @weektodatedays = -((@Weeks * 7)-7)
--Set the integer value of the offest day variable
SET @offset_day = CASE @day_parameter WHEN 'Sun' THEN 1
WHEN 'Mon' THEN 2
WHEN 'Tue' THEN 3
WHEN 'Wed' THEN 4
WHEN 'Thu' THEN 5
WHEN 'Fri' THEN 6
WHEN 'Sat' THEN 7
END
--Using offset day work out the last day date value to take the range of weeks up to
SET @result_date = DATEADD(d,0-CASE WHEN DATEPART(weekday,@today) = @offset_day THEN 7
WHEN DATEPART(weekday,@today) > @offset_day THEN DATEPART(weekday,@today) - @offset_day
ELSE DATEPART(weekday,@today) - @offset_day + 7
END,
@today)
-- Run while loop to populate table
while (@i <= @Weeks)
begin
INSERT INTO @mytemptablevariable
values (@i, DATEADD(DAY, @weekfromdatedays, DATEADD(DAY, 1, @result_date)), DATEADD(DAY, @weektodatedays, @result_date))
set @i = @i+1
set @weekfromdatedays = @weekfromdatedays + 7
set @weektodatedays = @weektodatedays + 7
end
-- Using values in week range temp table, select what week number the passed in date variable is in
select @weeknumber = weeknumber
from @mytemptablevariable
where @refdate >= fromdate AND @refdate <= todate
-- Return the result of the function to the query or calling statement
RETURN @weeknumber
END
