December 15, 2016 at 12:49 am
Hello
I want to arrive at an output like 2011-Q4 (Financial Yr-Qtr)
I can do this by:
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((YEAR(MyDate) - 1), '-', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((YEAR(MyDate) - 1), '-', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((YEAR(MyDate) - 0), '-', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((YEAR(MyDate) - 0), '-', 'Q2')
END AS FYrQtr
But can the same output be achieved without using CONCAT? (I only have 2008 at work; CONCAT arrived in 2012).
Thanks.
December 15, 2016 at 1:24 am
faulknerwilliam2 (12/15/2016)
HelloI want to arrive at an output like 2011-Q4 (Financial Yr-Qtr)
I can do this by:
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((YEAR(MyDate) - 1), '-', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((YEAR(MyDate) - 1), '-', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((YEAR(MyDate) - 0), '-', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((YEAR(MyDate) - 0), '-', 'Q2')
END AS FYrQtr
But can the same output be achieved without using CONCAT? (I only have 2008 at work; CONCAT arrived in 2012).
Thanks.
Does this do what you want?
DECLARE @example TABLE
(
MyDate DATETIME
)
INSERT INTO @example VALUES
('2011-01-01') --- Q3
,('2011-04-01') ----Q4
,('2011-07-01') --- Q1
,('2011-11-01')--- Q2
;
SELECT
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN CAST(YEAR(MyDate) - 1 AS CHAR(4))+ '-Q3'
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN CAST(YEAR(MyDate) - 1 AS CHAR(4))+ '-Q4'
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN CAST(YEAR(MyDate) - 0 AS CHAR(4))+ '-Q1'
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN CAST(YEAR(MyDate) - 0 AS CHAR(4)) + '-Q2'
END AS FYrQtr
FROM @example;
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
December 15, 2016 at 2:46 am
Personally, I would always use a date dim for this type of thing. There are many scripts out there which should do most of the donkey work for you.
Firstly, you only have to do the calculations once, when you populate the dim, after that it's a simple join, so it's fast. Secondly, if you're working for a company, financial years can change. If you have this hardcoded in sp's all over your codebase you could be in for a bit of a 'mare if it happens (I've worked for a sompany that was taken over twice within the space of a year, both parent companies had different financial year ends). then there's the possiblity of inconsistencies in application if you have it in many places.
If you've got a date dim, you just rename the column xxxFinancialYear (the beancounters will almost certainly want to mix and match FY comparisons) and add in your new FY column. Much much easier, that ability was a lifesaver for me in the scenario I just mentioned.
I have to say, many people do hate date dims, for reasons I find hard to fathom, but I have always found they work very well indeed for my use cases
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 15, 2016 at 9:32 am
BWFC (12/15/2016)
faulknerwilliam2 (12/15/2016)
HelloI want to arrive at an output like 2011-Q4 (Financial Yr-Qtr)
I can do this by:
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((YEAR(MyDate) - 1), '-', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((YEAR(MyDate) - 1), '-', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((YEAR(MyDate) - 0), '-', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((YEAR(MyDate) - 0), '-', 'Q2')
END AS FYrQtr
But can the same output be achieved without using CONCAT? (I only have 2008 at work; CONCAT arrived in 2012).
Thanks.
Does this do what you want?
DECLARE @example TABLE(
MyDate DATETIME
)
INSERT INTO @example VALUES
('2011-01-01') --- Q3
,('2011-04-01') ----Q4
,('2011-07-01') --- Q1
,('2011-11-01')--- Q2
;
SELECT
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN CAST(YEAR(MyDate) - 1 AS CHAR(4))+ '-Q3'
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN CAST(YEAR(MyDate) - 1 AS CHAR(4))+ '-Q4'
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN CAST(YEAR(MyDate) - 0 AS CHAR(4))+ '-Q1'
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN CAST(YEAR(MyDate) - 0 AS CHAR(4)) + '-Q2'
END AS FYrQtr
FROM @example;
The formulas can be greatly simplified by mapping the fiscal year to the calendar year first and then doing the calculations instead of doing the calculations first and then trying to map the individual parts to the correct results.
Here we want to map the beginning of the fiscal year (July 1) to the beginning of the calendar year (January 1) by subtracting six months (or two quarters) and then the calculations are easy, because the years and quarters are already lined up.
SELECT CONVERT(CHAR(4), fiscal_date, 120) + '-Q' + CAST(DATEPART(QUARTER, f.fiscal_date) AS CHAR(1))
FROM @example e
CROSS APPLY (VALUES(DATEADD(MONTH, -6, e.MyDate))) f(fiscal_date)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2016 at 2:56 pm
Thanks for the suggestion.
December 15, 2016 at 2:59 pm
I will check that out Drew; thanks for taking the trouble to reply.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply