Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Code to list the Week Number of Year Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 2:04 AM
Points: 2, Visits: 14
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
Post #1540292
Posted Sunday, February 16, 2014 5:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 137, Visits: 576

--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!
Post #1541899
Posted Sunday, February 16, 2014 10:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541914
Posted Sunday, February 16, 2014 11:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 137, Visits: 576


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!
Post #1541916
Posted Sunday, February 16, 2014 1:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
milos.radivojevic (2/16/2014)
[quote]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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541926
Posted Sunday, February 16, 2014 2:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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 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


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541932
Posted Monday, February 17, 2014 11:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
@Clare,

Are you all set one way or the other now?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1542377
Posted Tuesday, February 18, 2014 2:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 2:04 AM
Points: 2, Visits: 14
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
Post #1542423
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse