Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-19 : 08:42:42
|
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
285 Posts |
Posted - 2013-03-19 : 09:41:25
|
Post your code so that people can get some ideas to help |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-19 : 10:14:15
|
Here's a start:USE [MyDatabase]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CreateListOfWeeks]( @StartTime date, @EndTime date)ASBEGIN ;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. |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-19 : 10:58:10
|
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. |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-03-20 : 17:13:05
|
--code below will return the first Thurs on or before the specified date value.--for example: 20130320 returns 20130314; 20130321 returns 20130321.DECLARE @StartTime dateSET @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 |
|
|
KilpAr
Yak Posting Veteran
80 Posts |
Posted - 2013-03-21 : 09:38:42
|
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 is52/200953/20091/2010now if I leave this offset totally out i.e. ask for the weeks/years of Dec 26th and Jan 2nd, I get result of52/200953/2010which clearly1) misses a weekand2) has the year of the later one virtually incorrect due to day occurring on the new yearIf 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. |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-03-21 : 10:54:57
|
"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. |
|
|
|
|
|
|
|