SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looking for a week lister
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/19/2013 :  08:42:42  Show Profile  Reply with Quote
I have seen several "too specific" listers for weeks but I'm looking for a stored procedure that takes two parameters, startdate and enddate, and returns an inclusive list of weeks between. I have been able to do this otherwise, but I have problems with weeks 1 and 53 and having the year correct. For example, 31.12.2012 was at week 1/2013 and 1.1.2010 was at week 53/2009 but I have problems getting them to work at the same time.

namman
Constraint Violating Yak Guru

USA
272 Posts

Posted - 03/19/2013 :  09:41:25  Show Profile  Reply with Quote
Post your code so that people can get some ideas to help
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/19/2013 :  10:14:15  Show Profile  Reply with Quote
Here's a start:


USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateListOfWeeks](
	@StartTime		date,
	@EndTime		date
)
AS
BEGIN
	;with WeeklyCalendar 
	AS 
	(	
		SELECT cast(@StartTime AS datetime) AS dt 
		Union ALL 
		SELECT dateadd(DAY, 7, dt) 
		FROM WeeklyCalendar 
		WHERE dateadd(WEEK, 1, dt) <= @EndTime
	)
	SELECT convert(varchar(12),convert(varchar(2),DATEPART(ISO_WEEK, dt)) + '/' + convert(varchar(4),DATEPART(YEAR, dt))) as [Week/Year]
	FROM WeeklyCalendar 
	WHERE dt BETWEEN @StartTime AND @EndTime
	ORDER BY dt DESC
	OPTION (maxrecursion 0)
END


Calling it with:
exec [MyDatabase].[dbo].[CreateListOfWeeks] @StartTime = '2009-12-27', @EndTime = '2010-01-04'

returns 53/2010 and 52/2009, which of 52/2009 is correct and 53/2010 is incorrect.


edit. I could use there before ;with these two lines:
SET @StartTime = (select DATEADD(DAY,-(DATEPART(dw, @StartTime)-2),@StartTime))
SET @EndTime = (select DATEADD(DAY,-(DATEPART(dw, @EndTime)-2),@EndTime))
to correct them as mondays, but it will returns in some cases that week 53 with a year that is one too much. Like this year 31.12. was Monday and it was week 1, then it check 31.12. was 2012 and thus returns 1/2012, although it should, of course, be 1/2013.

Edited by - KilpAr on 03/19/2013 10:20:05
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/19/2013 :  10:58:10  Show Profile  Reply with Quote
Got it. The key was to create an offset integer to set the queried dates to be the Thursday of the particular week, which was done with:

DECLARE @dayoffset INT;

IF (DATEPART(dw, @StartTime)) = 1
SET @dayoffset = -3;
IF (DATEPART(dw, @StartTime)) = 2
SET @dayoffset = 3;
IF (DATEPART(dw, @StartTime)) = 3
SET @dayoffset = 2;
IF (DATEPART(dw, @StartTime)) = 4
SET @dayoffset = 1;
IF (DATEPART(dw, @StartTime)) = 5
SET @dayoffset = 0;
IF (DATEPART(dw, @StartTime)) = 6
SET @dayoffset = -1;
IF (DATEPART(dw, @StartTime)) = 7
SET @dayoffset = -2;

SET @StartTime = (select DATEADD(DAY,@dayoffset,@StartTime))
SET @EndTime = (select DATEADD(DAY,@dayoffset,@EndTime))

Now if someone wants to tell me how I make that big if cluster into something smarter, that would be nice.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 03/20/2013 :  17:13:05  Show Profile  Reply with Quote
--code below will return the first Thurs on or before the specified date value.
--for example: 20130320 returns 20130314; 20130321 returns 20130321.


DECLARE @StartTime date
SET @StartTime = '2009-12-27'
SELECT
    DATEADD(DAY, DATEDIFF(DAY, '19000104', @StartTime) / 7 * 7, '19000104') --'19000104'=Thurs

--P.S.: this codes always works, for any date or language setting

Edited by - ScottPletcher on 03/20/2013 17:14:49
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/21/2013 :  09:38:42  Show Profile  Reply with Quote
This is otherwise good, but I need to get the Thursday of the given week. I think in USA it goes somehow else, but here in Finland the week numbers goes so that depending on the new year week Thursday (I'll call this NYWThu here, pointing to the Thursday that occurs on the week of New Year) i.e. how many days is on that given week, there either is Week 53 (in case NYWThu is part of old year i.e. there's more days from old than new year that week - remember that week begins on Monday, not on Sunday, so Thursday is day #4 in the week) or then there is not week 53 at all, in which case the NYWThu is part of the new year.

So when I ask for a list of weeks between, say, Dec 26th 2009 and Jan 5th 2010, the correct answer is
52/2009
53/2009
1/2010

now if I leave this offset totally out i.e. ask for the weeks/years of Dec 26th and Jan 2nd, I get result of
52/2009
53/2010
which clearly
1) misses a week
and
2) has the year of the later one virtually incorrect due to day occurring on the new year

If I correct this like you offered so that I offset the start to be Dec 24th and the end to be Dec 31st, you can easily see that while it correct's the 2nd week to be the correct 53/2009, I would miss still that 1/2010.

So my best solution - and one that seems to work - is that I alter both start and end days to be the Thursday of those given weeks - whether it is +3 or -3 days or something in between.

Oh, also, my list is inclusive. If the start is Sunday and the end is Monday (next day), then answer is both of those two weeks.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
435 Posts

Posted - 03/21/2013 :  10:54:57  Show Profile  Reply with Quote
"that I alter both start and end days to be the Thursday of those given weeks"

That is what the code above does, always returns a Thurs date.

If you need to add 6 days to the starting/ending date as part of the calc, you can do that.

To me, once you have the starting date and ending date, the generation of individual weeks is trivial.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000