SQL Training Online

by 1 Comment

Moving Average with Windowing in SQL Server

The Moving Average used to be complex, but with the Windowing Functions, the Moving Average is easier and performs much better.

In this video I show you how to create a moving average in SQL using the OVER Clause and the ROWS Clause.

Video: SQL Moving Average with Windowing


How to use the SQL Over Clause for a Moving Average.

I use the Wide World Importers and the following scripts:

select [Invoice Date Key], sum(Profit) Profit
into dbo.ProfitByDate
from [Fact].[Sale]
group by [Invoice Date Key];

select 
[Invoice Date Key],
avg(Profit) OVER (ORDER BY [Invoice Date Key] ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as AverageProfit
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Rolling Average with Windowing video.

by Leave a Comment

Format Function in SQL Server

The Format Function can help you change how a date or decimal is displayed.

In this video I do a quick demonstration of the Format Function.

Video: SQL Server Format Function


How to use the SQL Format Function on a Date.

I use the Wide World Importers and the following scripts:

select OrderDate,Format(OrderDate,'MMM dd, yyyy') 
from [Sales].[Orders]
where year(OrderDate) = 2016

Link to the Microsoft SQL Server Custom Date Formatters.

 

See the original  SQL Format for Date video.

by Leave a Comment

SQL Date Comparison – How to Filter Datetime in SQL Server

Video: SQL Date Comparison – How to filter Datetime in SQL Server – SQL Training Online
In this video, I show you how to do a SQL Date Comparison in SQL Server when your Datetime has an actual time in it. I also include the latest trick that will make this easier if you are using SQL Server 2008 or SQL Server 2012.

[sharebox5_no_text] [/sharebox5_no_text]

How do you compare a Datetime in SQL Server?

A SQL Datetime stores both Date and Time. So when you are filtering using the SQL Where clause, you have to know if you Datetime is storing the time also.

Let’s take a look at the employee table in SQL Training Online Simple DB

select *
from employee

The column we are looking at is the hire date. I want to go after the person who was hired on February, 22 2005.

So, I use the following SQL.

select *
from employee
where hire_date='02/22/2005'

Now, this works because we don’t have any times stored in this column. But, if I change the record from 2005-02-22 00:00:00.000 to 2005-02-22 08:22:25.000 and rerun the query, you see that I don’t get any results.

SQL Date Comparison

That is because the value of 02/22/2005 is really 2005-02-22 00:00:00.000 and I just changed the hire_date to include a time other than 00:00:00.000.

So, I have to tweak my query to use a greater than and less than sign.

select *
from employee
where hire_date >= '02/22/2005'
and hire_date < '02/23/2005'

This works. But, to really understand what is going on, let me show you what SQL Server really sees.

select *
from employee
where hire_date >= '2005-02-22 00:00:00.000'
and hire_date < '2005-02-23 00:00:00.000'

There are other ways, and in this StackOverflow article you can read more about these other ways. But, I want to point out a few.

If you are working in SQL Server 2005 or earlier, the following is supposed to be the fastest performing way to do this.

select datediff(dd,0, hire_date),*
from employee
where dateadd(dd,0, datediff(dd,0, hire_date)) = '2005-02-22'

But, if you are working in SQL Server 2008 or SQL Server 2012, they have introduced an actual Date datatype that doesn’t include the Time portion. Since this is the case, you can use the CAST function to remove the time from the Datetime.

Here is the SQL to do that.

select *
from employee
where CAST(hire_date AS Date) = '2005-02-22'

And that’s it.  That is the SQL Date Comparison in Microsoft SQL Server.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

by Leave a Comment

How to use Datetime in SQL Server – SQL Training Online

This video is a quick overview of using the SQL Server Datetime function.

[sharebox5_no_text] [/sharebox5_no_text]

The datetime is actually made up of two different pieces:

1) Date

2) Time

This can be demonstrated using the SQL Cast function on a string to convert it to a datetime.

Here is some SQL for the Date portion:

select cast('20121019' as datetime)

Here is some SQL for the Time portion:

select cast('10:05:00' as datetime)

Now, you can also simply use the built-in getdate() function to bring back the current system time.

select getdate()

And finally, I want to bring back a datetime from an actual table. time

In this case, we will use the employee table from the SQL Training Online Simple DB:

select hire_date from employee

If we want to convert the result to a more formatted string, we will use the TSQL CONVERT function:

select convert(varchar,hire_date,104)
from employee

In this example, we are converting from a datetime to a varchar (string). The result will be in the format of 104.

To translate 104 to a datetime format, you will want to look at the MSDN Documentation.

And, that is how you use the Datetime SQL.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.