September 30, 2015 at 1:38 pm
I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:
WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5
Appreciate any help with this.
Helal
September 30, 2015 at 1:56 pm
Did you look in the Articles section for Calendar tables?
Todd Fifield's articles
http://www.sqlservercentral.com/articles/70482/[/url]
http://www.sqlservercentral.com/articles/T-SQL/70743/[/url]
http://www.sqlservercentral.com/articles/T-SQL/72345/[/url]
Then you'd just add your rule(s) to the Calendar table so that you could roll up your data any way you want.
September 30, 2015 at 2:43 pm
The WeekName logic contradicts itself. If:
8/30/2015 9/5/2015 = Sep_Week1
then shouldn't:
9/27/2015 10/3/2015 = Oct_Week1 rather than "Sep_Week5"
It's impossible for that week to be both Sep_Week5 and Oct_Week1 ... or is it??
Edit: You definitely won't need a calendar table, just a tally table, but you need to clarify the WeekName.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 30, 2015 at 2:44 pm
Here is a quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_SIZE INT = 365;
DECLARE @FIRST_DATE DATE = CONVERT(DATE,'20141231',112);
DECLARE @ZERO_DATE DATE = CONVERT(DATE,'19000101',112);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6)
,ALL_WEEKS_AND_DAYS AS
(
SELECT
NM.N
,DATEADD(DAY,NM.N,@FIRST_DATE) AS DATE_VALUE
,DENSE_RANK() OVER
(
ORDER BY (DATEDIFF(DAY,@ZERO_DATE,DATEADD(DAY,NM.N,@FIRST_DATE)) + 1 ) / 7
) AS SUNDAY_WEEK
FROM NUMS NM
)
SELECT
MIN(AWAD.DATE_VALUE) AS WeekStartdate
,MAX(AWAD.DATE_VALUE) AS WeekEndDate
,SUBSTRING(DATENAME(MM,MIN(AWAD.DATE_VALUE)),1,3) + '_Week' + CONVERT(VARCHAR(3),AWAD.SUNDAY_WEEK,0)
,(YEAR(MIN(AWAD.DATE_VALUE)) * 10000) + (DATEPART(MONTH,MIN(AWAD.DATE_VALUE)) * 100) + AWAD.SUNDAY_WEEK AS YYYYMMWW
, AWAD.SUNDAY_WEEK
FROM ALL_WEEKS_AND_DAYS AWAD
GROUP BY AWAD.SUNDAY_WEEK;
Results
WeekStartdate WeekEndDate YYYYMMWW SUNDAY_WEEK
------------- ----------- ----------- -------------------- --------------------
2015-01-01 2015-01-03 Jan_Week1 20150101 1
2015-01-04 2015-01-10 Jan_Week2 20150102 2
2015-01-11 2015-01-17 Jan_Week3 20150103 3
2015-01-18 2015-01-24 Jan_Week4 20150104 4
2015-01-25 2015-01-31 Jan_Week5 20150105 5
2015-02-01 2015-02-07 Feb_Week6 20150206 6
2015-02-08 2015-02-14 Feb_Week7 20150207 7
2015-02-15 2015-02-21 Feb_Week8 20150208 8
2015-02-22 2015-02-28 Feb_Week9 20150209 9
2015-03-01 2015-03-07 Mar_Week10 20150310 10
2015-03-08 2015-03-14 Mar_Week11 20150311 11
2015-03-15 2015-03-21 Mar_Week12 20150312 12
2015-03-22 2015-03-28 Mar_Week13 20150313 13
2015-03-29 2015-04-04 Mar_Week14 20150314 14
2015-04-05 2015-04-11 Apr_Week15 20150415 15
2015-04-12 2015-04-18 Apr_Week16 20150416 16
2015-04-19 2015-04-25 Apr_Week17 20150417 17
2015-04-26 2015-05-02 Apr_Week18 20150418 18
2015-05-03 2015-05-09 May_Week19 20150519 19
2015-05-10 2015-05-16 May_Week20 20150520 20
2015-05-17 2015-05-23 May_Week21 20150521 21
2015-05-24 2015-05-30 May_Week22 20150522 22
2015-05-31 2015-06-06 May_Week23 20150523 23
2015-06-07 2015-06-13 Jun_Week24 20150624 24
2015-06-14 2015-06-20 Jun_Week25 20150625 25
2015-06-21 2015-06-27 Jun_Week26 20150626 26
2015-06-28 2015-07-04 Jun_Week27 20150627 27
2015-07-05 2015-07-11 Jul_Week28 20150728 28
2015-07-12 2015-07-18 Jul_Week29 20150729 29
2015-07-19 2015-07-25 Jul_Week30 20150730 30
2015-07-26 2015-08-01 Jul_Week31 20150731 31
2015-08-02 2015-08-08 Aug_Week32 20150832 32
2015-08-09 2015-08-15 Aug_Week33 20150833 33
2015-08-16 2015-08-22 Aug_Week34 20150834 34
2015-08-23 2015-08-29 Aug_Week35 20150835 35
2015-08-30 2015-09-05 Aug_Week36 20150836 36
2015-09-06 2015-09-12 Sep_Week37 20150937 37
2015-09-13 2015-09-19 Sep_Week38 20150938 38
2015-09-20 2015-09-26 Sep_Week39 20150939 39
2015-09-27 2015-10-03 Sep_Week40 20150940 40
2015-10-04 2015-10-10 Oct_Week41 20151041 41
2015-10-11 2015-10-17 Oct_Week42 20151042 42
2015-10-18 2015-10-24 Oct_Week43 20151043 43
2015-10-25 2015-10-31 Oct_Week44 20151044 44
2015-11-01 2015-11-07 Nov_Week45 20151145 45
2015-11-08 2015-11-14 Nov_Week46 20151146 46
2015-11-15 2015-11-21 Nov_Week47 20151147 47
2015-11-22 2015-11-28 Nov_Week48 20151148 48
2015-11-29 2015-12-05 Nov_Week49 20151149 49
2015-12-06 2015-12-12 Dec_Week50 20151250 50
2015-12-13 2015-12-19 Dec_Week51 20151251 51
2015-12-20 2015-12-26 Dec_Week52 20151252 52
2015-12-27 2015-12-31 Dec_Week53 20151253 53
September 30, 2015 at 2:50 pm
Here's the code if a month's weeks start in the prior month:
SELECT
WeekStartDate,
WeekEndDate,
LEFT(DATENAME(MONTH, WeekEndDate), 3) + '_Week' +
CAST(ROW_NUMBER() OVER(PARTITION BY MONTH(WeekEndDate) ORDER BY WeekEndDate) AS varchar(2)) AS WeekName
FROM (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) % 7, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS first_sunday_of_year
) AS first_sunday_of_year
--dbo.tally is just a "standard" tally table of sequential numbers,
-- where its column name is also "tally". Substitute tally CTE from other poster above if you prefer.
--Edit: My hyperactive "sql injection" filter won't let me post tally CTE code.
INNER JOIN dbo.tally t ON DATEADD(DAY, t.tally * 7 + 6, first_sunday_of_year) < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)
CROSS APPLY (
SELECT DATEADD(DAY, t.tally * 7, first_sunday_of_year) AS WeekStartDate,
DATEADD(DAY, t.tally * 7 + 6, first_sunday_of_year) AS WeekEndDate
) AS assign_alias_names
ORDER BY 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 30, 2015 at 3:42 pm
That's awesome...thank you. how do I add number of days in each week period to the table?
September 30, 2015 at 3:49 pm
I think I figured that one out.
Thanks you
September 30, 2015 at 4:04 pm
my related question is although number of days in each week is rather consistent (6) if we expand the year from 2014 to 2020, but number of weeks in each month/year is not. While there are five weeks in Aug 2015, there are 4 weeks in Aug 2016 making data comparisons between August weeks of 2015 and 2016 rather inaccurate. what do I compare Aug week 5 in 2015 with in 2016?
Any ideas as how to create a date time schema, tally that will make comparing data by the schema more accurate?
Thank you,
October 2, 2015 at 2:37 pm
Since you're wanting to compare 2 period across years; why not use the week of the year [datepart(wk,somedate)] to create begin and end ranges for each year. You'd compare the 1st week of September 2015 [which starts Aug 30] against 2016 [which starts Aug 28th]. Comparisons should be a little closer to reality that way.
RMc
October 7, 2015 at 9:35 am
helal.mobasher 13209 (9/30/2015)
my related question is although number of days in each week is rather consistent (6) if we expand the year from 2014 to 2020, but number of weeks in each month/year is not. While there are five weeks in Aug 2015, there are 4 weeks in Aug 2016 making data comparisons between August weeks of 2015 and 2016 rather inaccurate. what do I compare Aug week 5 in 2015 with in 2016?Any ideas as how to create a date time schema, tally that will make comparing data by the schema more accurate?
Thank you,
The larger question is just how short a time period will make for a "useful" comparison. No matter what you do, weeks just won't EVER match up with either months or years, so using weeks is ALWAYS going to be problematic, if calendar months or calendar years are going to matter. Despite the shortness of February, and the 1 day difference in length for the other 11 months, the calendar month really is the best available compromise for time-based measurements, as weekly measurements are often subject to wide variations because they are just too short to allow time to smooth things out. That's not to say they can't be used, but at some point, any truly viable reporting has to "draw a line" somewhere. Weeks make that task impractical at best, unless you are willing to ignore months and years. In other words, you really can't have it both ways. Blame gravity, but that's just the way it is.
Once you decide EXACTLY what constitutes a reporting period, THEN you can "make it so"...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 8, 2015 at 8:11 am
Creating a 4/4/5 fiscal calendar, 52 weeks in every year, same number of weeks in every month, with first and last weeks holding the extra days, might make most comparisons easier over time by month / year.
We did this, and also would do day rates.
Nothing is perfect, and the preferred method most times is to find out how finance views this and match that.
It is very important to have everyone be comparing numbers with the same time periods.
You can even have differing number of days in week and month if you are a manufacturer, as shop days and sales days may be different.
You could for example, ship product 5 days a week, while the shop floor plans production for 4 or 6 days.
October 8, 2015 at 9:26 am
helal.mobasher 13209 (9/30/2015)
I need to create a week calendar from date in SQL 2012. Week date starts with Sunday regardless if first Sunday or last Sunday overlaps with previous or next month. For example, the first week in Sep 2015 starts on Sunday 8/30/2015 and ends in 9/5/2015. Too, the last week of Sep 2015 starts on 9/27/2015 and ends on 10/3/2015. Here is the final format:WeekStartdate WeekEndDate WeekName
8/30/2015 9/5/2015 Sep_Week1
9/6/2015 9/12/2015 Sep_Week2
.....
9/27/2015 10/3/2015 Sep_Week5
Appreciate any help with this.
Helal
As others have pointed out, this is a matter of definitions. Can you confirm that your definition of which month a week is a part of is based on which month has the most number of days in the week? For example, the week of 11/2/2015 - 12/5/2015 would be Dec_Week1, correct? And 12/27/2015 - 1/2/2016 would be Dec_Week5, correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2015 at 9:32 am
helal.mobasher 13209 (9/30/2015)
my related question is although number of days in each week is rather consistent (6) if we expand the year from 2014 to 2020, but number of weeks in each month/year is not. While there are five weeks in Aug 2015, there are 4 weeks in Aug 2016 making data comparisons between August weeks of 2015 and 2016 rather inaccurate. what do I compare Aug week 5 in 2015 with in 2016?Any ideas as how to create a date time schema, tally that will make comparing data by the schema more accurate?
Thank you,
That's the problem with months and weeks. The only way to actually compare month to month is to use the proverbial "Man Month" which is the number of days in the year divided by 12 or "Man week" of the number of days in the year divided by 52. IsoWeeks is a better option. Of course, weeks won't match so you're kinda stuck even with 5-4-4 annotations especially since some years have a 5-4-5 final quarter.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply