How to utilize DPS's "SQL Server Connection" dialog box to filter by a dynamic date, such as "yesterday" or "last weekday"
Altair EmployeePlease be sure to read the previous article, found here, in order to have context for this one.
In that article, we learned that by manually editing (or entering from scratch) a query, you can cut down the amount of data that need to be transmitted from the server to your machine. We also learned how to access that part of the dialog box.
Finally, we learned how to send the GETDATE() function in such a way that the server provides all records of the day instead of only ones that match the exact time. This was accomplished with CONVERT(date, GETDATE()).
If you would need only data from yesterday, you may simply write CONVERT(date, GETDATE() - 1) where -1 can be -2 or -3 or any number of days you need to see. There's one final challenge to make this truly dynamic and able to be fully automated, and that is to use a variable within the number subtracted from GETDATE(). For this example, you are working at a bank and need yesterday's data except on Monday when you need the data from Friday, because the guys refilling the ATMs don't work on the weekends.
In this instance, you will still use the SELECT statement and you will use UNION operator and then SELECT again. The two tables are each selected by a filter using the WHERE clause.
Before we go making things more complicated, we're going to simplify them. From the final screenshot in the previous article, you will note that the query names every column in the table, here underlined in red.
The first thing you want to do is replace all of those column names with a single asterisk. That will tell the SQL server that you want every column, and it keeps the expression easier to read for yourself or anyone who might use it in the future.
This statement will be processed the same way on the server, but is a lot cleaner for a human to read.
Your next step is going to be to subtract a day from the GETDATE(). Then you'll need to add an AND operator and an expression that stipulates any day except Monday. That expression is DATEPART(dw, CONVERT(date, GETDATE())) <> 2. This uses DATEPART with dw in the first argument. DATEPART and dw work to give a numerical day of the week where Sunday is 1, Monday 2, and so on.
By marrying these two expressions with AND, we are telling the server that the Start Date must be yesterday and today must not be Monday. If you run this on Tuesday or Friday or Saturday, it will give you the data from yesterday. On Monday, no data and no table will be returned because the DATEPART is equal to 2.
If you leave it at that, you will have a query that gives yesterday's data and it will never give anything on Monday. This is where the UNION comes in. The table that's the other component of the Union must only produce data on Monday and it gives the data from Friday (three days ago.) The expression for that table is very close to the one from the non-Monday table. You merely change the -1 after GETDATE() to -3 and change the "not equal sign" <> to a regular equal sign. In total, the entire query looks like this:
The clever thing about this solution (provided to me by Jack Lynch) is the ability to make the UNION work similar to an IF statement where only one of these things is going to be true at any time, resulting in minimized network traffic before the data even hit Monarch Data Prep Studio. This technique now is expanding your capabilities more and more and we hope you will find more uses for Monarch to automate repetitive data tasks.