February 27, 2020 at 12:00 am
Comments posted to this topic are about the item Implementing Date Calculations in SQL
Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
https://twitter.com/avikoleum
https://www.linkedin.com/in/aveekd22/
March 5, 2020 at 11:25 am
Wondering why you are not using the EOMONTH function for the last day of the relevant months?
March 5, 2020 at 1:20 pm
Rather than having to refresh your dates every day, wouldn't it have been simpler to implement a view with dynamic dates?
Each refresh of the report is only ever pulling 2 dates from the view so it's not like its a performance bottleneck.
March 5, 2020 at 2:17 pm
Wondering why you are not using the EOMONTH function for the last day of the relevant months?
For what was written as code in the article, I'm wondering the same thing. Unfortunately, the code in the article expresses the wrong method to do such date calculations because the WHERE clause in the reporting code uses BETWEEN. It means that, if someone writes similar code against a DATETIME2(7) column, the last 3 milliseconds of the day will be missed and a whole lot can happen in those 3 milliseconds that can cause substantial transactions to be missed throwing totals off by quite a bit. Both EOMONTH and the use of BETWEEN, IMHO, should be outlawed as a "Worst Practice".
The "Best Practice" would be to have used the standard "closed/open" temporal boundaries in the following form...
WHERE SomeDateTimeColumn >= CURRENTPeriodStart AND SomeDateColumn < NEXTPeriodEnd
... where the period start and ends are "whole" dates with a "midnight time".
The code that wasn't changed in the example was the code that most desperately needed to change.
I'm also dead set against having to maintain such a lookup table because you can't control when someone or something is going to run a report and the table may have been updated too early or too late and, again, milliseconds DO matter in this area. The view (actually, I'd make it an iTVF so you could vary the "today" date if needed... that won't work in a view) that Norty303 suggested would be more timely and require no maintenance.aya
And, no... the common justification for using BETWEEN because, supposedly, only whole dates will be used in the tables doesn't hold water because you simply cannot control what may happen to that table in the future. Someone may suddenly decide that the datatype and the data needs to change to something more finite. You also can't control what some desperate programmer may copy your code for. Use the standard that I cited above ALL the time and your code will be bullet-proof in those areas.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2020 at 2:26 pm
@aveek22 ,
Man, once again, very well written article. I just have to seriously disagree with the methods you used. Please see my previous post above for why.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2020 at 4:06 pm
Hello,
Going forward this post, I'd like to ask about my issue, that touching date and time subject.
We use a Retail Calendar (445) which is different from the regular one. The year begins in October and the first day of every month is not the real first day of this month. For example for this fiscal year (2020), November begins on Oct. 6th and ends on Nov. 2d, next year there will be a different dates.
I need to create an offset field/s calculating the differences between two points of times based on that calendar.
The calculation should be dynamic and comparing every time between the date of today and the second (past/ future/ present date).
I used many kinds of script, but no one of them works with that kind of calendar.
I have my "445 calendar" and know when every month begins and ending as well as the year, so I don't need to calculate the calendar itself.. 445 means the months will have 4,4 and 5 weeks in them, for example, October will contain 4 weeks, November - 4 weeks, December 5 weeks, Jan.- 4, Feb -4, March -5, etc. My problem is to calculate this Offset. I did it successful for the regular calendar.
Thank you!
March 5, 2020 at 6:50 pm
I posted a function that calculates the 4-5-4, 5-4-4 and 4-4-5 calendars for a 3 year period here: https://www.sqlservercentral.com/scripts/fiscal-retail-4-5-4-calendar-function
If I am understanding your request - you need to figure out the 4-4-5 portion of the calendar. If that is the case - the function does that using the formula: ((WeekInQuarter - 1) / 4) - (WeekInQuarter / 13) + 1
The caveat is what to do with the 53rd week of a year. In the 4-4-5 calendar the last quarter would end up as 4-4-6 - or you restate the calendar where you shift the weeks in the year back one so that year starts one week later and there are only 52 weeks. In most cases, the 4-4-5 calendar would be reset to a 4-5-5 for comparison in the non-restated version.
If that isn't the issue you are having - can you explain it further?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 9, 2020 at 12:33 pm
Another nice aspect of this approach would be if you made the stored procedure use a Temp Table and a Merge, while making the TimePeriods table a temporal table. This would also allow you to pull older versions of the query more easily using the temporal syntax to gather your dates.
Also, I would agree that using the following would be a more accurate approach.
WHERE [SalesDate] >= @PeriodStartDateTime
AND [SalesDate] < @PeriodEndDateTime
December 14, 2021 at 4:56 am
Hi,
I have been looking for the most correct way to handle this kind of analytics (periods) with sql server, and this article has been the closest to get a solution, but I have a problem (doubt), suppose today is 15-dec-2021 and I want to get the YTD sales or the current month versus the same time period of 2020, in that case I don't want to include all the days of the month dec-2020 in the comparison because it has less days in dec-2021 than the comparison period in 2020.
Could you please give some ideas how I could implement a solution to avoid that problem and have a "fair" comparison in the time periods.
I appreciate your time and help.
Regards,
Javier
December 15, 2021 at 2:01 pm
Hi, I have been looking for the most correct way to handle this kind of analytics (periods) with sql server, and this article has been the closest to get a solution, but I have a problem (doubt), suppose today is 15-dec-2021 and I want to get the YTD sales or the current month versus the same time period of 2020, in that case I don't want to include all the days of the month dec-2020 in the comparison because it has less days in dec-2021 than the comparison period in 2020.
Could you please give some ideas how I could implement a solution to avoid that problem and have a "fair" comparison in the time periods.
I appreciate your time and help.
Regards, Javier
It's always been interesting to me how much time and effort is spent on how to identify the "same time period" for the previous year as the current year. It's not actually possible for adjacent years.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2021 at 9:49 pm
It's always been interesting to me how much time and effort is spent on how to identify the "same time period" for the previous year as the current year. It's not actually possible for adjacent years.
Which is why the 52/53 week calendars were created - to at least be able to compare equivalent time periods in adjacent years. But then you also have to know when to restate a particular year for comparison, which is dependent on when the fiscal year starts and which years contain 53 weeks.
If all you want is a YTD comparison - that is easy. Get the first of each year, the current date and same date prior year (use dateadd and subtract 1 year). Then just create 2 tables (derived table or CTE or view) and compare...but that is where it falls apart. What are you comparing?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2021 at 11:24 pm
Yep. Totally agreed that the 52/53 week things get folks a whole lot closer than most but, as you say, what do people thing they're actually comparing? Even the almighty ISO Week thing doesn't help there.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply