SQLTeam.com | Weblogs | Forums

Fiscal Year To Date


#1

So the Fiscal Year for this company is Apr - Mar
They would like to see MTD and YTD from last fiscal year and this fiscal year for comparison reporting.

[code]
= (CASE WHEN MONTH(GETDATE()) < 3 THEN DATEFROMPARTS(YEAR(GETDATE())-1 , 3 , 31) ELSE DATEFROMPARTS(YEAR(GETDATE()) , 3 , 31) END)

= (CASE WHEN MONTH(GETDATE()) < 3 THEN DATEFROMPARTS(YEAR(GETDATE()) , 3 , 31) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 3 , 31) END)[/code]

Unfortunately the above code does not work in 2008
What's the code to do the same in 2008?


#2
select 
    (CASE WHEN MONTH(GETDATE()) < 3 THEN CAST(CAST(YEAR(GETDATE())-1 AS VARCHAR(4))+'0331' AS DATETIME) 
      ELSE CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))+'0331' AS DATETIME) END) as dt_1
,(CASE WHEN MONTH(GETDATE()) < 3 THEN CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4))+'0331' AS DATETIME) 
      ELSE CAST(CAST(YEAR(GETDATE())+1 AS VARCHAR(4))+'0331' AS DATETIME) END) as dt_2
dt_1 dt_2
31/03/2018 00:00:00 31/03/2019 00:00:00

#3

thank you

Perhaps the code I had is incorrect.
I tried what you've shared and I'm only getting March 31, 2018 data.

Need MTD and YTD from LAST fiscal year and THIS fiscal year, rolling forward.

So it should return
4/1/2017 - 4/16/2017 AND 4/1/2018 - 4/16/2018 today. and so on
where
next month, say on the 11th, it should return
5/1/2017-5/10/2017 AND 5/1/2018-5/10/2018 for MTD
AND 4/1/2017-5/10/2017 plus 4/1/2018-5/10/2018
and continue through the end of the fiscal year then repeat but next Apr it'll show 2018 and 2019, etc ...


#4

Hi,

What you posted initially it's using GETDATE().
Add some input data and what it's the desired output.


#5

? DateFromParts does not work in 2008

How about this, what's the correct syntax for:
Last year - so something like DateAdd(yyyy, -1, Getdate())?
Last year Month to be - 4/1/previousyear using the above for the year to date so 4/18/previous year
This year - so DateAdd(yyyy, 0, Getdate())
current month to date - DATEADD(MONTH, DATEDIFF(MONTH, 0, GetDate()-1), 0) this provides current month to date


#6

It could also be because I am using it against:

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END


#7

This will get you the start of current month:

dateadd(month,datediff(month,0,current_timestamp),0)

This will get you the start of current fiscal year:

dateadd(year,datediff(month,90,current_timestamp)/12,90)

#8

This is giving what I looking for as far as MTD last year (LMTD) and MTD this year
Is there's a way to run this

>= DATEADD(YEAR, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND [highlight #FCE94F]< DATEADD(YEAR, - 1, GETDATE() - 1)[/highlight]
>= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND [highlight #FCE94F]< GETDATE() - 1[/highlight]

against this?

CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1 , 7) THEN dbo.DAYSADDNOWK(OrderDate , 1) ELSE DATEADD(dd , 0 , DATEDIFF(dd , 0 , dbo.SO_SalesOrderHistoryHeader.OrderDate)) END

And then a way to get 4/1/lastyear to date last year and 4/1/thisyear to date in the current year as we go into other months?
Meaning next month will have 4/1/201# - May, etc ... in both years?
Set it to always start on 4/1/#### since that's the start of the fiscal year.


#9

Thanks! dateadd(year,datediff(month,90,current_timestamp)/12,90)

I'm having trouble modifying that for last year, apparently this isn't correct

dateadd(year,-1, datediff(month,90,current_timestamp)/12,90)


#10

Change this:

to this:

dateadd(year,-1 + datediff(month,90,current_timestamp)/12,90)

#11

Excellent! Thank you so much!

If there's a way to always get LMTD and MTD out of LYTD and YTD?
They want to see LYTD and YTD AND LMTD and MTD


#12

Thanks again!
Here's the full, in case someone else needs it like I did

Last Fiscal Year beginning April where weekends are marked as Monday = (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHistoryHeader.OrderDate)) END BETWEEN DATEADD(year, - 1 + DATEDIFF(month, 90, GETDATE()) / 12, 90) AND DATEADD(YEAR, - 1, GETDATE() - 1)) 
OR
This Fiscal Year beginning April where weekends are marked as Monday = (CASE WHEN { fn DAYOFWEEK(dbo.SO_SalesOrderHistoryHeader.OrderDate) } IN (1, 7) THEN dbo.DAYSADDNOWK(OrderDate, 1) ELSE DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.SO_SalesOrderHistoryHeader.OrderDate)) END BETWEEN DATEADD(year, DATEDIFF(month, 90, GETDATE()) / 12, 90) AND GETDATE() - 1)