July 4, 2012 at 12:23 am
Dear All,
I need your help on generating the list of week numbers between two dates with Monday as the week start day.
for instance: need list of week numbers between 05-07-2012 and 26-07-2012 with Monday as the week start day.
Please kindly help me providing with the suitable sql statement.
Thanks in advance,
Ram
July 4, 2012 at 1:39 am
I would strongly recommend using a calendar table which you can link on to get the results you need.
I will try and find the links to some articles on this for you.
Edit. This is the calendar table that I use.
July 4, 2012 at 2:04 am
If for some reason you can't use a calendar table, you could do something like this: -
DECLARE @startDate DATE = '2012-07-05', @endDate DATE = '2012-07-26';
WITH t1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)), -- 2 Rows
t2(N) AS (SELECT 1 FROM t1 x, t1 y), -- 4 Rows (2*2)
t3(N) AS (SELECT 1 FROM t2 x, t2 y), -- 16 Rows (4*4)
t4(N) AS (SELECT 1 FROM t3 x, t3 y), -- 256 Rows (16*16)
t5(N) AS (SELECT 1 FROM t4 x, t4 y), -- 65,536 Rows (256*256)
tally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATEDIFF(WEEK, @startDate, @endDate)) -- Limit the result-set straight up front
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM t5 x, t5 y) -- 4,294,967,296 Rows (65,536*65,536)
SELECT N AS WeekNo,
CASE WHEN DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-02', DATEADD(WEEK,N,@startDate)), '2012-01-02') < @startDate
THEN @startDate
ELSE DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-02', DATEADD(WEEK,N,@startDate)), '2012-01-02') END AS weekStart,
CASE WHEN DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', DATEADD(WEEK,N+1,@startDate)), '2012-01-08') > @endDate
THEN @endDate
ELSE DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', DATEADD(WEEK,N+1,@startDate)), '2012-01-08') END AS weekEnd
FROM tally;
Returns: -
WeekNo weekStart weekEnd
-------------------- ----------------------- -----------------------
0 2012-07-05 00:00:00.000 2012-07-08 00:00:00.000
1 2012-07-09 00:00:00.000 2012-07-15 00:00:00.000
2 2012-07-16 00:00:00.000 2012-07-22 00:00:00.000
3 2012-07-23 00:00:00.000 2012-07-26 00:00:00.000
I've assumed that you don't want the weekStart to be before the "start date" and the weekEnd to be after the "end date", adjust if I've assumed incorrectly.
July 4, 2012 at 10:53 pm
An alternative to a Calendar table, is to use a FUNCTION to generate the calendar.
Like the one Jeff Moden gifted me with here: http://www.sqlservercentral.com/Forums/Topic1320527-392-2.aspx#bm1322829
Be sure to use his and not the one I posted as his is much speedier.
Faster than a speeding locomotive. Able to leap tall buildings with a single bound. It's a bird, its a plane, no its Jeff Moden's GenerateCalendar function!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 5, 2012 at 12:15 am
Here is another calendar table function.
Date Table Function F_TABLE_DATE
July 5, 2012 at 12:18 am
Worthy of mention, if you do opt to add a Calendar table, either of the proposed FUNCTIONs will make short work of populating it.
Any way you choose to accept it, this is the approach to use to solve your original question.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 6, 2012 at 7:06 am
I could not clearly understand if you asking for the number of weeks between the two dates of you wanted the actual dates.
However, here is something you can use to calculate the number of weeks between two dates
select DATEPART(wk,26-07-2012) - DATEPART(wk,05-07-2012)
July 8, 2012 at 10:10 pm
I've finally sorted it out by writing the stored procedure as given below:
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Counter INT
DECLARE @NextDate DATE
DECLARE @WeekNo INT
DECLARE @MyTable TABLE
(
WeekNo int
)
SET @Counter = 0
SET @NextDate = DATEADD(dd,@Counter + 1,@StartDate)
WHILE @NextDate < @EndDate
BEGIN
IF @NextDate > @StartDate
BEGIN
SET @Counter = @Counter + 1
END
SET @NextDate = DATEADD(dd,@Counter,@StartDate)
SET @WeekNo = DATEPART(wk, @NextDate)
IF ((select count(1) from @MyTable where WeekNo = @WeekNo) = 0)
BEGIN
insert into @MyTable values (@WeekNo)
END
END
SELECT * FROM @Mytable
END
Many thanks for those who all responded,
Ram
July 9, 2012 at 12:26 am
ramsai1973 (7/8/2012)
I've finally sorted it out by writing the stored procedure as given below:BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Counter INT
DECLARE @NextDate DATE
DECLARE @WeekNo INT
DECLARE @MyTable TABLE
(
WeekNo int
)
SET @Counter = 0
SET @NextDate = DATEADD(dd,@Counter + 1,@StartDate)
WHILE @NextDate < @EndDate
BEGIN
IF @NextDate > @StartDate
BEGIN
SET @Counter = @Counter + 1
END
SET @NextDate = DATEADD(dd,@Counter,@StartDate)
SET @WeekNo = DATEPART(wk, @NextDate)
IF ((select count(1) from @MyTable where WeekNo = @WeekNo) = 0)
BEGIN
insert into @MyTable values (@WeekNo)
END
END
SELECT * FROM @Mytable
END
Many thanks for those who all responded,
Ram
That's an excellent way to show that using the Calendar TABLE or FUNCTION is the desired approach:
-- All dates between the prior Monday and the end date
SELECT [Date], WkNo
FROM (SELECT DATEADD(week, DATEDIFF(week, 0, '2012-07-05'), 0)) a(FirstMon)
CROSS APPLY dbo.GenerateCalendar1(FirstMon, DATEDIFF(day, FirstMon, '2012-07-26')) b
-- Only the Mondays between start and end date
SELECT [Date], WkNo
FROM (SELECT DATEADD(week, DATEDIFF(week, 0, '2012-07-05'), 0)) a(FirstMon)
CROSS APPLY dbo.GenerateCalendar1(FirstMon, DATEDIFF(day, FirstMon, '2012-07-26')) b
WHERE [WkDName2] = 'MO'
Both use Jeff's GenerateCalendar1 FUNCTION that I pointed you to on my first post.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply