January 14, 2019 at 5:48 am
Hi,
I am trying to get the count of data by fiscal quarters where Q1 starting from Feb. My below query works does not work for Fiscal qtrs. Can someone help to get the count by fiscal and not regular qtrs.
SELECT TOP 4 DATEPART(YEAR,addeddate) [Year],
DATEPART(QUARTER,addeddate) [Quarter], count(id) ID
FROM test
GROUP BY DATEPART(YEAR,AddedDate),DATEPART(QUARTER,AddedDate)
ORDER BY 1 desc,2
Thanks.Thanks.
January 14, 2019 at 6:42 am
Hi,
You cannot base your query on a regular quarter if you don't use it.
You need to compute your customized quarter to a parameter table based on the month, eg: SELECT RIGHT('00' + YEAR(addeddate), 4) + RIGHT(('00' + CAST(MONTH(addeddate) AS varchar)), 2) AS MonthOfTheDate,
CASE
WHEN MONTH(addeddate) IN (2,3,4) THEN 1
WHEN MONTH(addeddate) IN (5,6,7) THEN 2
WHEN MONTH(addeddate) IN (8,9,10) THEN 3
WHEN MONTH(addeddate) IN (11,12, 1) THEN 4
END
AS QuarterOfTheDate
FROM test
And you loop on this table with the month of your added date . You can certainly put the table above in a CTE, if you prefer...
Johan Jezequel
** Sorry for my english, I promess you I do my best **
January 14, 2019 at 9:37 am
People often overcomplicate fiscal calculations, because the calendar year is so engrained in our psyches. I think of fiscal years as a special type of DATETIMEOFFSET where instead of the offset being in hours and minutes, it's in months (and sometimes days). In your case, you are dealing with a fiscal year with an offset of one month, so to do calculations, you need to account for that offset. I am using the DATEADD() function to do so.
SELECT TOP 4 fiscal_year AS [Year]
, fiscal_quarter AS [Quarter]
, COUNT(id) AS ID
FROM test
CROSS APPLY ( VALUES(DATEPART(YEAR, DATEADD(MONTH, -1, addeddate)), DATEPART(QUARTER, DATEADD(MONTH, -1, addeddate))) ) f(fiscal_year, fiscal_quarter)
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 14, 2019 at 10:15 am
Really depends on how the company has defined their fiscal years. In this case it could be subtracting 1 month or it could be adding 11 months. It depends.
January 14, 2019 at 12:25 pm
Lynn Pettis - Monday, January 14, 2019 10:15 AMReally depends on how the company has defined their fiscal years. In this case it could be subtracting 1 month or it could be adding 11 months. It depends.
I don't think so. If it were shifted by 11 months, then the OP would most likely have been using DATEPART(YEAR,addeddate) + 1 instead of just DATEPART(YEAR,addeddate) for their calculations. I also suspect that human nature tends to keep the fiscal year in sync with the calendar year as much as possible, which again argues for subtracting 1 month instead of adding 11 ( or subtracting 1 year and 1 month or adding 1 year and 11 months or ...). I haven't done a survey, but I would expect that when the fiscal year begins in Feb-Jun, the overwhelming consensus would be to subtract and when it begins in Aug-Dec the overwhelming consensus would be to add, and it would only be July that could theoretically go either way, HOWEVER a major entity such as the US or state government could skew those results in that organizations dealing with said entity will be inclined to use the same offset as said entity.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 14, 2019 at 12:50 pm
drew.allen - Monday, January 14, 2019 9:37 AMPeople often overcomplicate fiscal calculations, because the calendar year is so engrained in our psyches. I think of fiscal years as a special type of DATETIMEOFFSET where instead of the offset being in hours and minutes, it's in months (and sometimes days). In your case, you are dealing with a fiscal year with an offset of one month, so to do calculations, you need to account for that offset. I am using the DATEADD() function to do so.
SELECT TOP 4 fiscal_year AS [Year]
, fiscal_quarter AS [Quarter]
, COUNT(id) AS ID
FROM test
CROSS APPLY ( VALUES(DATEPART(YEAR, DATEADD(MONTH, -1, addeddate)), DATEPART(QUARTER, DATEADD(MONTH, -1, addeddate))) ) f(fiscal_year, fiscal_quarter)
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarterDrew
thanks it works fine.
January 14, 2019 at 3:12 pm
drew.allen - Monday, January 14, 2019 12:25 PMLynn Pettis - Monday, January 14, 2019 10:15 AMReally depends on how the company has defined their fiscal years. In this case it could be subtracting 1 month or it could be adding 11 months. It depends.I don't think so. If it were shifted by 11 months, then the OP would most likely have been using DATEPART(YEAR,addeddate) + 1 instead of just DATEPART(YEAR,addeddate) for their calculations. I also suspect that human nature tends to keep the fiscal year in sync with the calendar year as much as possible, which again argues for subtracting 1 month instead of adding 11 ( or subtracting 1 year and 1 month or adding 1 year and 11 months or ...). I haven't done a survey, but I would expect that when the fiscal year begins in Feb-Jun, the overwhelming consensus would be to subtract and when it begins in Aug-Dec the overwhelming consensus would be to add, and it would only be July that could theoretically go either way, HOWEVER a major entity such as the US or state government could skew those results in that organizations dealing with said entity will be inclined to use the same offset as said entity.
Drew
You also have to consider that some organizations use a 4-4-5 (4-5-4 or 5-4-4) calendar - which breaks out each quarter into 13 week periods. Each period will end on the same day of the week which will not be the end of a calendar month and won't start on the 1st of the month in all cases.
If the organization uses a 4-4-5, then you need to count 13 weeks from the first day of the quarter even if that day starts on the last day of the previous year. And - if this year is a catch up year you have to account for that extra week...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 15, 2019 at 6:58 am
Papil - Monday, January 14, 2019 5:48 AMA useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods
(report_name CHAR(10) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is 'yyyyyyyy-mm-00' for a month within a year and 'yyyyyyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design reportreport name column.
[/quote]
(function(){(function(e){if(e){var t=e.cloneNode;e.cloneNode=function(n){var o=t.call(e,n);if(e.classList.contains("mceContentBody"))o.innerHTML=e.innerHTML,r(o);else try{i(o)}catch(e){}return o},i(e)}function n(e){if(e.parentNode)if(e.childNodes.length>1){for(var t=document.createDocumentFragment();e.childNodes.length>0;)t.appendChild(e.childNodes[0]);e.parentNode.replaceChild(t,e)}else e.firstChild?e.parentNode.replaceChild(e.firstChild,e):e.parentNode.removeChild(e)}function r(e){if(e)try{for(var t=e.querySelectorAll(".gr_"),r=t.length,i=0;i<r;i++)n(t)}catch(e){}}function i(e){try{Object.defineProperty(e,"innerHTML",{get:function(){try{var t=e.ownerdocument.createRange();t.selectNodeContents(e);var n=t.cloneContents(),i=document.createElement("div");return i.appendChild(n),r(i),i.innerHTML}catch(e){return""}},set:function(t){try{var n=e.ownerdocument.createRange();n.selectNodeContents(e),n.deleteContents();var r=n.createContextualFragment(t);e.appendChild(r)}catch(e){}}})}catch(e){}}})(document.querySelector("[data-gramm_id='9a30941c-8ae7-8d1e-ea75-016984a26747']")) })()[/code]These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. I like the MySQL convention of using double zeroes for months and years, That is '-mm-00' for a month within a year and '-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. Remember that when you design name column.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 15, 2019 at 7:27 am
We used a 4-4-5 fiscal quarter.
Building a time dimension is usable in both sql and cube.
With 4-4-5 variations you also have a couple extra days possibly moved to a different wk/yr.
If the business uses a fiscal calendar, it is good if all reporting uses that standard.
January 15, 2019 at 8:16 am
drew.allen - Monday, January 14, 2019 12:25 PMLynn Pettis - Monday, January 14, 2019 10:15 AMReally depends on how the company has defined their fiscal years. In this case it could be subtracting 1 month or it could be adding 11 months. It depends.I don't think so. If it were shifted by 11 months, then the OP would most likely have been using DATEPART(YEAR,addeddate) + 1 instead of just DATEPART(YEAR,addeddate) for their calculations. I also suspect that human nature tends to keep the fiscal year in sync with the calendar year as much as possible, which again argues for subtracting 1 month instead of adding 11 ( or subtracting 1 year and 1 month or adding 1 year and 11 months or ...). I haven't done a survey, but I would expect that when the fiscal year begins in Feb-Jun, the overwhelming consensus would be to subtract and when it begins in Aug-Dec the overwhelming consensus would be to add, and it would only be July that could theoretically go either way, HOWEVER a major entity such as the US or state government could skew those results in that organizations dealing with said entity will be inclined to use the same offset as said entity.
Drew
Sure, you go with that.
January 15, 2019 at 9:20 am
Year to Date vs. some target and Year Over Year might be worth some thought.
Kind of beyond the scope of the original post, but very real questions that are likely to come up.
Which leads to thinking about more scalable solutions.
Setting an arbitrary date in your code while time continues to advance might lead to some extra attention some morning.
I came from a data warehouse where even things like a nightly process running late (after midnight) could affect things.
And or goal was lights out prosesses as we liked to sleep and not come into a problem.
Just a different way of thinking.
And at times (actually quite often) the next question was easy as the base foundation was in place.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply