September 11, 2014 at 10:56 am
Hi All,
I'm pretty new to working with dates. I have a requirement of finding the Start Date and End Date of each week in a specified month. Sunday is the start of the week and Saturday is the end of the week. I found a piece of code that calculates the business start/end date that I have been trying to figure out. It's close but for some reason it skips over Sunday. Any pointers would really be appreciated.
Thanks,
Mark
CREATE FUNCTION GetWeeksForMonth
(
@dt datetime-- Pass any date value of month for which you need week info
)
RETURNS @RESULTS TABLE
(
WeekNo int,
WeekStart datetime,
WeekEnd datetime
)
AS
BEGIN
;With Monthdates
AS
(
SELECT DATEADD(mm,DATEDIFF(mm,0,@Dt),0) AS dt
UNION ALL
SELECT Dt +1
FROM MonthDates
WHERE dt< DATEADD(mm,DATEDIFF(mm,0,@dt)+1,0)
)
INSERT @RESULTS
SELECT ROW_NUMBER() OVER (ORDER BY DATEDIFF(dd,0,dt)/7) AS WeekNo,
MIN(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 6 THEN dt END) AS StartDt,
MAX(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 6 THEN dt END) AS EndDt
FROM Monthdates
GROUP BY DATEDIFF(dd,0,dt)/7
HAVING SUM(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 7 THEN 1 ELSE 0 END) > 0
RETURN
END
Called with: select * from GetWeeksForMonth('08/01/2014')
Produces:
WeekNoWeekStart WeekEnd
12014-08-01 00:00:00.0002014-08-02 00:00:00.000
22014-08-04 00:00:00.0002014-08-09 00:00:00.000
32014-08-11 00:00:00.0002014-08-16 00:00:00.000
42014-08-18 00:00:00.0002014-08-23 00:00:00.000
52014-08-25 00:00:00.0002014-08-30 00:00:00.000
62014-09-01 00:00:00.0002014-09-01 00:00:00.000
Trying to get:
WeekNoWeekStart WeekEnd
12014-08-01 00:00:00.0002014-08-02 00:00:00.000
22014-08-03 00:00:00.0002014-08-09 00:00:00.000
32014-08-10 00:00:00.0002014-08-16 00:00:00.000
42014-08-17 00:00:00.0002014-08-23 00:00:00.000
52014-08-24 00:00:00.0002014-08-30 00:00:00.000
62014-09-31 00:00:00.0002014-08-31 00:00:00.000
September 11, 2014 at 11:52 am
What are the rules for the partial weeks at the start and the end of each month?
--Jeff Moden
September 11, 2014 at 11:56 am
I'm trying to keep everything in the specified month. In Aug 14 for example it would return only 8/1 and 8/2 (Sat) for the first week and only 8/31 for the last week in the month. Did I miss any other rules?
Thanks,
Mark
September 12, 2014 at 11:00 am
The following should do it (see attached). I made sure that it's a high performance iTVF instead of an mTVF. I also got rid of the resource hungry, performance robbing recursive CTE that counts and added a column to return the number of days in each week. Of course, you can remove that column if you'd prefer it not to be there.
--Jeff Moden
September 12, 2014 at 12:07 pm
Jeff,
Thanks so much for the help! That worked perfectly. I'll dig in and understand dates a little better.
Mark
September 12, 2014 at 1:33 pm
mark.stewart 12691 (9/12/2014)
Jeff,Thanks so much for the help! That worked perfectly. I'll dig in and understand dates a little better.
Mark
Very cool. Thanks for the feedback, Mark. If you have any questions about the code, please don't hesitate to ask them.
--Jeff Moden
September 13, 2014 at 12:32 pm
Jeff Moden (9/12/2014)
The following should do it (see attached). I made sure that it's a high performance iTVF instead of an mTVF. I also got rid of the resource hungry, performance robbing recursive CTE that counts and added a column to return the number of days in each week. Of course, you can remove that column if you'd prefer it not to be there.
Nice one Jeff, is there a spackle coming?
😎
September 15, 2014 at 4:38 pm
Eirikur Eiriksson (9/13/2014)
Jeff Moden (9/12/2014)
The following should do it (see attached). I made sure that it's a high performance iTVF instead of an mTVF. I also got rid of the resource hungry, performance robbing recursive CTE that counts and added a column to return the number of days in each week. Of course, you can remove that column if you'd prefer it not to be there.Nice one Jeff, is there a spackle coming?
😎
Thanks, Eirikur, especially for the idea of Spackling this. I suppose I could expand on it a bit to include multiple months to make it more useful. Actually, come to think of it and except for a modernization of technique, it's mostly written from a post I did on another site back in 2007. Add a week number to each row and it's mostly done.
https://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1173&lngWId=5
--Jeff Moden
September 15, 2014 at 10:51 pm
Jeff Moden (9/15/2014)
Eirikur Eiriksson (9/13/2014)
Jeff Moden (9/12/2014)
The following should do it (see attached). I made sure that it's a high performance iTVF instead of an mTVF. I also got rid of the resource hungry, performance robbing recursive CTE that counts and added a column to return the number of days in each week. Of course, you can remove that column if you'd prefer it not to be there.Nice one Jeff, is there a spackle coming?
😎
Thanks, Eirikur, especially for the idea of Spackling this. I suppose I could expand on it a bit to include multiple months to make it more useful. Actually, come to think of it and except for a modernization of technique, it's mostly written from a post I did on another site back in 2007. Add a week number to each row and it's mostly done.
https://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1173&lngWId=5
My thought is that this elegant function can be used when more elaborate methods such as a calendar table would be an utter overkill, a perfect Spackle subject.
😎
September 17, 2014 at 8:46 am
When I ran the following example for FUNCTION dbo.GetWeeksForMonth
--===== Return weeks in current month
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(GETDATE())
I got the following error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Can anyone tell me what I am missing here?
Thanks
September 17, 2014 at 12:46 pm
hmbtx (9/17/2014)
When I ran the following example for FUNCTION dbo.GetWeeksForMonth--===== Return weeks in current month
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(GETDATE())
I got the following error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Can anyone tell me what I am missing here?
Thanks
That's from the second usage example in the code. Are you sure that you highlighted the whole usage example and didn't leave out the last parenthesis because it runs just fin on all of my systems. And, to be sure, you are using SQL Server, correct?
--Jeff Moden
September 17, 2014 at 1:10 pm
Jeff:
Thank you for your reply.
I just tried it again and I am not leaving out any characters.
I get the same error.
I am running it SQL Server 2008R2.
Thanks
Howard
September 17, 2014 at 6:16 pm
hmbtx (9/17/2014)
Jeff:Thank you for your reply.
I just tried it again and I am not leaving out any characters.
I get the same error.
I am running it SQL Server 2008R2.
Thanks
Howard
Apologies on this, Howard. I can't troubleshoot the problem because I can't duplicate it on any of my machines. I even copied the code you posted and I still can't get anything except the correct answer.
--Jeff Moden
September 17, 2014 at 6:59 pm
Howard,
I'm on SQL Server 2012 so not sure if this helps or not. I've run Jeff's code through several month's and confirmed his logic is solid. Can you post your Function View and I'll try it on one of our 2008 installs?
Jeff, again thanks for your help! I've been digging into your code and am slowly understanding DATEADD. Working on CROSS APPLY now 🙂
Cheers!
Mark
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(GETDATE())
Produces:
12014-09-01 00:00:00.0002014-09-06 00:00:00.0006
22014-09-07 00:00:00.0002014-09-13 00:00:00.0007
32014-09-14 00:00:00.0002014-09-20 00:00:00.0007
42014-09-21 00:00:00.0002014-09-27 00:00:00.0007
52014-09-28 00:00:00.0002014-09-30 00:00:00.0003
The Table-valued Function looks like:
USE [xxxxxxYour Data base Name]
GO
/****** Object: UserDefinedFunction [dbo].[GetWeeksForMonth] Script Date: 9/17/2014 7:47:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetWeeksForMonth]
/**********************************************************************************************************************
Purpose:
Given any legal date for the DATETIME datatype, return the dates for the start and end of the week for all weeks in
the month represented by the given date while observing the date boundaries of the month.
Programmer's Notes:
1. The first date of the month is always returned as the first date of WeekStart.
2. The last date of the month is always returned as the last date of WeekEnd.
3. Intermediate rows return the start and end dates for a whole week that starts on Sunday.
4. ANY legal date/time allowed in the DATETIME datatype may be used.
Example Usage:
--===== Generic Syntax (@dt is DATETIME)
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(@dt)
;
--===== Return weeks in current month
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(GETDATE())
;
--===== Return weeks for August, 2015
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth('2014-08-15')
;
Revision History:
REV 00 - 12 Sep 2014 - Jeff Moden - Initial creation.
**********************************************************************************************************************/
--===== Define the I/O for this function
(
@dt datetime-- Pass any date value of month for which you need week info
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Return the weeks for the month of the given date.
WITH
cteMonth AS
(--==== Setup some constants to make other parts of the code easier.
SELECT FirstDay = DATEADD(mm,DATEDIFF(mm, 0,@dt), 0)
,LastDay = DATEADD(mm,DATEDIFF(mm,-1,@dt),-1)
,FirstSunday = DATEADD(dd,DATEDIFF(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@dt),0))/7*7,-1)
),
cteWeeks AS
(--==== Create all of the week dates for a 6 week period. This is much faster than generating all dates for the whole month.
SELECT WeekNo = t.N
,WeekStart = CASE WHEN DATEADD(wk,t.N-1,m.FirstSunday) < m.FirstDay THEN m.FirstDay ELSE DATEADD(wk,t.N-1,m.FirstSunday) END
,WeekEnd = CASE WHEN DATEADD(wk,t.N,m.FirstSunday)-1 > m.LastDay THEN m.LastDay ELSE DATEADD(wk,t.N,m.FirstSunday)-1 END
FROM cteMonth m
CROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t (N)
)--==== This select only the correct weeks.
SELECT WeekNo, WeekStart, WeekEnd
,DaysInWeek = DATEDIFF(dd,WeekStart,WeekEnd)+1
FROM cteWeeks
WHERE WeekStart <= WeekEnd
;
September 17, 2014 at 8:02 pm
Thanks, Mark,
DATEDIFF is the tough one for most folks because they frequently don't understand that it works on the BOUNDARIES of the datepart. For example, a DATEDIFF(yy,'2014-12-31 23:59:59.997','2015-01-01 00:00:00.000') will return a "1" even though the two dates are only 3ms apart.
As for CROSS APPLY, it's the same as a correlated subquery in the SELECT list except that it can return more than one value for each row without blowing up. Paul White wrote really good articles on the subject. Here are the links to those articles...
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply