Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Well done!

You have completed Reporting with SQL!

Sign up for Treehouse Back to Library
Preview
Video Player
00:00
00:00
00:00
  1. 2x 2x
  2. 1.75x 1.75x
  3. 1.5x 1.5x
  4. 1.25x 1.25x
  5. 1.1x 1.1x
  6. 1x 1x
  7. 0.75x 0.75x
  8. 0.5x 0.5x
Use Up/Down Arrow keys to increase or decrease volume.

Creating Up-to-the-Minute Reports

2:17 with Andrew Chalkley

One of the powerful features in SQL is writing queries based on today's date and time. We'll use a function to get today's date.

One of the most powerful things you can do with SQL 0:00
is run queries based on today's date. 0:03
For example, sales totals from the beginning of the current month to today or 0:05
all users who've signed up on your site today. 0:10
But, you don't have to manually type in the current date 0:14
each time you create a query. 0:17
You can get today's date using a SQL function. 0:19
If you're running a query to get all of today's comments on a blog, or 0:22
retrieving the cells in the last week, you'll need to use the date function. 0:26
To get today's date from the date function, 0:32
you can pass in the string of now. 0:35
If today was the first of April 2020, we'd expect to see this. 0:37
If today was December 20th, 2019, we'd expect to see this. 0:43
Let's see this function in action. 0:49
This shows today's date. 0:52
We'll get different values because obviously, I'm recording this in the past. 0:54
Hello, you futuristic person, you. 0:58
Let's find all the orders that were placed today, but haven't been shipped yet. 1:00
When we review the orders table, we see that is a status. 1:06
Let's run a SQL query that will get all distinct statuses from the orders table. 1:13
We have placed and shipped, so we can search for all orders that are placed. 1:20
And then we want to search for ordered_on to equal the date now. 1:33
We can get this list of orders to the fulfillment staff, 1:50
so they can send out today's orders. 1:53
Whether you're a developer or analyst creating dashboard for 1:56
others to use, using the date function is incredibly useful. 1:59
In the next video, we'll see how we can run queries that will be able to generate 2:03
more interesting results. 2:08
And be sure to check out the teacher's notes to see the syntax 2:10
on how to get today's date in other versions of SQL. 2:13