Year to last month (YTD) comparison to Last Year to last month (LYTD) 1

[supportsvc]

I can't seem to get YTD for beginning of the year to last month and last year to last month

2017 would include Jan - Mar = see highlighted, that seems to work
2018 would include Jan - Mar = this section does not

WHERE        [highlight #FCE94F](dbo.SO_SalesOrderHistoryHeader.OrderDate <= DATEADD(YEAR, - 1, DATEADD(mm, - 1, GETDATE())))[/highlight]  OR
(dbo.SO_SalesOrderHistoryHeader.OrderDate <= DATEADD(mm, - 1, GETDATE()))
HAVING        [highlight #FCE94F](YEAR(dbo.SO_SalesOrderHistoryHeader.OrderDate) = YEAR(GETDATE()) - 1)[/highlight] AND
                   (YEAR(dbo.SO_SalesOrderHistoryHeader.OrderDate) = YEAR(GETDATE()))

When the current year is removed, I get what I need for 2017, but when adding the current year criteria, I get all of last year only.

 

[supportsvc]

Hello,
I never requested to result in END of month.
It's always been MTD (Month to Date) ...

I think the code I posted needs modification
I am getting all of 2017-2018 Fiscal Year Apr 2017 - Mar 2018 <-- just want Apr to Date in 2017 (4/1 - 4/17/2017)
And Apr to date 2018 <-- this is correct

then in May
1) Apr 2017 - May to DATE 2017
2) Apr 2018 - May to DATE 2018

Does that make better sense?

Again, it's always been MTD, the change is that the YTD, fiscal year is Apr-Mar, not Jan-Dec.
And all of the codes always resulted to the end of the month on the prior year. Which was never the request.

 

[Olaf Doschke]

I actually think you need far less than you described and since we are in April all you need is the full last fiscal year from April 2017 to March 2018. The current YTD taken from April 1st has no month, as April is still current. What would help best is describing what timespans you want to query and compare in simple full English sentences.

Bye, Olaf.

 

[supportsvc]

Today is 4/17/2018

Looking for results from query in the following: not sure if it's possible or 2 separate queries
Last Year MTD = 4/1/2017 - 4/17/2017 (Last year month to date, since today is 4/17)
This Year MTD = 4/1/2018 - 4/17/2018 (This year month to date, since today is 4/17)

This should be the easiest since we're in the 1st month of the fiscal year ... gets more complex as the months roll on ...

Next month
LYMTD = 5/1/2017 - 5/5/2017 (running it on 5/5/2018, since the report will run on a weekly basis to see MTD results)
TYMTD = 5/1/2018 - 5/5/2018 (running it on 5/5/2018, since the report will run on a weekly basis to see MTD results)

However, the YTD in May and rest of the months in the fiscal year would show
Last year to date = 4/1/2017 - 5/5/2017
This year to date = 4/1/2018 - 5/5/2018
(running it on 5/5/2018, since the report will run on a weekly basis to see YTD and MTD results)

but need to show 5/1-5/5 for both years for the MTD. So may need 2 queries. One to handle MTD and another for YTD?

 

[supportsvc]

Also, I think the issue is I'm trying to run 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

 

[supportsvc]

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.

 

[supportsvc]

Got it figured out with help from SQL TEAM
by bitsmed2h
with

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)

Was able to get last year MTD and this year MTD of THEIR Fiscal Year (Apr-Mar)

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)