January 16, 2019 at 10:48 am
Hi,
I am using below query to get the data by fiscal year . I want to get the data for only last 2 fiscal years (i.e starting from Feb 2017 till now).Right now i have year hardcoded. any way to get it using sql functions?
SELECT 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) where fiscal_year>'2017'
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter
Thanks
January 16, 2019 at 11:09 am
Papil - Wednesday, January 16, 2019 10:48 AMHi,
I am using below query to get the data by fiscal year . I want to get the data for only last 2 fiscal years (i.e starting from Feb 2017 till now).Right now i have year hardcoded. any way to get it using sql functions?
SELECT 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) where fiscal_year>'2017'
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarterThanks
Please supply the DDL for your source table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 16, 2019 at 3:17 pm
You should avoid using functions on columns in WHERE and ON clauses and your fiscal_year is actually shorthand for a function on a column, so you should rewrite your WHERE clause to not use a function. I'll leave it to you to try to figure out what the correct expression is.
WHERE addeddate >= <some expression>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2019 at 7:16 am
drew.allen - Wednesday, January 16, 2019 3:17 PMYou should avoid using functions on columns in WHERE and ON clauses and your fiscal_year is actually shorthand for a function on a column, so you should rewrite your WHERE clause to not use a function. I'll leave it to you to try to figure out what the correct expression is.
WHERE addeddate >= <some expression>Drew
I meant function for date. I have already put the where condition in the sql i posted. I need expression for beginning of fiscal year 2 years from now. Starting 2017 Q1. i can put " where addeddate>'2017 Q1' " but is there any other expression i can use?
January 17, 2019 at 8:58 am
Papil - Thursday, January 17, 2019 7:16 AMdrew.allen - Wednesday, January 16, 2019 3:17 PMYou should avoid using functions on columns in WHERE and ON clauses and your fiscal_year is actually shorthand for a function on a column, so you should rewrite your WHERE clause to not use a function. I'll leave it to you to try to figure out what the correct expression is.
WHERE addeddate >= <some expression>Drew
I meant function for date. I have already put the where condition in the sql i posted. I need expression for beginning of fiscal year 2 years from now. Starting 2017 Q1. i can put " where addeddate>'2017 Q1' " but is there any other expression i can use?
There is a well-known formula for calculating the beginning of the current calendar year. You've been shown how to adjust calendar year formulas to work with fiscal years. Given the beginning of this fiscal year, you should be able to easily calculate the beginning of last fiscal year. See if you can put it all together and then use that in your WHERE clause.
WHERE addeddate >= <beginning of last fiscal year>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2019 at 3:21 pm
The last time I looked, the GAAP had over 157 different fiscal years. The best way to do this is to set up a calendar table that maps your fiscal year weeks and fiscal year months back into the standard common error calendar. Remember that, SQL is a data language, not a computational language. Trying to do this sort of thing with temporal computations is like trying to alphabetize Chinese. The fundamental structure of your tool doesn't support the concept you are trying to use.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply