‘Just copy and paste this query, go to the WHERE clause, change it to last week, and run it. It’s simple!’ — Some people you might know
Manually changing values in a query on a daily/weekly/monthly cadence can be a huge pain. It also opens the door for human error. We’ll review the power of SQL’s GETDATE( ) function, how to include it in queries (maybe you have a report that is always last week’s data, for example), and, very importantly, how to avoid a few issues with date/week ranges.
As with all tools, understanding what a function does is very helpful. Today’s date is 2021–08–27. Let’s take a look at what running a GETDATE will give us (formatted as ‘yyyy-MM-dd’):
It GETs the DATE on which it’s executed. Not quite the ground-shaking shock you may have felt when you found out Bruce Willis was really dead the whole time during ‘The Sixth Sense’…but really close.
So, let’s say you have data that needs to be pulled each Monday. The report is to include data from only the previous week. Do not include weeks prior to last week or the partial current week.
For this, you will need some sort of Calendar table. In it, you will ideally have a column that is a year-week combo. Please include some sort of delimiter between the year and week. If your company doesn’t already have this, I am assuming you are reading this on your own computer/cell phone because your company might not even have computers yet.
Now, obviously looking at today’s date will not give us information on what we call last week. Let’s take a look at a table with ‘calendar_date’ and ‘year_week’ columns for this week and last.
You might be accustomed to using something like a DATEADD function where one of the arguments is a number and another argument is the interval type (year, day, etc.). When using GETDATE, you can just simple say ‘-7’ to get the date a week ago. Let’s now look at what we’ll get when we select GETDATE( ) and GETDATE( ) — 7 from our ‘calendar_table’.
Let’s say we are running this on a Monday. In this example, we could select 2021–08–16 as last week’s Monday and pull all data WHERE ‘year_week’ is equal to the value in our ‘calendar_table’, again ‘2021–33’ for this example. If we run this on a Sunday, last week’s Sunday’s ‘year_week’ will be our variable to use, which is still…
Continue reading: https://towardsdatascience.com/how-to-use-getdate-for-reports-d8cdc504a261?source=rss—-7f60cf5620c9—4