July 27, 2014 at 9:19 pm
Comments posted to this topic are about the item How to Handle Calculations Related to fiscal year and quarter
July 27, 2014 at 9:26 pm
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.
Gerald Britton, Pluralsight courses
July 28, 2014 at 2:43 am
Agree 🙂
July 28, 2014 at 5:03 am
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.
Thanks
July 28, 2014 at 5:12 am
What if your period are 5-4-4?
July 28, 2014 at 5:26 am
As written, your code would leave out any transactions datestamped for the start time of the period:
WHERE
d.DateFirstIssued > fy.FYQ2
Shouldn't the operator be >= to include the start date and time?
July 28, 2014 at 5:58 am
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.
Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2014 at 6:14 am
sslyle-1091060 (7/28/2014)
What if your period are 5-4-4?
Good question and would make for a good article to exploit the ISO_Week datepart (came out with 2008) of the datetime functions. The question is, what do you want done with years that have 53 fiscal weeks in them? One company I worked at used 6-4-4, 5-4-4, 5-4-4, 5-4-4 and another used 5-4-4, 5-4-4, 5-4-4, 5-4-5.
Of course, I've also seen some non-US companies that started their work-week on Thursdays instead of Mondays and a lot of US companies that start their work week on Sundays instead of Mondays (working for one right now), which always makes things a bit more interesting in calculating 5-4-4 quarters.
To be honest, I think calculating things by quarter is a bit archaic now. It was done to make reporting by accountants a whole lot easier in the long gone days before computers.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2014 at 6:46 am
Maybe I'm just being a bit dense but I would rather use the below script to generate instead of relying on the datediff function and working back from today's date... obviously the below has more columns than you would need for explanation - can anyone point out why mine wouldn't be the preferred route?
--example table with dates to sort by Fin Year and Fin Quarter
CREATE TABLE #DatesToTest
(
TestDate DATE NOT NULL
,FinYear VARCHAR(5) NULL
, FinYearStart DATE NULL
,FinQuarter1Start DATE NULL
,FinQuarter2Start DATE NULL
,FinQuarter3Start DATE NULL
,FinQuarter4Start DATE NULL
,FinQuarter INT
)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE())
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-365)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-180)
DECLARE @FinYearStart VARCHAR(20)
SET @FinYearStart = '07-01' -- Use this variable to define the beginning of your financial year
--PRINT @FinYearStart
--calculate financial year based off of financial year start date
SELECT * FROM [#DatesToTest] AS DTT
UPDATE [#DatesToTest]
SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-'+@FinYearStart AS DATE)
THEN YEAR([TestDate])
ELSE YEAR([TestDate])-1
END
SELECT * FROM [#DatesToTest] AS DTT
--Set financial year start date into table for easier access and quarter formula
UPDATE [#DatesToTest]
SET FinYearStart= CAST([FinYear]+'-'+@FinYearStart AS DATE)
SELECT * FROM [#DatesToTest] AS DTT
--set the financial year quarter dates for easy calculations
UPDATE [#DatesToTest]
SET [FinQuarter1Start] = [FinYearStart]
,[FinQuarter2Start] = DATEADD(MONTH,3,[FinYearStart])
,[FinQuarter3Start] = DATEADD(MONTH,6,[FinYearStart])
,[FinQuarter4Start] = DATEADD(MONTH,9,[FinYearStart])
SELECT * FROM [#DatesToTest] AS DTT
--check which quarter the date falls into
UPDATE [#DatesToTest]
SET [FinQuarter] = CASE WHEN [TestDate] >= [FinQuarter4Start] THEN 4
WHEN [TestDate] >= [FinQuarter3Start] THEN 3
WHEN [TestDate] >= [FinQuarter2Start] THEN 2
ELSE 1 END
SELECT * FROM [#DatesToTest] AS DTT
--tidy up
DROP TABLE [#DatesToTest]
July 28, 2014 at 6:57 am
paul.pennington (7/28/2014)
As written, your code would leave out any transactions datestamped for the start time of the period:WHERE
d.DateFirstIssued > fy.FYQ2
Shouldn't the operator be >= to include the start date and time?
Agree, thanks
July 28, 2014 at 6:58 am
chris.johnson 1969 (7/28/2014)
Maybe I'm just being a bit dense but I would rather use the below script to generate instead of relying on the datediff function and working back from today's date... obviously the below has more columns than you would need for explanation - can anyone point out why mine wouldn't be the preferred route?
--example table with dates to sort by Fin Year and Fin Quarter
CREATE TABLE #DatesToTest
(
TestDate DATE NOT NULL
,FinYear VARCHAR(5) NULL
, FinYearStart DATE NULL
,FinQuarter1Start DATE NULL
,FinQuarter2Start DATE NULL
,FinQuarter3Start DATE NULL
,FinQuarter4Start DATE NULL
,FinQuarter INT
)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE())
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-365)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-180)
DECLARE @FinYearStart VARCHAR(20)
SET @FinYearStart = '07-01' -- Use this variable to define the beginning of your financial year
--PRINT @FinYearStart
--calculate financial year based off of financial year start date
SELECT * FROM [#DatesToTest] AS DTT
UPDATE [#DatesToTest]
SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-'+@FinYearStart AS DATE)
THEN YEAR([TestDate])
ELSE YEAR([TestDate])-1
END
SELECT * FROM [#DatesToTest] AS DTT
--Set financial year start date into table for easier access and quarter formula
UPDATE [#DatesToTest]
SET FinYearStart= CAST([FinYear]+'-'+@FinYearStart AS DATE)
SELECT * FROM [#DatesToTest] AS DTT
--set the financial year quarter dates for easy calculations
UPDATE [#DatesToTest]
SET [FinQuarter1Start] = [FinYearStart]
,[FinQuarter2Start] = DATEADD(MONTH,3,[FinYearStart])
,[FinQuarter3Start] = DATEADD(MONTH,6,[FinYearStart])
,[FinQuarter4Start] = DATEADD(MONTH,9,[FinYearStart])
SELECT * FROM [#DatesToTest] AS DTT
--check which quarter the date falls into
UPDATE [#DatesToTest]
SET [FinQuarter] = CASE WHEN [TestDate] >= [FinQuarter4Start] THEN 4
WHEN [TestDate] >= [FinQuarter3Start] THEN 3
WHEN [TestDate] >= [FinQuarter2Start] THEN 2
ELSE 1 END
SELECT * FROM [#DatesToTest] AS DTT
--tidy up
DROP TABLE [#DatesToTest]
There is case in my situation that only pure SELECT is allowed in the query, not even DECLARE, ORDER, ...
July 28, 2014 at 7:03 am
So how about the below instead? Is there an overhead doing it this way?
--example table with dates to sort by Fin Year and Fin Quarter
CREATE TABLE #DatesToTest
(
TestDate DATE NOT NULL
,FinYear VARCHAR(5) NULL
, FinYearStart DATE NULL
,FinQuarter1Start DATE NULL
,FinQuarter2Start DATE NULL
,FinQuarter3Start DATE NULL
,FinQuarter4Start DATE NULL
,FinQuarter INT
)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE())
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-365)
INSERT [#DatesToTest]
( [TestDate])
VALUES ( GETDATE()-180)
--calculate financial year based off of financial year start date
SELECT * FROM [#DatesToTest] AS DTT
UPDATE [#DatesToTest]
SET [FinYear] = CASE WHEN [TestDate]>=CAST(CAST(YEAR([TestDate]) AS CHAR(4))+'-07-01' AS DATE)
THEN YEAR([TestDate])
ELSE YEAR([TestDate])-1
END
SELECT * FROM [#DatesToTest] AS DTT
July 28, 2014 at 7:07 am
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
July 28, 2014 at 7:08 am
I'm not sure what that means, but my philosophy is to let the business tell me what their fiscal periods are and give me the data in a spreadsheet. I just load up what I get.
July 28, 2014 at 7:09 am
Jeff Moden (7/28/2014)
g.britton (7/27/2014)
Much better to use a date dimension table that contains all relevant date info for your company. You can include columns like 'IsHoliday' or 'IsWeekend' as well as fiscal half-year, quarter, week and day and anything else that is useful to your company. Use a PK of yyyymmdd as an integer. Update the table as needed (maybe only once a year) and join it to your queries instead of calculating these things every time. Avoid errors from the get-go.Agreed but, particularly if your fiscal year was different than the calendar year, guess what kind of formulas you'd use to make such a table. 😉
Why use formulas at all? I just get the business subject matter expert to give me spreadsheet each year with the values filled in. I bear no responsibility for determining those values. I merely load up what I've been given with a simple SSIS job with some simple sanity checks (e.g. February can't have 30 days and can only have 29 days on Leap years (that's one formula I don't mind coding!)). It takes the SME less than an hour a year to generate the spreadsheet and only a few seconds to load up the new year.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply