Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Handle Calculations Related to fiscal year and quarter


How to Handle Calculations Related to fiscal year and quarter

Author
Message
halifaxdal
halifaxdal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1744
Comments posted to this topic are about the item How to Handle Calculations Related to fiscal year and quarter
g.britton
g.britton
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1319 Visits: 1763
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, MCSE-DP, MVP
Toronto PASS Chapter
Carrie.Sim
Carrie.Sim
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 141
Agree :-)
halifaxdal
halifaxdal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1744
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
sslyle-1091060
sslyle-1091060
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 32
What if your period are 5-4-4?
paul.pennington
paul.pennington
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 123
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70964 Visits: 40742
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70964 Visits: 40742
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
chris.johnson 1969
chris.johnson 1969
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 42
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]


halifaxdal
halifaxdal
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1744
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search