December 23, 2014 at 3:08 am
Good Morning,
I need to create a table which holds date information for our financial year.
I have all the dates between now and 2045 and the start of the week and the end of the week. What I also have is the first sunday of the previous week in the spreadsheet too.
Please see below attachment
What I need to autofill once I import these three dates into a database is the week and the month.
The difficulty surrounding the month is that, we start a new month on the FIRST Sunday of the month.
So dates 07/04/14 to 04/05/2014 would be month 1.
Month 2 would begin on 05/05/2014 as it is the day after the first Sunday of the month, and so on....Month 5 would start on the 04/08/14.
Could anyone help me script something that would automatically calculate the week and month for me on the basis on above, if I have the start date, end date and 1st sunday already in a table?
Thanks
December 23, 2014 at 3:13 am
Spreadsheet now attached with date examples
December 23, 2014 at 5:27 am
Your description "we start a new month on the FIRST Sunday of the month." Does not match your sample "as it is the day after the first Sunday of the month". Please clarify.
Also, when would month 4 (July) 2014 start? On June 30 or or July 7th? The same for Month 6 (Sptember), does it start on the 1st or the 8th of September?
Regarding the week number: Are there any special rules to consider?
December 23, 2014 at 6:49 am
In answer to your question and to clarify.
There is nothing special about the "weeks" as far as I am aware.
I'll try and explain the month a bit better and apologise for the ambiguity.
The months for “fiscal year” at our company would be as follows –
With the month number, start date and the end date below -
1 – 07/04/14 – 04/05/14
2 – 05/05/14 – 01/06/14
3 – 02/06/14 – 06/07/14
4 – 07/07/14 – 03/08/14
5 – 04/08/14 – 31/08/14
6 – 01/09/14 – 05/10/14
7 – 06/10/14 – 02/11/14
8 – 03/11/14 – 30/11/14
9 – 11/12/14 – 04/01/15
10 – 05/01/15 – 01/02/15
11 – 02/02/15 – 01/03/15
12 - 02/03/15 – 05/03/15
So the month ends on the first Sunday of the next month.
December 23, 2014 at 6:52 am
"So the month ends on the first Sunday of the next month." does not match your sample data:
Month 5 (August) doesn't end at the first Sunday of the next month (that would be Sept. 7th), it ends at the last Sunday of the current month (Aug 31st).
Again, please clarify.
December 23, 2014 at 7:20 am
Sorry I'm not making sense - the way we do our months here aren't really making sense to me either.
Looking at the data again it looks to be that the month ends the day before the first Monday of the next month starts.
So using these dates again as an example -
1 – 07/04/14 – 04/05/14
2 – 05/05/14 – 01/06/14
3 – 02/06/14 – 06/07/14
4 – 07/07/14 – 03/08/14
5 – 04/08/14 – 31/08/14
6 – 01/09/14 – 05/10/14
7 – 06/10/14 – 02/11/14
8 – 03/11/14 – 30/11/14
9 – 11/12/14 – 04/01/15
10 – 05/01/15 – 01/02/15
11 – 02/02/15 – 01/03/15
12 - 02/03/15 – 05/03/15
The year starts 7th of April. The first Monday of May is the 5th . The first monday of June is the 2nd, the first monday of July is 7th, the first Monday of august is 4th (and so on).
So the month resets the day before the first Monday of the upcoming month.
December 23, 2014 at 2:56 pm
Here's a solution that'll return the Fiscal year, month, and week based on a given year and the number of years to consider:
DECLARE @FY char(4)='2014',
@NumberOfYears TINYINT = 10
;
WITH cte AS
(
SELECT DISTINCT
DATEADD(yy,x.N,DATEADD(dd,Number,@FY+'0401')) as FDay,
YEAR(DATEADD(MONTH,-3,DATEADD(yy,x.N,DATEADD(dd,Number,@FY+'0401')))) AS FYear
FROM master..spt_values
CROSS APPLY (SELECT Number AS N FROM master..spt_values s WHERE s.Type ='P' and s.Number< @NumberOfYears)x
WHERE Type ='P' and Number<373
),
cte2 as
(
SELECT MIN(FDay) AS FirstMonday, FYear, ROW_NUMBER() OVER(PARTITION BY FYear ORDER BY MIN(FDay)) as MonthNumber
FROM cte
WHERE DATEDIFF(dd,0,FDay) %7 = 0 AND FDay < DATEADD(yy,@NumberOfYears,@FY+'0401')
GROUP BY FYear,MONTH(FDay)
)
SELECT FDay AS FirstMonday, FYear, y.MonthNumber, ROW_NUMBER() OVER(PARTITION BY FYear ORDER BY FDay) as WkNumber
FROM cte
CROSS APPLY (SELECT TOP 1 MonthNumber FROM cte2 WHERE cte2.FirstMonday <= cte.FDay ORDER BY cte2.FirstMonday DESC)y
WHERE DATEDIFF(dd,0,FDay) %7 = 0 AND FDay < DATEADD(yy,@NumberOfYears,@FY+'0401')
December 24, 2014 at 2:20 am
Incredible.
Thank you so much, this is much more advanced than I'm used to but it "almost" gives me what I want.
Looking at year 2015, this will include a leap year - the 29th of Feb 2016.
Not sure why but for 2015 only 51 weeks are being calculated. It appears that it jumps a week.
So it goes from 22/02 - 28/02 ( I added the week end to your script) and then it jumps to 07/03 to the 13/03.
So week 29/02 to 06/3 is missing altogether.
Any extra help on this would be great - but just to say again this is fantastic what we have already, just needs tweaking
December 24, 2014 at 3:17 am
Stupid error.... :crazy:
Replace
DATEADD(yy,x.N,DATEADD(dd,Number,@FY+'0401')) as FDay,
with
DATEADD(dd,Number,DATEADD(yy,x.N,@FY+'0401')) as FDay,
The first line add the year after the number of days are added. This leads to ignoring leap years if the start year isn't a leap year itself.
When using 2014 as the start year, it'll use Feb 28th and add the years up as well as March 1st. So it won't "hit" Feb. 29th.
The results are different depending on the start year. If you start with 2015, the result is ok, since within that first year it'll include Feb 29th 2016. Adding one year to it will lead to March 1st 2017. But it doesn't work vice versa.
Sorry for the mistake. I should have known better...
MERRY XMAS AND A HAPPY NEW YEAR!!
December 24, 2014 at 3:33 am
Don't dare apolagise mate - You've helped me so much. Really appreciate it.
Happy xmas.
December 24, 2014 at 11:46 am
I think this is a much simpler method:
DECLARE @first_fiscal_year smallint
DECLARE @number_of_years tinyint
SET @first_fiscal_year = 2014
SET @number_of_years = 10
------------------------------------------------------------------------------------------------------------------------
DECLARE @fiscal_years TABLE (
year smallint PRIMARY KEY,
first_monday_of_april AS
CAST(DATEADD(DAY, -DATEDIFF(DAY, 0, CAST(year AS char(4)) + '0407') % 7,
CAST(year AS char(4)) + '0407') AS date)
)
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
INSERT INTO @fiscal_years ( year )
SELECT @first_fiscal_year + years.tally
FROM cteTally100 years
WHERE years.tally BETWEEN 0 AND @number_of_years - 1
;WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT
week_start,
fy.year AS fiscal_year,
relative_month_number % 12 + 1 AS month_number,
weeks.tally % 52 + 1 AS Week_Number
--INTO tempdb.dbo.date_calcs1
FROM @fiscal_years fy
INNER JOIN cteTally100 weeks ON
weeks.tally BETWEEN 0 AND 51
CROSS APPLY (
SELECT DATEADD(DAY, weeks.tally * 7, fy.first_monday_of_april) AS week_start
) AS assign_alias_names1
CROSS APPLY (
SELECT DATEDIFF(MONTH, fy.first_monday_of_april, week_start) AS relative_month_number
) AS assign_alias_names2
ORDER BY fy.year, weeks.tally
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 24, 2014 at 2:18 pm
I don't you if you still need it or want it but I went whole hog on this one and created a function that provides a whole lot of Fiscal-related information. As stated in the header of the code, it can be used for on-the-fly singleton values or in conjunction with a "Tally" Table function to generate as many Fiscal Dates as you might need or to generate a more permanent Fiscal Calendar table.
Here's the code. It looks long but don't let the looks fool you. It's progressive so it's easy to read and it's nasty fast (generates 100 years of date information by day in about a half a second). The code also has usage examples in the header.
CREATE FUNCTION dbo.FiscalDateInfo (@pSomeDT DATETIME)
/**********************************************************************************************************************
Purpose:
Given a Calendar Date, calculate various date parts for a Fiscal Year whose definition is that it starts on the first
Monday of April and each month within the Fiscal Year starts on the first Monday of that month. It also calculates
"Start" and "Next" boundaries for each "part" including Fiscal Weeks and more.
Usage:
--===== Simple "Singleton" Syntax
SELECT FiscalYear
,FiscalMonth
,WeekOfFiscalYear
,WeekOfFiscalMonth
,CurrFiscalYearStart
,NextFiscalYearStart
,CurrFiscalMonthStart
,NextFiscalMonthStart
,CurrFiscalWeekStart
,NextFiscalWeekStart
FROM dbo.FiscalDateInfo(@SomeDT)
;
--===== Return the Fiscal Calendar Information for 100 Calendar Years from the year 2000 up to 2100.
-- This method can be used to create a permanent Fiscal Calendar Table.
-- If you don't have an fnTally function, I've attached it to this post.
SELECT CalendarDate = DATEADD(dd,t.N,'2000')
,f.*
FROM dbo.fnTally(0,DATEDIFF(dd,'2000','2100')) t
CROSS APPLY dbo.FiscalDateInfo(DATEADD(dd,t.N,'2000')) f
ORDER BY t.N
;
Programmer's Notes:
1. If you make a Fiscal Calendar Table from this, I suggest using the "CalendarDate" column as the Clustered PK.
2. Consider NOT making a table from this because it's 100% memory (no READs whatsoever) and it's nasty fast.
The 100 year example (36,526 days) takes only 552ms to run and that includes piping the output to the screen.
3. Because only date functions were used, Leap Years are handled auto-magically.
Revision History:
Rev 00 - 24 Dec 2014 - Jeff Moden - Intial creation andd Unit Test.
**********************************************************************************************************************/
RETURNS TABLE AS
RETURN
WITH
cteFirstOfCurrMonth AS
( --=== Finds the first of the month for the given date
SELECT FirstOfCurrMonth = DATEADD(mm,DATEDIFF(mm,0,@pSomeDT),0)
)
,
cteFirstOfOther AS
( --=== Finds first of previous and next months
SELECT FirstOfPrevMonth = DATEADD(mm,-1,FirstOfCurrMonth)
,FirstOfCurrMonth
,FirstOfNextMonth = DATEADD(mm, 1,FirstOfCurrMonth)
FROM cteFirstOfCurrMonth
)
,
cteFiscalMonthStart AS
( --=== Finds the first Monday of the months from above
SELECT PrevFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfPrevMonth)/7*7,0)
,CurrFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfCurrMonth)/7*7,0)
,NextFiscalMonthStart = DATEADD(dd,DATEDIFF(dd,-6,FirstOfNextMonth)/7*7,0)
FROM cteFirstOfOther
)
,
cteOffSet AS
( --=== If the given date is less than the first Monday of the month, then offset everything by -1 month
-- Can't just subtract a month here because months don't have an even number of weeks. We have to do the "Monday" thing.
SELECT CurrFiscalMonthStart = CASE WHEN @pSomeDT < CurrFiscalMonthStart THEN PrevFiscalMonthStart ELSE CurrFiscalMonthStart END
,NextFiscalMonthStart = CASE WHEN @pSomeDT < CurrFiscalMonthStart THEN CurrFiscalMonthStart ELSE NextFiscalMonthStart END
FROM cteFiscalMonthStart
)
,
cteFiscalBasics AS
( --=== Calculate the fiscal week starts, Year, and Month
SELECT FiscalYear = YEAR(CurrFiscalMonthStart) - CASE WHEN MONTH(CurrFiscalMonthStart) <= 3 THEN 1 ELSE 0 END --Previous year if Jan, Feb, or Mar
,FiscalMonth = (MONTH(CurrFiscalMonthStart)+8)%12+1 --The +8 is the other 9 months-1 because of the 0-based modulus
,WeekOfFiscalMonth = DATEDIFF(dd,CurrFiscalMonthStart,@pSomeDT)/7+1 --Number of weeks since the first of the fiscal month +1
,CurrFiscalMonthStart
,NextFiscalMonthStart
,CurrFiscalWeekStart = DATEADD(dd,DATEDIFF(dd, 0,@pSomeDT)/7*7,0) --Figures out the Monday equal to or prior to the date
,NextFiscalWeekStart = DATEADD(dd,DATEDIFF(dd,-7,@pSomeDT)/7*7,0) --Figures out the Monday equal to or prior to the date +1 week
FROM cteOffset
)
,
cteFiscalYears AS
( --=== Calculate the start of the current and next fiscal years
SELECT FiscalYear
,FiscalMonth
,WeekOfFiscalMonth
,CurrFiscalYearStart = DATEADD(dd,DATEDIFF(dd,-6,DATEADD(mm,(FiscalYear-1900)*12+ 3,0))/7*7,0) --Adds the year to date "0" as months + 3 and finds the first Monday of the year
,NextFiscalYearStart = DATEADD(dd,DATEDIFF(dd,-6,DATEADD(mm,(FiscalYear-1900)*12+15,0))/7*7,0) --Same but adds an extra 12 months
,CurrFiscalMonthStart
,NextFiscalMonthStart
,CurrFiscalWeekStart
,NextFiscalWeekStart
FROM cteFiscalBasics
)
--==== Last but not least, calculate the week of the fiscal year
SELECT FiscalYear
,FiscalMonth
,WeekOfFiscalYear = DATEDIFF(dd,CurrFiscalYearStart,CurrFiscalWeekStart)/7+1
,WeekOfFiscalMonth
,CurrFiscalYearStart
,NextFiscalYearStart
,CurrFiscalMonthStart
,NextFiscalMonthStart
,CurrFiscalWeekStart
,NextFiscalWeekStart
FROM cteFiscalYears
;
Following the example to generate the 100 years of dates, you'll need a "Tally Table" function. Here's the function that I usually use.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne (must be a 0 or 1) up to and including @MaxN with a max value of 1 Billion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works. http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN INT)
RETURNS TABLE AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) --10E1 or 10 rows
, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or 1 Thousand rows
, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or 1 Billion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E9 -- Values from 1 to @MaxN
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2014 at 10:31 am
CELKO (12/25/2014)
Why not move the data from the spreadsheets you already have to a table? Where is the ISO week-date column?
Hi Joe,
I think that's what the op is trying to do. I think they used the spreadsheet as a working tool to make all of the dates they needed to load into a Fiscal Calendar Table and were trying to figure out the formula that they needed to calculate other columns like Fiscal Year and Fiscal Month, etc. Once done, the goal was to have all this info in a table.
As for the ISO week-date column, that would certainly be easy enough to add especially since there's actually a function for it but wouldn't that be superfluous considering that they're not actually following anything that has to do with ISO date parts?
While you're here... you've been around the block with a lot of companies and have played a role in the development of the ISO standards. I've never understood why companies and even the government have these odd-ball Fiscal Year "standards" like the Fiscal Year starting on the first Monday of April (for example). What is the purpose of doing such a thing instead of following either the good ol' Calendar Year or the ISO standard year? It seems like a huge headache for all and I just don't get it.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2014 at 11:17 am
CELKO (12/25/2014)
I think that's what the op is trying to do. I think they used the spreadsheet as a working tool to make all of the dates they needed to load into a Fiscal Calendar Table and were trying to figure out the formula that they needed to calculate other columns like Fiscal Year and Fiscal Month, etc. Once done, the goal was to have all this info in a table.
Over the years I have found that programmers cheerily re-invent what the accounting department already has hidden in the basement in spreadsheets. We just have the attitude of a Dilbert cartoon ("The trolls in Accounting who deny expenses!", etc) and never think about talking to them. :rolleyes:
Heh... very true.
Still, I'm curious. Why do some companies use weird Fiscal Years, such as starting in April? What is the purpose there?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2014 at 1:20 pm
CELKO (12/25/2014)
Why do some companies use weird Fiscal Years, such as starting in April? What is the purpose there?
The last time I looked the GAAP had about 150 fiscal calendars. I have no idea why. Hell, I am still hoping that the US will switch to the Metric system, UTC time and the Edwards Calendar (four quarters of 30, 30, 31 day months with an intercalendaral New Years day every year and a leap year day every four years).
Heh... it's funny that you bring up the metric system. I remember doing some work concerning solar heat and I had a question. Someone on that particular forum (I forget which forum it was... it was a long time ago) was British and my question concerned BTUs (British Thermal Units) so I naturally assumed that he would know about BTUs. Man, was I wrong! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply