SQL Training Online

by Leave a Comment

Clickbank Product Analysis from the XML Feed – #PowerBI 001

How to take the Clickbank XML feed and turn it into a powerful report using Power BI.

Here is the finished product so you can interact with it.

**Don’t forget to click the full screen button to really see the report!

In this video I show you how to go from XML to Microsoft Power BI report in under 15 minutes from start to finish.

Video: Clickbank Product Analysis


How to take the Clickbank XML feed and turn it into a powerful report using Power BI.

 

See the original Power BI from XML to Report video.

by 1 Comment

Latitude and Longitude with the Geography Data Type – SQL Training Online

The Geography data type is used in many different SQL Server databases. It contains Latitude, Longitude, and many other properties and functions.

In this video I show you how to extract the latitude and longitude from the Geography data type.

Video: Latitude and Longitude with the Geography Data Type


How to extract the latitude and longitude from the Geography data type..

I use the Wide World Importers and the following scripts:

--SQL Geography Data Type
select CityName
,Location.Lat Latitude
,Location.Long Longitude
,cast(location as varchar(max))
from Application.Cities

Link to the a good tutorial on MSSQLTips.com.

 

See the original Latitude and Longitude in SQL Server video.

by 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.

by 1 Comment

Prior Day Profit using the Lag Function with Windowing in SQL Server

You can get a prior day value using the Lag Function in SQL Server.

In this video I show you how to create a prior day squeeze of profit using the Windowing Lag function in TSQL.

This video builds on the prior day’s video, Moving Average with Windowing.

Video: Prior Day Profit using the Lag Function in SQL Server


How to use the SQL Lag Function for Prior Day Squeeze.

I use the Wide World Importers and the following scripts:

select 
[Invoice Date Key],
Profit,
Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]) as PriorDayProfit,
Profit - isnull(Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]),0) as DailySqueeze
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Prior Day Profit – Lag Windowing Function video.

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.