SQL Datetime Archives - SQL Training Online

August 29, 2017 by Joey Blue 1 Comment

Year-to-Date using Partition By with Windowing – SQL Training Online

You can use the Partition By Function in SQL Server to get a Year-to-Date and Month-to_Date calculation.

In this video I show you how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

This video builds on the prior day’s video, Prior Day Profit using the Lag Function with Windowing .

Video: Month-to_Date using the Partition By Function in SQL Server

how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

I use the Wide World Importers and the following scripts:

--SQL Year-to-Date using Partition By
select 
year([Invoice Date Key]) as Year,
Month([Invoice Date Key]) as Month,
[Invoice Date Key],
Profit,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]), Month([Invoice Date Key]) ORDER BY [Invoice Date Key]) as MonthToDate,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]) ORDER BY [Invoice Date Key]) as YearToDate
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

See the original Year-to-Date using the Partition Windowing Function video.

August 27, 2017 by Joey Blue 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.

August 26, 2017 by Joey Blue 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.

February 7, 2015 by Joey Blue Leave a Comment

How to Filter using SQL IsDate() Function and Cast

Video: SQL IsDate() Function as a Filter

Using the SQL IsDate() Function in your filter needs a little trick to make it work.

In your career you may run into an issue where you have a date stored as a varchar.

When this happens, you may need to use the SQL IsDate in the Where Clause.

You need to first use the SQL IsDate Function to check if the value is a valid date.

Then you can convert the varchar to a date using Cast or Convert.

Even though you have filtered on the SQL Isdate, you might still have a problem with the conversion.

This video shows you how to deal with it.

I don’t mention it in the video, but when dealing with dates, you always have to keep in mind the default date settings of the database.

Cast and Isdate use these defaults and could cause you some trouble if you aren’t aware of them.

Here are the scripts to run the queries that are in the video.

create table emp
(
id int primary key identity(1,1)
,emp_name varchar(50)
,emp_birthday varchar(20)
)
GO
insert into emp (emp_name,emp_birthday) values ('Bob','12/12/2001');
insert into emp (emp_name,emp_birthday) values ('Mary','123');
insert into emp (emp_name,emp_birthday) values ('Jill','12/1/2005');
insert into emp (emp_name,emp_birthday) values ('Jim','1/12/1999');
insert into emp (emp_name,emp_birthday) values ('Sue','7/15/2011');
insert into emp (emp_name,emp_birthday) values ('Sally','20120315');
GO

select id,emp_name,emp_birthday
,isdate(emp_birthday)
from emp
where isdate(emp_birthday)=1
and 
case when isdate(emp_birthday)=0 then cast(null as date)
    else cast(emp_birthday as date)
end < '1/1/2006'

Here is the YouTube Video SQL Isdate Function as a Filter with Cast .

January 31, 2013 by Joey Blue Leave a Comment

How to Calculate a SQL Moving Average without a Cursor

Update: If you are working with the newest versions of SQL Server, you can use the windowing functions to accomplish the same thing. I posted the updated code at the end of the post. For this video, I still like the thought process of anchoring to a date.

Video: 3 Day Moving Average in SQL

An efficient way to calculate a moving average in SQL using a few tricks to set date anchors.

There are debates on the best way to do a SQL Moving Average in SQL Server.

Some people think there are times when a cursor is most efficient. Other’s think that you can do it all in a set-based way without the cursor.

The other day I was going to calculate a moving average and my first thought was to use a cursor.

I did some quick research and found this forum question: Moving Average in TSQL

There is a post that shows a subquery with an anchor date to help find the 1 and 2 day offset.

Here is the script you can use to test the 3 day SQL Moving Average final result.

CREATE TABLE [dbo].[daily_sales](
    [id] [int] NULL,
    [dt] [datetime] NULL,
    [revenue] [decimal](12, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (1, CAST(0x0000A15700000000 AS DateTime), CAST(125.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (2, CAST(0x0000A15800000000 AS DateTime), CAST(114.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (3, CAST(0x0000A15900000000 AS DateTime), CAST(92.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (4, CAST(0x0000A15A00000000 AS DateTime), CAST(152.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (5, CAST(0x0000A15B00000000 AS DateTime), CAST(48.00 AS Decimal(12, 2)))
GO

Here is the final query.

SELECT        
            DATEADD(DAY, days_since_1900, '19000101') AS dt,
            AVG(revenue) AS Revenue_3_day_moving_average
FROM (
            select 
            DATEDIFF(DAY, '18991230', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL

            select 
            DATEDIFF(DAY, '18991231', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL
            select 
            DATEDIFF(DAY, '19000101', dt) AS days_since_1900
            ,revenue
            ,1 as Actualdate
            from daily_sales

) AS daily_sales_normalized_to_1900
GROUP BY    days_since_1900
HAVING        MAX(Actualdate) = 1
ORDER BY    dt

Here is the query you would use with SQL Server 2012.

select dt, 
    avg(revenue)
    OVER (
        ORDER BY dt
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )
FROM daily_sales

The original Youtube video: SQL Moving Average