September 15, 2015 at 1:10 am
Hello
I have a report that uses the Datepart function to return the current, and previous, year's data:
DATEPART(YEAR, a.Date);
DATEPART(YEAR, a.Date) = DATPART(YEAR, GetDate())-1
This brings through calendar year fine.
Is it possible to amend this to bring through the financial year (e.g. 1/4/15 - 31/3/16)?
Any advice much appreciated.
September 15, 2015 at 1:34 am
Could you give a couple of examples of your expected output? It's just a lot easier to code from that. It sounds like you could use something like this:...
(Sorry, used a database I had kicking around...)
SELECT TOP 20 OrderID
, OrderDate
, DATEPART(QUARTER,OrderDate) AS OrigQtr
, DATEPART(QUARTER,DATEADD(MONTH,3,OrderDate)) AS FiscalQtr
, YEAR(OrderDate) AS OrigYr
, YEAR(DATEADD(MONTH,3,OrderDate)) AS FY
FROM Sales.Orders
If you have to do this in SSRS, you could just create FY and FQ as calculated columns, and you'd be sorted, right?
September 15, 2015 at 2:09 am
pietlinden, finanacial year start from April and end on March of the next year,
your query will not calculate properl FY
please check this
declare @OrderDate datetime = '2000-04-01'
select
DATEPART(QUARTER,@OrderDate) AS OrigQtr
, DATEPART(QUARTER,DATEADD(MONTH,3,@OrderDate)) AS FiscalQtr
, YEAR(@OrderDate) AS OrigYr
, YEAR(DATEADD(MONTH, 3, @OrderDate)) AS FY
---- Required Result
select
case when @OrderDate >= 4 then datepart(yy, @OrderDate) + 1 else datepart(yy, @OrderDate) end AS FY
September 15, 2015 at 2:26 am
faulknerwilliam2 (9/15/2015)
DATEPART(YEAR, a.Date) = DATPART(YEAR, GetDate())-1
Whatever method you use, I expect you will find that
WHERE a.Date >= @FYStartDate
AND a.Date < @FYEndDatePlusOneDay
performs more efficiently - especially if you have an Index on a.Date
(If a.Date IS a DATE datatype, rather than DATETIME, then you can do
WHERE a.Date >= @FYStartDate
AND a.Date <= @FYEndDate
)
September 15, 2015 at 4:14 am
faulknerwilliam2 (9/15/2015)
HelloI have a report that uses the Datepart function to return the current, and previous, year's data:
DATEPART(YEAR, a.Date);
DATEPART(YEAR, a.Date) = DATPART(YEAR, GetDate())-1
This brings through calendar year fine.
Is it possible to amend this to bring through the financial year (e.g. 1/4/15 - 31/3/16)?
Any advice much appreciated.
Following expression will do
IIF(DATEPART(MONTH, a.Date) >= 4, DATEPART(YEAR, a.Date) + 1, DATEPART(YEAR, a.Date))
hope it helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply