February 11, 2014 at 9:34 am
Hello I'm relatively new to software development and I have a query regarding listing the weeks 1 - 52 where week 1 of the year starts on Monday the 6th of January for 2014
e.g
WeekOfYear MondayOfYear
1 2014-01-06
2 2014-01-13
3 2014-01-20
Business Rules requested by customer are:
1. Week 1 of the year is when the first Monday in January occurs.
2. Week 1 of the month starts with the first Monday in the month.
Would anyone have the code for this?
Kind regards
Clare
February 16, 2014 at 5:45 am
--first Monday of the month
SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(month, DATEDIFF(month, '19000101', GETDATE()), '19000101')-1) /7*7 + 7, '19000101');
--first Monday of the year
SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(year, DATEDIFF(year, '19000101', GETDATE()), '19000101')-1) /7*7 + 7, '19000101');
--last Monday of the year
SELECT DATEADD(day, DATEDIFF(day, '19000101',DATEADD(year, DATEDIFF(year, '18991231', GETDATE()), '18991231')) /7*7, '19000101');
___________________________
Do Not Optimize for Exceptions!
February 16, 2014 at 10:35 am
milos.radivojevic (2/16/2014)
--first Monday of the month
SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(month, DATEDIFF(month, '19000101', GETDATE()), '19000101')-1) /7*7 + 7, '19000101');
--first Monday of the year
SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(year, DATEDIFF(year, '19000101', GETDATE()), '19000101')-1) /7*7 + 7, '19000101');
--last Monday of the year
SELECT DATEADD(day, DATEDIFF(day, '19000101',DATEADD(year, DATEDIFF(year, '18991231', GETDATE()), '18991231')) /7*7, '19000101');
That's nice but do you have any code that actually does what the OP requested? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2014 at 11:08 am
That's nice but do you have any code that actually does what the OP requested? 😉
I guess that the problem was to identify the frist monday in a year and that the rest of the rows generation is more or less trivial. Anyway, here is a complete solution. It uses the function dbo.GetNums to enumarate the weeks of the year, but it could be easily modified to use any kind of numbering sequnce for the same purpose.
The definition of the function dbo.GetNums can be found here: http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
And here is the code genearting the requested list for the year specified in a local variable.
DECLARE @Year AS INT = 2014;
WITH cte AS
(
SELECT
n AS WeekOfYear,
DATEADD(day, DATEDIFF(day, '19000101',
DATEADD(year, DATEDIFF(year, '19000101', CAST(@Year AS VARCHAR(4)) + '0101'), '19000101')-1) /7*7 + 7*n, '19000101')
AS MondayOfYear
FROM dbo.GetNums(53)
)
SELECT WeekOfYear, CAST(MondayOfYear AS DATE) AS MondayOfYear
FROM cte
WHERE YEAR(MondayOfYear) = @Year;
___________________________
Do Not Optimize for Exceptions!
February 16, 2014 at 1:19 pm
milos.radivojevic (2/16/2014)
I guess that the problem was to identify the frist monday in a year and that the rest of the rows generation is more or less trivial.
Agreed but use of a Tally or Numbers Table or function isn't trivial for a lot of folks. I've interviewed about 50 people in the last 5 years (20 in the last year alone), and only 1 of them knew what one was but didn't know how to use it. Roughly a dozen of those folks couldn't even write a WHILE loop.
Anyway, thanks for posting your solution and the link to Ben-Gan's function I'm sure that'll help the OP and others a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2014 at 2:16 pm
CLARE.FAUGHEY (2/11/2014)
Hello I'm relatively new to software development and I have a query regarding listing the weeks 1 - 52 where week 1 of the year starts on Monday the 6th of January for 2014e.g
WeekOfYear MondayOfYear
1 2014-01-06
2 2014-01-13
3 2014-01-20
Business Rules requested by customer are:
1. Week 1 of the year is when the first Monday in January occurs.
2. Week 1 of the month starts with the first Monday in the month.
Would anyone have the code for this?
Kind regards
Clare
You just have to know that, especially based on Item #2 in the business rules above, that you and your customer are going to want a bit more than just those two columns of data sometime in the near future.
To keep from having to pull your hair out on this "fiscal year" requirement every time you turn around, I recommend making a "Calendar Table" similar to the following. I also recommend that you ask the customer if they really want what is known as an "ISO Calendar". Calendars that start on the first Monday of the year are similar but they are definitely NOT ISO calendars.
/**********************************************************************************************************************
Purpose:
Create a Calendar Table based on the first Monday of each year. Note that this is NOT an ISO based Calendar Table
where the first week of the year is the week that contains the first Thursday of the year.
Note also that this table does NOT account for holidays or week days, either, because I don't know what holidays
the customer observes.
Revision History:
Rev 00 - 16 Feb 2014 - Jeff Moden - Initial release.
**********************************************************************************************************************/
WITH
cteGenDates AS
( --=== Create a list of calendar dates from 1900-01-01 through 2099-12-31.
SELECT TOP (DATEDIFF(dd,'1900','2100'))
CalendarDate = ISNULL(DATEADD(dd,ROW_NUMBER()OVER(ORDER BY (SELECT NULL))-1,'1900'),0)
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
),
cteFiscal1 AS
( --=== Calculate the fiscal year and the week number of the fiscal year.
SELECT CalendarDate
,FiscalYear = DATEPART(yy,DATEADD(dd,DATEDIFF(dd,'1753',CalendarDate)/7*7,'1753'))
,FiscalYearWeek = (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'1753',CalendarDate)/7*7,'1753'))+6)/7
FROM cteGenDates
),
cteFiscal2 AS
( --=== Calculate the first Monday of the year as the start of the fiscal year.
SELECT *
,FiscalYearStartDate = DATEADD(dd,DATEDIFF(dd,'1753',DATEADD(mm,DATEDIFF(mm,'1753',CAST(FiscalYear AS VARCHAR(10))),'1753')+6)/7*7,'17530101')
FROM cteFiscal1
),
cteFiscal3 AS
( --=== Calculate the start date of each fiscal week
SELECT *
,FiscalWeekStartDate = DATEADD(dd,(FiscalYearWeek-1)*7,FiscalYearStartDate)
FROM cteFiscal2
),
cteFiscal4 AS
( --=== Calculate the start date of each fiscal month
SELECT *
,FiscalMonthStartDate = DATEADD(dd,DATEDIFF(dd,'1753',DATEADD(mm,DATEDIFF(mm,'1753',FiscalWeekStartDate),'1753')+6)/7*7,'1753')
FROM cteFiscal3
) --=== Calculate some other useful columns and create a table from it all (JBM20140216)
SELECT CalendarDate
,NextDate = CalendarDate+1
,DOW3 = LEFT(DATENAME(dw,CalendarDate),3)
,FiscalYearStartDate
,FiscalYear
,FiscalYearWeek
,FiscalMonthStartDate
,FiscalMonth = DATEPART(mm,FiscalWeekStartDate)
,FiscalMonthWeek = DATEDIFF(dd,FiscalMonthStartDate,FiscalWeekStartDate)/7+1
,FiscalWeekStartDate
INTO dbo.FiscalCalendar
FROM cteFiscal4
;
--===== Add the expected PK
ALTER TABLE dbo.FiscalCalendar
ADD CONSTRAINT PK_FiscalCalendar
PRIMARY KEY CLUSTERED (CalendarDate)
WITH FILLFACTOR = 100
;
--===== Display the contents of the new table
SELECT * FROM dbo.FiscalCalendar ORDER BY CalendarDate
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2014 at 11:12 pm
@Clare,
Are you all set one way or the other now?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2014 at 2:09 am
Thank you so much Jeff yes I am all set, what you posted works fantastic and I really appreciate your response. And also Milos too, figuring out how all the dates are defined is no joke.
Kind regards
Clare
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply