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.

 

[Olaf Doschke]

You mix up WHERE and HAVING, that's wrong in itself. First of all HAVING needs GROUP BY, which you'll surely have, but then mainly offers you to filter by values of aggregated date. A ckassic HAVING condition is for finding double data via HAVING COUNT(*)>0. You put the conditions of begin1, end1, begin2 and end2 dates of the two spans you're interested in the WHERE condition only. You use HAVING rather in the natural language sense "having this year or last year", but that's not what it's meant for.

The condition you need are: WHERE (date between begin1 and end1) OR (date between begin2 and end2) or as many prefer without BETWEEN: WHERE (date >= begin1 and date<end1) or (date >= begin2 and date<end2). In this case end1 and end2 are actually exactly the begin of months outside the intervals so <end1/end2 is just before that. Sidenote: BETWEEN always includes endpoints and is often avoided for datetime intervals, though the "new" type date helps to overcome problems of wrong cut offs.

Next step: Just look at the datetimes you generate:

SELECT DATEADD(YEAR, - 1, DATEADD(mm, - 1, GETDATE()))
--Output: 2017-03-13 07:02:08.443

You rather seek the begin of this month backdated to last year, 2017-04-01 00:00:00.000, so this is off some days and the time portion needs to be nulled. You want 1st April instead of 1st March, as you want to include March data.

SELECT DATEADD(mm, - 1, GETDATE())
--Output: 2018-03-13 07:02:08.443

Will also not work for this year for same reasons.

There are famous expressions for end of month, begin of month, etc. you just have to google them and put them in. Besides since about 6 years there is an EOMONTH function, but I'll not adapt to it, since I don't know your server version, so I go with the classic expressions:

--2018 end date:
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
--2017 end date
SELECT DATEADD(month, DATEDIFF(month, 0, Dateadd(year,-1,GETDATE())), 0)

You also need begin of years to filter out anything April 2017-December 2017 and anything before 2017, too.

--2018 begin:
SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
--2017 begin
SELECT DATEADD(month, DATEDIFF(month, 0, Dateadd(year,-1,GETDATE())), 0)

Ýou tried to solve that with your HAVING clause and it would also work to filter for that in WHERE, but it needs to be combined with the end condition and there is one bad thing about it, you'd compute YEAR(orderdate) for every orderdate, that's not optimible with the index on orderdate, so you rather compare with the start-of-year datetimes.

--WHERE (orderdate >= begin1 and orderdate<end1) or (orderdate >= begin2 and orderdate<end2)
WHERE
(dbo.SO_SalesOrderHistoryHeader.OrderDate>=DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) 
  AND dbo.SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
)
OR
(dbo.SO_SalesOrderHistoryHeader.OrderDate>=DATEADD(year, DATEDIFF(year,-1, GETDATE()), 0) 
  AND dbo.SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(year, -1, GETDATE()), 0)
)

Bye, Olaf.

 

[supportsvc]

Only getting current year Jan-Mar

 

[Olaf Doschke]

No.

Then check, if your table actually has 2017 data:
SELECT COUNT(*) FROM ... WHERE OrderDate<'20180101'

Or did you keep your HAVING clause? What's you full query now?

Bye, Olaf.

 

[Olaf Doschke]

OK double checked timespans:

Select DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
Select DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0), DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,GETDATE())), 0)

I previously gave you the same expressions for 2017 begin and end dates.

Checking with test data:

Declare @testdata as table (orderdate datetime);

insert into @testdata values ('20170101'),('20170201'),('20170301'),('20170401'),('20180101'),('20180201'),('20180301'),('20180401')

SELECT * FROM @testdata as SO_SalesOrderHistoryHeader
WHERE
(SO_SalesOrderHistoryHeader.OrderDate>=DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) 
  AND SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
)
OR
(SO_SalesOrderHistoryHeader.OrderDate >= DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0)
  AND SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,GETDATE())), 0)
)

So your WHERE clause is (just adding back dbo):

WHERE
(dbo.SO_SalesOrderHistoryHeader.OrderDate >= DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) 
  AND dbo.SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
)
OR
(dbo.SO_SalesOrderHistoryHeader.OrderDate >= DATEADD(year, DATEDIFF(year, 0, GETDATE()) [b]-1[/b], 0)
  AND dbo.SO_SalesOrderHistoryHeader.OrderDate < DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,GETDATE())), 0)
)

Bye, Olaf.

 

[supportsvc]

Perfect! That did it

Thank you so much!

 

[gbaughma]

Select DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0), DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
Select DATEADD(year, DATEDIFF(year, 0, GETDATE())-1, 0), DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,GETDATE())), 0)

Hey... how does that work with leap-years? 2/29/2016 - 1 year... does that throw an error?

Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[Olaf Doschke]

Good question, in detail this will depend on how the part DATEADD(year,-1,GETDATE())) acts when GETDATE() is on a leap date. So let's try that with DATEADD(year,-1,'20160229'):

SELECT DATEADD(year,-1,'20160229')

This gives 28th February 2015, so it will work. 1st March would shift the end date.

Let's simply replace GETDATE() with '20160229' in all its places and see what datetimes we get:

Select DATEADD(year, DATEDIFF(year, 0, '20160229'), 0), DATEADD(month, DATEDIFF(month, 0, '20160229'), 0)
Select DATEADD(year, DATEDIFF(year, 0, '20160229')-1, 0), DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,'20160229')), 0)

That works out. Even on 29th February of leap years this not just gives correct datetimes for the current year, but also the past years YTD span would be January only. You need to go to 1st March to have February included in the time spans:

Select DATEADD(year, DATEDIFF(year, 0, '20160301'), 0), DATEADD(month, DATEDIFF(month, 0, '20160301'), 0)
Select DATEADD(year, DATEDIFF(year, 0, '20160301')-1, 0), DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,'20160301')), 0)

By the way, all the 0 in all these expressions have the meaning of the ultimo datetime 1900-01-01 0:00 AM and in one occasion earlier I replaced one of them with -1, which didn't work out. To make this explicitly clear you could declare a variable @ultimodate, set it to 0, and use it in the expressions. Then you're not tempted to replace it with -1 to go back one month or year.

Bye, Olaf.

 

[gbaughma]

Thank you for the clarification, Olaf! Great investigative work.



Just my $.02

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[supportsvc]

Olaf,
Thanks again on the original Year to date of last month!

Of course it changes ...
How can that be edited to show Year to date-1, then Month to date-1?

Tried this modification of your working code for last month

>= DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0) AND < DATEADD(month, DATEDIFF(month, - 1, DATEADD(year, - 1, GETDATE() - 1)), 0)
>= DATEADD(year, DATEDIFF(year, 0, GETDATE()-1), 0) AND < DATEADD(month, DATEDIFF(month, - 1, GETDATE()-1), 0)

I get all of Jan - Apr for 2017 <--- this needs to be only to MTD of last year
And of all Jan - Apr to date in 2018

 

[Olaf Doschke]

I'm always not reminded of the leap year problem, when I think of my father.

Not?

Yes, he was born on 28th February. But 1939, that wasn't a leap year.

Well, I halfways have in mind such implementations work logically with months ends. You'd get the same problem going back one month from a month with 31 days to a month with 30 days if dateadd wouldn't take that into account. But it's good to make such tests.

Bye, Olaf.

 

[Olaf Doschke]

supportsvc,

the expressions all automatically shift along to the next larger timespan at the first of each month. If you need that in the middle of a month already, I don't see how that works out nicely, as going for January to April today already means taking in April up to now, which would daily have new data and change.

If you want all full past months, that's already doing it. Put in '20180501' for all GETDATE() and you'll see, then you get Jan-Apr timespans.

Bye, Olaf.

 

[supportsvc]

ok, well, the MTD and YTD is Fiscal Year To Date

which is Apr-Mar

I was trying to use this

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.

= (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)

Unfortunately the above code does not work in 2008
Do you or anyone know what the code to do the same in 2008?

 

[Olaf Doschke]

It's staring in your face. The where clause I gave with DATEADD and DATEDIFF calculations is working in older SQL Server versions, too. The functions MONTH and DATEFROMPARTS you try to use are newer and not available in 2008. DATEADD and DATEDIFF likely even go back to SQL2000?. At least anything that's viable for today.

If you want to use fixed dates, nothing is simpler than that by using 'YYYYMMDD', so >='20170101' up to <'20180401', you don't need to know the end of months dates, as you simply can make that upper limit an excluded date, first of the next month; just by using comparison with < instead of <=. My formulas work on exactly that basis already and automatically adapt to the current month. There still is nothing to mend with it.

You currently do get 2017 Jan-Mar and 2018 Jan-Mar. This will expand to 2017 Jan-Apr and 2018 Jan-Apr in May and in January 2019 it will collapse down to no data of 2019 and 2018 and in February 2019 will be Jan of 2018 and 2019, all by the power of the expressions and GETDATE. You don't ever need to change this to any concrete dates. If you want to test this, go back to my formula and replace GATEDATE() with whatever date you want to test in the form of 'YYYYMMDD', nothing more, nothing less, just replace the occurrances of GETDATE(), literally just this function I use multiple times for each of the points in time. The start of the intervals stays at current and last years first day and ends at current month first date. The only thing that could make this better perhaps, is when it gives you full last and previous to last year in January, but then you can simply work with YERA(somedate) in (2019,2018) or such a simpler clase. Maybe the simplest change would be computing first day of years not on the basis of GETDATE() but GETDATE() shifted one month back.

Before I'd change that, please just give a sample of the dates you want and we'll figure out the expressions you need for them. It'd also be a viable approach to make use of 'YYYYMMDD' by simply constructing these strings clientside before executing as adhoc query. I wouldn't worry about adhoc being inferior to precompiled stored proc, as query time will be longer than compile time anyway and if you'd want you cold also define a stored proc taking in the begin and end dates as you like them to be, the general clause for that also is already mentioned:

WHERE (orderdate >= @begin1 and orderdate<@end1) or (orderdate >= @begin2 and orderdate<@end2)

Bye, Olaf.

 

[supportsvc]

Perhaps I explained it incorrect.

Fiscal year is April through March
Returning Jan - Mar is inaccurate for their Fiscal Year

Month and Date is set but NOT year, it's dynamic

Need to show
1) 4/1/#### of last year through 3/31/#### of this year = Last Fiscal year BUT on a rolling monthly basis, hence the LAST Month To Date (MTD) and LAST Year to Date (YTD) as we'll view the results on a weekly basis with a total of the month each month.
2) 4/1/#### of this year through 3/31/#### of next year = This Fiscal year BUT on a rolling monthly basis, hence the MTD and YTD
So LAST MTD for LAST YTD is 4/1/2017 - 4/16/2017 AND THIS MTD and THIS YTD 4/1/2018 - 4/16/2018

This is the 1st month of their fiscal year.
1) 4/1/2017 - 4/16/2017 (yesterday LAST YTD / LAST MTD)
2) 4/1/2018 - 4/16/2018 (yesterday YTD / MTD)

then 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 ...

 

[Olaf Doschke]

The dynamic in the year is there, because next year this will cover 2019/2018. So where is your problem?

This is not even bound to Jan-Mar, this is bound to Jan to full past month, of this year and previous year in relation to now (GETDATE()).

There is no binding to month or year numbers anywhere in the expressions.

Bye, Olaf.

 

[SkipVought]

@supportsvc

You started out asking for a YTD solution. In fact you explicitly stated, “2017 would include Jan - Mar...”

Four days later, you switched to Fiscal YTD.

Kind of unfair switching a horse in the middle of the stream, moving the goalposts, etc.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

SkipVought,
Yes, i know. I stated that the company just informed me when they say YTD, it's Fiscal Year to date and Month to date JUST yesterday.
URGH

And that their Fiscal year is Apr-Mar

Olaf, as posted yesterday

Olaf,
Thanks again on the original Year to date of last month!

Of course it changes ...
How can that be edited to show Year to date-1, then Month to date-1?

Tried this modification of your working code for last month

>= DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0) AND < 
DATEADD(month, DATEDIFF(month, - 1, DATEADD(year, - 1, GETDATE() - 1)), 
0)
>= DATEADD(year, DATEDIFF(year, 0, GETDATE()-1), 0) AND < 
DATEADD(month, DATEDIFF(month, - 1, GETDATE()-1), 0)

I get all of Jan - Apr for 2017 <--- this needs to be only to MTD of last year
And of all Jan - Apr to date in 2018

The code did not provide MTD of last year, it provided to the end of the month of the current month in last year.
It needs to be Month to Date as of yesterday.
So today is 4/17/2018,
Needs to return
through 4/16/2017 and through 4/16/2018

Hope this makes sense.

The DATEFROMPARTS does not work in 2008

 

[supportsvc]

(dbo.SO_SalesOrderHistoryHeader.OrderDate = (CASE WHEN MONTH(GETDATE())
                          <= 3 THEN CAST(CAST(YEAR(GETDATE()) - 1 AS 
VARCHAR(4)) + '0331' AS DATE) ELSE CAST(CAST(YEAR(GETDATE()) AS 
VARCHAR(4)) + '0331' AS DATE) END)) OR
                         (dbo.SO_SalesOrderHistoryDetail.CancelledLine 
= 'N') AND (NOT (dbo.SO_SalesOrderHistoryHeader.OrderStatus IN ('X', 
'Z'))) AND (dbo.SO_SalesOrderHistoryHeader.OrderDate = (CASE WHEN 
MONTH(GETDATE())
                          <= 3 THEN CAST(CAST(YEAR(GETDATE()) AS 
VARCHAR(4)) + '0331' AS DATE) ELSE CAST(CAST(YEAR(GETDATE()) + 1 AS 
VARCHAR(4)) + '0331' AS DATE) END))

This returns
Apr 2017 - Apr 2018

 

[Olaf Doschke]

When the fiscal years shifts 3 month, you can adjust ALL of the dates involved by DATEADD(month,3 olddate), that's the lazy solution for that.

On the other hand, you must still have copy&pasted my code wrong, because I don't see what you report. You say "The code did not provide MTD of last year, it provided to the end of the month of the current month in last year."

In regard to the end date of the past year that is:

SELECT DATEADD(month, DATEDIFF(month, 0, DATEADD(year,-1,GETDATE())), 0)

That is April 1st 2017, not as you claim "end of the month of the current month in last year" which would be April 30th 2017.

April 1st is totally correct in that context, as it is EXCLUDED, because we're using the comparison operator < (lower than) instead of <= (lower than or equal). If you still don't get it, the dates matching this condition need to be before midnight of April 1st, so they can be as close as you want to April, but must still be in March or earliler. It is much simpler to compute 1st days of months than end of month (even though newer SQL versions now have EOMONATH). Has that become clear now?

And this is not the only wrong understanding you had. Even tough I understand now that your problem has shifted by 3 months (see at the start of this post) I'm not eager to help someone, who doesn't recognize what I give him and doesn't even try to understand. Even at the time I actually had errors, I was just left with "Only getting current year Jan-Mar" and no details. You could be more communicative to get to your goals. Wehn you had written Apr2018-Mar108 or even just April to April or fiscal year starting at April that would have become much clearer, just spending 3 more seconds to make yourself clear is all it needs.

Bye, Olaf.