Creating Fiscal/Calendar Table

10-11  Source: Network gathering  Views:0 

Advertisement
Hello,
Please, help to create a Fiscal and Calendar table with the following conditions:
The table and the first fiscal year start from 01/31/2011
The end date could be 12/31/2020 (not that important as it could be a smaller date)
All the next fiscal years should start 52 weeks after on the following Monday.
I’m trying to modify the following code that is calculating the beginning of the fiscal year with 52 weeks ahead but with following Monday only for the first next Fiscal Year.
Thanks
DECLARE
@StartDate smalldatetime
= '01/31/2011'
--First Calendar date to include in table
DECLARE
@EndDate smalldatetime
= '12/31/2020'
--Last calendar date to include in the table
declare
@FiscalYearBegin_First smalldatetime
= '1/31/2011'
DECLARE
@Cycle INT
= 52
SET
DATEFIRST 7
 ;WITH
--This secton generates the number table
E00(N)
AS (SELECT 1
UNION ALL
SELECT 1),
E02(N)
AS (SELECT 1
FROM E00
a, E00
b),
E04(N)
AS (SELECT 1
FROM E02
a, E02
b),
E08(N)
AS (SELECT 1
FROM E04
a, E04
b),
E16(N)
AS (SELECT 1
FROM E08
a, E08
b),
E32(N)
AS (SELECT 1
FROM E16
a, E16
b),
cteTally(N)
AS (SELECT
ROW_NUMBER()
OVER (ORDER
BY N)
FROM E32),
--This CTE generates a list of calendar dates
CalendarBase
as (
 SELECT
DateKey =
n
, CalendarDate
= DATEADD(day,
n -1,
@StartDate )
case when n
= 1 then
@FiscalYearBegin_First 
else
case when
datepart(dw,DATEADD(DAY,
n -1
, @StartDate ))
= 2 then 
dateadd(week,CAST(ABS(DATEDIFF(WEEK,DATEADD(day,
n -1
, @StartDate ),@StartDate))[email protected]
AS INT)
* @Cycle,@FiscalYearBegin_First)
else
dateadd(week,
datediff(week, 0,
dateadd(week,CAST(ABS(DATEDIFF(WEEK,DATEADD(day,
n - 1,
@StartDate ),@StartDate))[email protected]
AS INT)
* @Cycle,@FiscalYearBegin_First)), 7)
end
end FiscalYearBegin
FROM cteTally
  WHERE
N <=
DATEDIFF(day,
@StartDate ,
@EndDate +1)
--Finally, use the list of calendar dates to fill the date dimension
SELECT
DateKey
, IsoDate      
= CONVERT(char(8),
CalendarDate, 112)
, CalendarDate
, CalendarYear 
= YEAR(CalendarDate)
, CalendarQuarter
=  (DATEPART(QUARTER,CalendarDate)
, CalendarMonth
= MONTH(CalendarDate)
, CalendarDay  
= DATEPART(DAY,
CalendarDate)
, DayofWk      
= DATEPART(Dw,
CalendarDate)
, CalendarWeekOfMonth
= DATEDIFF(week,
DATEADD(day,1,
CalendarDate-DAY(CalendarDate)
+ 1)
-1, CalendarDate)
+1
, WeekofYr     
= DATEPART(WEEK,
CalendarDate)
, DayofYr      
= DATEPART(DAYOFYEAR,
CalendarDate)
, NameMonth    
= DATENAME(Month,
CalendarDate)
, NameDay      
= DATENAME
(Weekday,CalendarDate
, FiscalYear   
= YEAR(FiscalYearBegin)
, FiscalMonth   
= DATEDIFF(
MONTH,
FiscalYearBegin,
CalendarDate) + 1
, FiscalQuarter  
= DATEDIFF(
QUARTER,
FiscalYearBegin,
CalendarDate) + 1
, FiscalWeek    
= DATEDIFF(
WEEK,
FiscalYearBegin,
CalendarDate) + 1
, FiscalDay     
= DATEDIFF(
day,
FiscalYearBegin,
CalendarDate) + 1
FROM
CalendarBase
Try the below code if this meets your requirement.
DECLARE @StartDate DATETIME = '01-31-2011'
,@EndDate DATETIME = '12-31-2020'
;WITH cteCalendar (CurrentDate,WeekDay,DayNum,WeekNum)
AS
(SELECT @StartDate AS CurrentDate, DATENAME(WEEKDAY,@StartDate) AS WeekDay, 1 AS MonthCode, 1 AS WeekNum
UNION ALL
SELECT DATEADD(DD,1,cte.CurrentDate) AS CurrentDate, DATENAME(WEEKDAY,DATEADD(DD,1,cte.CurrentDate)) AS WeekDay, cte.DayNum + 1 AS DayNum
, cte.DayNum / 7 + 1 as WeekNum
FROM cteCalendar cte
WHERE cte.CurrentDate + 1 <= @EndDate
SELECT CurrentDate,WeekDay
,CASE WHEN WeekNum%52 = 0 THEN 52
ELSE WeekNum%52 END AS WeekNumber
,DATEPART(YY,@StartDate) + (WeekNum - 1)/52 AS FiscalYear
FROM cteCalendar
OPTION (MAXRECURSION 0)
Only year condition was mentioned and you didn't mention about month, so i did not consider month in the result set. If month is also required, share the condition (30Days in a month or 4Weeks in month?)
Let me know if i got you wrong.
Related articles
  • Creating Fiscal/Calendar Table 10-11

    Hello, Please, help to create a Fiscal and Calendar table with the following conditions: The table and the first fiscal year start from 01/31/2011 The end date could be 12/31/2020 (not that important as it could be a smaller date) All the next fiscal

  • SQL Query to calculate on-time dispatch with a calendar table 10-11

    Hi Guys, I have a query (view) to calculate orders' fulfillment leadtimes. The current criteria exclude week-ends but not bank holidays therefore I have created a calendar table with a column name isBusinessDay but I don't know how to best use this t

  • Creating a fiscal date table that includes Day of Year and Week of Year column 10-11

    Hi, Our fiscal year runs between 1st October through to 30th September and I'm trying to create a date table that I can use to represent this in Powerpivot (I'm using Excel 2013). I had no probably creating columns for Fiscal month or fiscal year, ho

  • Is there a way to create a calendar with a table that self fills in the dates? 10-11

    I am creating a calendar that recognizes a date entered into a numeric cell and then calculates the dates for the rest of the cells. Ex:   Sun Mon Tues Wed Thur Fri Sat 16 17 18 19 20 21 22 23 24 25 26 27 28 24 30 31 I put in 16th a tuesday then the

  • Creating a calendar of events based upon selections made in other tables 11-30

    I have a Sheet that I've started working on the includes a variety of pop-up menus that reference look up tables on another sheet that contain names and prices of products. In conjunction with the drop down menus are a series of check boxes for the m

  • SSRS Expression Current month in a Fiscal calendar dimension from a SSAS cube 10-11

    How to create an expression for a default SSRS parameter to a SSAS cube when using i Fiscal Year calendar - and where I want to show current Calendar month and year (for example February 2014)  This is an example of my Time dimension for February 201

  • Multiple Fiscal Calendar in a single context 10-11

    Hi, I know that for each context we can only have one PAS database, as a result, only a single Fiscal Calendar definition for a each context. My question is: there is any way of creating a workaround for this? I need to have in a single context KPI's

  • Is there a way to create a calendar for keynote that automatically updates to current month? 10-11

    I use keynote presentations for teaching foreign languages and wanted a calendar that updates automatically to current month everytime I present it rather than making it manually each month.  I'm wondering if it can be done using the keynote date/tim

  • Multiple Fiscal Calendars Displayed in Subject Area Time Dimension 10-11

    Hi all, Thanks for taking the time to review my post. Environment Oracle BI Applications 7.9.6 Financial Analytics Oracle E-Business Suite 11.5.10 Query The Time dimension on my Subject Areas (Financial Analytics) are showing more period data than I

  • How to create a Calendar Class in Universe. 10-11

    I want to create a Calendar class which will further contain Year,Quarter,Month,Day,Week Objects which can be used at report level. In our database we have different dates in " DD/MM/YYYY:hh:min:ss " format. I would like to know if this can be u

  • How to create common Calendar hieracrhy? 11-30

    I need to create a repository to support the following busines scenario. My customer has a Supportcall Handling system and he wants a report to show him the volumes of Supportcalls in the following format. A Supportcall logged on Monday, first respon

  • How to create a time table by Automator 11-30

    I want to create a new calendar by using Automator. It is a time table. In the time table, there are Day 1 - Day 6. For example, https://files.me.com/lawhei/442uc1 so on. It starts from 1/9/2010 and end on 1/7/2011. h represent holiday, no event need

  • Rep deseign question-- changing from (W_DAY_D) to Fiscal Calendar (M_CAL % 11-30

    I am trying to find out some information on Rep design for a customer. The question is, for a given RPD file, with Dim - Date Dimension that is pointing to W_DAY_D (default Calendar Data), can the Source in RPD for Dim - Date Dimension be changed to

  • Fiscal Calendar month 11-30

    I am trying to create a function within my select statement that will return the fiscal month number. My company's fiscal calendar starts on October 1. The first problem I had is I wasn't sure if there was any easy way to just say if the current mont

  • ERROR: There is an error in your fiscal calendar 11-30

    Hi, I am trying to create Set Of Books in Vision Demo Database using accounting calendar FISCAL, but when I tried to save the record it gives me following error "There is an error in your fiscal calendar. Not all dates between 01-Jan-01 and 01-FEB-01

  • How can I create a new table in a MySQL database in MVC 5 10-11

    I have an MVC 5 app, which uses MySQL hosted in Azure as a data source. The point is that inside the database, I want to create a new table called "request". I have already activated migrations for my database in code. I also have the following

  • Error while creating the DWH tables using DAC 10-11

    Hi, I am getting error while creating the DWH tables using DAC. I have created a ODBC DSN using merant driver with DAC repository DB credentials and the test connection is successful. And while creating the tables i gave the olap dw credentials and t

  • Error while creating Cross-reference table using Xreftool (PIP Ins) 10-11

    Error while running xref.sh script unable to create cross reference table. **Error: Exception in thread "main" java.lang.NoClassDefFoundError: oracle/tip/xref/tool/AdminTool** Could not find xref directory within tip folder (/OracleAS_1/bpel/doc

  • How do I create new user table in program and then access it? 10-11

    I am writing an application where I check for the existence of user objects when the add on starts and run the setup if the objects do not exist. I can create the user tables and fields but after the setup completes these objects still are not visibl

  • How to create a custom table in the below format 10-11

    Dear Friends           can any one please let me know hot to create a custom table as below outpatient |employeeno | class | startdate | enddate | spouse |1stchild | 2nchild|  Rate | amount| My FO wants to enter in the SM30 as above ......i.e he want