Original SQL table-valued Function: Returns the number of custom weeks from the beginning of the current week

As always, a short blog post records a problem encountered during the development process. The original intention is to record some of their own idea, but also want to be able to help some coincidence encounter such a problem, the need for the beginning is because, to count the last n weeks of sales data.

Let's take a look at the effect of the execution function, such as SELECT * FROM [dbo]. [Fn_runweek] (-12)

The specific functions are as follows:

-- =============================================
-Call: SELECT * FROM [dbo]. [Fn_runweek] (-12)
--Create date:2015-01-14
--Description: Returns the N-week data from the beginning of the week (52 weeks a year)
-- =============================================
ALTER FUNCTION [dbo]. [Fn_runweek] (@num int=-12)
RETURNS @tab TABLE
(
Sale_year VARCHAR (10),
Xsdate VARCHAR (10),
Sort INT
)
As
BEGIN
DECLARE @week INT
SELECT @week =datepart (wk, GETDATE ())
DECLARE @i INT
SET @i=0
while (@i> @num)
BEGIN
INSERT into @tab
SELECT [Sale_year] = year (DATEADD (week, @i, GETDATE ())),
[Xsdate] = DATEPART (wk, (DATEADD (week, @i, GETDATE ())),
Sort = @i
SET @[email protected]
END
RETURN
END

if friends think that may be used later, welcome reprint, of course, can also recommend, thank you.

PS: This blog post is written using Windows Live Writer 2012, and the format effect may not be good.

Original SQL table-valued Function: Returns the number of custom weeks from the beginning of the current week