Hello, I want to write a stored procedure in SQL wich returns last 12 weeks list (including current week) with start date. e.g. 01: 04 Jan 10 53: 28 Dec 09 52: 21 Dec 09 51: 14 Dec 09 : : P.S. My first day of week is Monday so first week of year 2010 will start from 4 Jan 2010 and week starting from 28 Dec will be wk 53. I am doing something like this
ALTER PROCEDURE [dbo].[GetWeekNumberList]
@IncludeCurrentWeek BIT = NULL
AS
BEGIN
SET DATEFIRST 1;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #tempWeekList(weekDate DATETIME, weekLabel VARCHAR(25))
DECLARE @Counter INT,
@StartDate DATETIME,
@EndDate DATETIME
IF @IncludeCurrentWeek IS NOT NULL AND @IncludeCurrentWeek = 1
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, GETDATE()) \* -1) + 1, GETDATE())
END
ELSE
BEGIN
SET @StartDate = DATEADD(dd, (DATEPART(dw, DATEADD(dd, -7, GETDATE())) \* -1) + 1, DATEADD(dd, -7, GETDATE()))
END
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = 1
WHILE @Counter <= 12
BEGIN
INSERT INTO #tempWeekList
VALUES (@EndDate, CAST(DATEPART(wk, @EndDate) AS VARCHAR) + ': ' + CONVERT(VARCHAR, @StartDate, 6))
SET @StartDate = DATEADD(dd, -7, @StartDate)
SET @EndDate = DATEADD(dd, 4, @StartDate)
SET @Counter = @Counter + 1
END
SELECT \* FROM #tempWeekList
DROP TABLE #tempWeekList
END
I am not able to get the 53 as last week. How to do it? Any idea or help will be very useful to me. Thanks.