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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looking for a week lister

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
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-03-19 : 10:14:15
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.
Go to Top of Page

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.
Go to Top of Page

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 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
Go to Top of Page

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 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
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.
Go to Top of Page
   

- Advertisement -