Often when writing a query you need to get data from a range of dates; when you start thinking about automating the SQL query it’s even more important. In this post I’ll show you a quick trick to let Toad for Data Analysis figure it out for you!
For this example I’ll be working with Oracle though it’s no different for other platforms. In my Oracle database, if I wanted to get a list of the orders that were placed last year I’d have to write something like the following:
SELECT ORDER_NUM, BOOKINGS_DATE
FROM LICENSE_BOOKINGS
WHERE (BOOKINGS_DATE BETWEEN to_date
('2007-01-01 00:00:00',
'yyyy/mm/dd hh24:mi:ss')
AND to_date
('2007-12-31 23:59:59',
'yyyy/mm/dd hh24:mi:ss'));
Sure, some people are familiar with the syntax. But what about the syntax for the last quarter, the current month or just last week? It’s not rocket science, but it can get confusing and is quite error prone.
Now, in the SQL above, I just hard coded the dates – that’s a dangerous practice in most cases. So what do I do? I start thinking about maybe writing it a little bit differently… maybe you use a bind variable or I start working with SYSDATE to get the system’s date and first and work backwards from there. If I want to automate the query, this is the path that I have to go down.
In Toad for Data Analysis we make life a little bit easier – we write the date ranges for you. So now instead of using complicated SQL to get the job done, you can drop in something like /*Last week*/ or /*Last 30 days*/ and Toad for Data Analysis will dynamically insert the correct SQL. Keep in mind that this is a Toad for Data Analysis feature – if you execute the SQL statement in something like SQL*Plus the Oracle compiler will see /*Last week*/ as a comment and your statement will not run.
Here’s how the revised query will look:
SELECT ORDER_NUM, BOOKINGS_DATE
FROM LICENSE_BOOKINGS
WHERE (BOOKINGS_DATE = '' /*Last month*/ );
(Note that the criteria is empty – I use two single quotes with no space. This is important. Toad for Data Analysis will insert the correct SQL between these two single quotes when I execute the query.)
If you use the Query Builder in Toad for Data Analysis it’s super-simple. Once you select a column with a date datatype, click on the “Where Clause” field to open a new window where you pick from a list of available ranges. Viola! You’re done.
For those of you writing SQL scripts by hand, here’s a list of the available commands. Keep in mind that they’re CaSe SeNsItIvE… if you don’t write it exactly as you see here then it won’t work.
|
/*Today*/
|
/*Current month*/
|
|
/*Yesterday*/
|
/*Last month*/
|
|
/*Current week*/
|
/*Current quarter*/
|
|
/*Last week*/
|
/*Last quarter*/
|
|
/*Last 7 days*/
|
/*Current year*/
|
|
/*Last 15 days*/
|
/*Last year*/
|
|
/*Last 30 days*/
|
|
If you want to automate the script you’ve created, save it to disk and then click “Automate” on the Toad for Data Analysis toolbar. After you build the automation routine you can click “Schedule” to add it to the Windows Scheduler and set it to run anytime.