I've been googling all over the place and haven't found this. The retail client I'm working for using the NRFretail calendar. NRF site Calendars I'm wondering if anyone has ever created a lookup/dimension table with these values.
Thanks,
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
回答1:
You can find a perl module that can generate a Retail 4-5-4 calendar for any year on CPAN: http://metacpan.org/pod/DateTime::Fiscal::Retail454
It was written specifically for this problem.
回答2:
An algorithmic option I've used in the past was (I'm paraphrasing as I did it in Excel):
- From the date, figure out the weeknum (in the range 1 to 53)
From the weeknum, calculate the period number (in the range 1 to 12) as:
1+ 'one-based 3*(INT((weeknum-1)/13))+ 'start from the first week within a 13-week quarter IF(MOD(weeknum-1,13)>3,1,0)+ 'adjust for weeks in the middle chunk IF(MOD(weeknum-1,13)>8,1,0)+ 'and for weeks in the last chunk IF(weeknum>52,-1,0) 'and handle "leap week" every 6th year or so
回答3:
see http://www.nrf.com/modules.php?name=Documents&op=viewlive&sp_id=6019
4-5-4 or 4-4-5 calendars are used to give consistent month lengths from year to year, so rather than reporting monthly sales from 1-31 January the month of January starts on say a sunday and ends Saturday. This way there is always a consistent number of weekends in a month and holidays don't move around, making monthly sales comparisoons from year to year feasible
there is a lot of fiddling behind the scenes but 4-5-4 calendars are published - see link above.
it sounds like you are trying to use them as a dimension for a star schema data warehouse? Are you looking for advice on implementing that?
I really should have done some real work this evening
******************* Feb06
[[29, 30, 31, 1, 2, 3, 4],
[5, 6, 7, 8, 9, 10, 11],
[12, 13, 14, 15, 16, 17, 18],
[19, 20, 21, 22, 23, 24, 25]]
******************* Mar06
[[26, 27, 28, 1, 2, 3, 4],
[5, 6, 7, 8, 9, 10, 11],
[12, 13, 14, 15, 16, 17, 18],
[19, 20, 21, 22, 23, 24, 25],
[26, 27, 28, 29, 30, 31, 1]]
******************* Apr06
[[2, 3, 4, 5, 6, 7, 8],
[9, 10, 11, 12, 13, 14, 15],
[16, 17, 18, 19, 20, 21, 22],
[23, 24, 25, 26, 27, 28, 29]]
the above seems correct eyeballing it against NRF calendars
its from the below monstrosity, but it might help you do 15 years
import calendar
calendar.setfirstweekday(calendar.SUNDAY)
import datetime
import pprint
def addweek(yr):
d = datetime.date(yr, 12, 31)
if d.weekday() in (6,0,1):
return True
else:
return False
def printday(d):
print d.day,
if d.weekday() == 5:
print
def wksinthismth(cal):
seq = [len(mth) for mth in cal]
#print "seq", seq
#special - but really need dates in cal
if (len(seq) % 11 == 0 ) and len(seq) != 0 and addweek(2006):
return 5
try:
lenlastmth = seq[-1]
except:
#print "fail4"
return 4
try:
lenprevmth = seq[-2:-1][0]
except:
#print "fail5"
return 5
try:
lenprevprevmth = seq[-3:-2][0]
except:
lenprevprevmth = 4
if lenprevmth == []:return 5
if (lenlastmth == 4 and lenprevmth == 5 and lenprevprevmth == 5):
#long january just passed
return 5
#print lenlastmth, lenprevmth
if (lenlastmth == 4 and lenprevmth == 4) :
#print "ok5"
return 5
else:
#print "ok4"
return 4
oneday = datetime.timedelta(days=1)
startdate = datetime.date(2006,1,29)
thisdate = startdate
cal = []
thismth = []
thiswk = []
for i in range(800):
thiswk.append(thisdate.day)
if thisdate.weekday() == 5: #week over, decide how format it
wks = wksinthismth(cal)
if len(thismth) <= wks-1:
thismth.append(thiswk)
else:
cal.append(thismth)
thismth = []
thismth.append(thiswk)
thiswk = []
thisdate += oneday
mthslist = ['Feb06',
'Mar06',
'Apr06',
'May06',
'Jun06',
'Jul06',
'Aug06',
'Sept06',
'Oct06',
'Nov06',
'Dec06',
'Jan07',
'Feb07',
'Mar07',
'Apr07',
'May07',
'Jun07',
'Jul07',
'Aug07',
'Sept07',
'Oct07',
'Nov07',
'Dec07',
'Jan08',
'Feb08',
'Mar08',
'Apr08',
'May08',
'Jun08',
'Jul08',
'Aug08',
'Sept08',
'Oct08',
'Nov08',
'Dec08',
]
i = 0
for mth in cal:
print "*******************", mthslist[i]
pprint.pprint(mth)
i += 1
# start week on Sun
# if 31 dec is S M T then add week in Jan
print [len(mth) for mth in cal]
回答4:
Attacched a csv with a calendar from 2005 to 2025,with the followings:
RatailCalendar-4-5-4
DataNumber: date in number format
Year: Year
Quarter: Quarter (number)
Month: Month (number)
Week: week
DataDate: date in string format
Day: day number
DayName:Day description (In italian)
CommercialWeek: commercial Week caluculated with DateTime::Fiscal::Retail454
CommercialMonth: commercial Month based on nrf.com 4-5-4 retail calendar
CommercialYear: commercial Year based on nrf.com 4-5-4 retail calendar
StartWeek: start commercial week aluculated with DateTime::Fiscal::Retail454
EndWeek: end commercial week aluculated with DateTime::Fiscal::Retail454
This is the perl script used to generate the calendar:
use DateTimeX::Fiscal::Fiscal5253;
use Data::Dumper;
use Data::Dumper::Table;
use DBI;
use strict;
use JSON;
use warnings;
my $fc = DateTimeX::Fiscal::Fiscal5253->new(
end_month => 1,
end_dow => 7,
end_type => 'closest',
leap_period => 'last',
year => 2019,
);
my $filename = 'report.txt';
open(my $fh, '>', $filename) or die "Could not open file '$filename'$!";
print $fh Dumper(\$fc);
close $fh;
The main difference with nrf.com 4-5-4 retail calendar is that the week is formatted in "Italian Style" starting on Monday.
Hope this help.
回答5:
The script below is to create a table for 4/5/4 Retail Calendar for any time frame. It calculates any year containing 52 or 53 weeks. The week is formated in US style starting on Sunday.
I have created Calendar table before running the following script with the following structure and populated data into "dbo.Calendar". You might want to use the article by Aaron Bertrand https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ to populate data into the table "Calendar":
CREATE TABLE dbo.Calendar
(
DateKey INT NOT NULL PRIMARY KEY,
[Date] DATE NOT NULL,
[Day] TINYINT NOT NULL,
DaySuffix CHAR(2) NOT NULL,
[Weekday] TINYINT NOT NULL,
WeekDayShortName VARCHAR(3) NOT NULL,
WeekDayLongName VARCHAR(10) NOT NULL,
IsWeekend BIT NOT NULL,
IsHolidayUS BIT NOT NULL,
HolidayUSText VARCHAR(64) SPARSE,
IsHolidayCA BIT NOT NULL,
HolidayCAText VARCHAR(64) SPARSE,
DOWInMonth TINYINT NOT NULL,
[DayOfYear] SMALLINT NOT NULL,
WeekOfMonth TINYINT NOT NULL,
WeekOfYear TINYINT NOT NULL,
ISOWeekOfYear TINYINT NOT NULL,
[Month] TINYINT NOT NULL,
[MonthShortName] VARCHAR(3) NOT NULL,
[MonthLongName] VARCHAR(10) NOT NULL,
[Quarter] TINYINT NOT NULL,
QuarterName VARCHAR(6) NOT NULL,
[Year] INT NOT NULL,
YYYYMM CHAR(6) NOT NULL,
MonthYear CHAR(7) NOT NULL,
FirstDayOfMonth DATE NOT NULL,
LastDayOfMonth DATE NOT NULL,
FirstDayOfQuarter DATE NOT NULL,
LastDayOfQuarter DATE NOT NULL,
FirstDayOfYear DATE NOT NULL,
LastDayOfYear DATE NOT NULL,
FirstDayOfNextMonth DATE NOT NULL,
FirstDayOfNextYear DATE NOT NULL,
PeriodID INT NULL
);
GO
The script creates the table "CalendarPeriods45" and populates data. The table "CalendarPeriods454" is linked to the table "Calendar" by "PeriodID". The script was created in Sql Server 2016, so you might need to change "DROP TABLE IF EXISTS..." to the "IF OBJECT_ID('dbo.CalendarPeriods454') IS NOT NULL DROP TABLE...".
DROP TABLE IF EXISTS dbo.CalendarPeriods454
CREATE TABLE dbo.CalendarPeriods454
(
PeriodID INT NOT NULL PRIMARY KEY,
StartDate Date NOT NULL,
EndDate Date NOT NULL,
[Days] TINYINT NOT NULL,
PeriodOfYear TINYINT NOT NULL,
PeriodShortName VARCHAR(3) NOT NULL,
PeriodLongName VARCHAR(10) NOT NULL,
[Year] INT NOT NULL,
[Quarter] TINYINT NOT NULL,
QuarterName VARCHAR(6) NOT NULL,
SalesReleaseDate Date NOT NULL,
Season VARCHAR(6) NOT NULL
);
GO
DECLARE @StartDate datetime; -- date to load
DECLARE @EndDate datetime; -- date to load
SET @StartDate = '2000-01-30' -- should be beginning of the 454 Calendar year
SET @EndDate = '2041-01-30'
DECLARE @PeriodID INT;
SELECT @PeriodID = ISNULL(s.periodID,0) FROM (SELECT MAX(periodID) as periodID from CalendarPeriods454) as s
SET @PeriodID = @PeriodID + 1
DECLARE @PeriodOfYear TINYINT; --1 to 12
SET @PeriodOfYear = 1;
DECLARE @Days TINYINT ; --28 or 35
DECLARE @PeriodShortName VARCHAR(3);
DECLARE @PeriodLongName VARCHAR(10);
DECLARE @Year int;
SET @Year = Datepart(year,@StartDate);
DECLARE @Quarter TINYINT; --1 or 2 or 3 or 4
While @StartDate<=@EndDate
BEGIN
SET @Days = (CASE @PeriodOfYear
WHEN 1 THEN 28
WHEN 2 THEN 35
WHEN 3 THEN 28
WHEN 4 THEN 28
WHEN 5 THEN 35
WHEN 6 THEN 28
WHEN 7 THEN 28
WHEN 8 THEN 35
WHEN 9 THEN 28
WHEN 10 THEN 28
WHEN 11 THEN 35
WHEN 12 THEN 28
END)
-- Exclusion if need to add additional week at the end of the year, check the last day of the period.
-- If last day of the period equal 26 or 27 january we add additional week
IF @PeriodOfYear = 12 and (datepart(day,dateadd(day,@Days-1,@StartDate))=26 or datepart(day,dateadd(day,@Days-1,@StartDate))=27)
SET @Days = 35
SET @PeriodShortName = (CASE @PeriodOfYear
WHEN 1 THEN 'Feb'
WHEN 2 THEN 'Mar'
WHEN 3 THEN 'Apr'
WHEN 4 THEN 'May'
WHEN 5 THEN 'Jun'
WHEN 6 THEN 'Jul'
WHEN 7 THEN 'Aug'
WHEN 8 THEN 'Sep'
WHEN 9 THEN 'Oct'
WHEN 10 THEN 'Nov'
WHEN 11 THEN 'Dec'
WHEN 12 THEN 'Jan'
END)
SET @PeriodLongName = (CASE @PeriodOfYear
WHEN 1 THEN 'February'
WHEN 2 THEN 'March'
WHEN 3 THEN 'April'
WHEN 4 THEN 'May'
WHEN 5 THEN 'June'
WHEN 6 THEN 'July'
WHEN 7 THEN 'August'
WHEN 8 THEN 'September'
WHEN 9 THEN 'October'
WHEN 10 THEN 'November'
WHEN 11 THEN 'December'
WHEN 12 THEN 'January'
END)
SET @Year = (CASE @PeriodOfYear
WHEN 1 THEN Datepart(year,@StartDate)
ELSE @Year
END)
SET @Quarter = (CASE @PeriodOfYear
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 5 THEN 2
WHEN 6 THEN 2
WHEN 7 THEN 3
WHEN 8 THEN 3
WHEN 9 THEN 3
WHEN 10 THEN 4
WHEN 11 THEN 4
WHEN 12 THEN 4
END)
INSERT INTO dbo.CalendarPeriods454(PeriodID,StartDate,EndDate,[Days],PeriodOfYear,
PeriodShortName,PeriodLongName,[Year],[Quarter],QuarterName,SalesReleaseDate,Season)
--Calendar 454-454-454-454
SELECT
PeriodID = @PeriodID,
StartDate = @StartDate,
EndDate = dateadd(day,@Days-1,@StartDate),
[Days] = @Days,
PeriodOfYear = @PeriodOfYear,
PeriodShortName = @PeriodShortName,
PeriodLongName = @PeriodLongName,
[Year] = @Year,
[Quarter] = @Quarter,
QuarterName =CONVERT(VARCHAR(6), CASE @Quarter WHEN 1 THEN 'First'
WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),
SalesReleaseDate = dateadd(day,4,@StartDate),
Season = CASE WHEN @PeriodOfYear>=1 and @PeriodOfYear<=6 THEN 'SPRING' ELSE 'FALL' END
SET @StartDate = dateadd(day,@Days,@StartDate)
SET @PeriodID = @PeriodID+1
SET @PeriodOfYear = (CASE WHEN @PeriodOfYear+1 =13 THEN 1 ELSE @PeriodOfYear+1 END)
END
-- Table Calendar was created before at a daily grain
-- we need to update Calendar Table to set PeriodID to link Calendar and
alter table [dbo].DateDimension add PeriodID INT
alter table [dbo].DateDimension add FW varchar(2)
;with cte
as
(
select DateKey
, ((ROW_NUMBER() over(Order by DateKey asc)-1) / 7) + 1 as 'FW'
, Cal454.PeriodID 'FM'
FROM [dbo].DateDimension as C1
--FROM [dbo].Calendar as C1
INNER JOIN [dbo].[CalendarPeriods454] as Cal454
ON Cal454.StartDate<=C1.Date AND Cal454.EndDate>=C1.Date
)
UPDATE c1
SET c1.PeriodID = cte.FM
,c1.FW = cte.FW
FROM [dbo].DateDimension as C1
join cte
on c1.DateKey = cte.DateKey
