News

Here at iDashboards, we love making the design of interactive charts and dashboards easy with our drag and drop interface. But we also have a soft spot for those who like to get their hands dirty slinging some SQL code as well. That is why we make it simple for people who are just getting started with SQL – as well as seasoned veterans – to create beautiful and impactful charts using their existing skill set, in the vendor-specific dialect they know and love.

This is accomplished using the Custom SQL module in the Chart Designer. This module can be enabled by your iDashboards Administrator. Once enabled, rather than spending valuable time learning a new system or syntax, SQL code can be written on the fly against your database in its native dialect. More importantly, previously written queries can be pasted in and used to create charts directly.

This also allows a SQL analyst to use powerful, vendor specific functions that can make complex queries or analysis much easier.

Let’s take a look at a few examples using Transact-SQL (or T-SQL) the language of Microsoft SQL Server.

For this blog, I have created a very simple database with three tables. Let’s assume this is for a fictional company called AwesomeCo. The three tables are below:

The sales table contains daily sales information and looks like this:

If we want to make a chart that displays a trend of all the sales by day, we can use a simple generic query like this:

SELECT
[saleDate]
, [salesAmount]
FROM   sales

If placed into a line chart, it would look something like this:

sql1

Although this is chart is interesting, it may be too granular to get true insight. There are additional problems as well – it appears that AwesomeCo doesn’t make a sale every day. This can make the above chart a little misleading. Perhaps if we could view the sales grouped by month, the picture would become clearer.

But how can we do that?

DateTime Functions

While there are some standard functions dealing with the DateTime datatype, the way many RDMS vendors deal with this datatype is quite varied. If we want to group a sum of all our sales by month, we could use something as simple as:

SELECT
MONTH([saleDate]) AS [MonthOfSale]
, SUM([salesAmount]) AS [MonthlySales]
FROM    sales
GROUP BY
MONTH([saleDate])

This query will give us our sales grouped by month number (ex. Any date in January would be the number 1). As you can see below, however, this approach has given us some misleading results. It has added up all sales to occur in a particular month regardless of the year the sale took place!

sql2

 

 

 

 

 

 

 

There are solutions to this problem, such as also selecting out the YEAR as well as the month and grouping on both.

But what if we want to keep the datatype of the month value as a DateTime? We can write a query in T-SQL like the one below that will keep allowing us to group on a single month value:

SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0) AS [MonthOfSale]
, SUM([salesAmount]) AS [MonthlySales]
FROM   sales
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0)

This will allow us to group on the first date of the month a sale occurred in, and add all those sales up by month, as you can see in the results below:

sql3

 

 

 

 

 

How did this work? It was achieved by using two different functions. First, we used the DATEDIFF function to tell us how many months it has been since the year 1900 (this is considered 0 in SQL Server) and the sale date. Let’s take a look at how this works:

DATEDIFF(MONTH, 0, ’12/18/2015′)

The result of the statement below would be 1391, as in “it has been 1391 Months since 1/1/1900.”

Now let’s nest the DATEDIFF function inside a DATEADD function.

DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0 )

The DATEADD function above will add the number of months it is given to a date it is provided. In this case 1/1/1900 (remember, 0 is 1/1/1900). Another way to look at this is:

DATEADD(MONTH, 1391, 0)

So if we add 1391 months to 1/1/1900, we will get 12/1/2015, or the first day of the month that the sale occurred in. Pretty neat!

Now let’s paste that query into the Custom Query module and make a chart.

sql4

 

This sales trend chart is a lot easier to understand than looking at our sales in an uneven daily trend.

Examining this chart, it seems that sales are generally trending up in 2015 versus the previous year. To confirm this, it might be a good idea to compare the sales by month against the previous year. Let’s do this with SQL code.

Window Functions

T-SQL offers a powerful window function called LAG (SQL Server 2012+) which should allow us to pull the value of MonthlySales from exactly 12 months ago. You may notice the query below is using a type of subquery called a derived table. While there are other approaches to write this query, there can be advantages to authoring SQL queries this way.

SELECT
[MonthOfSale]
, [MonthlySales]
, [PrevYearMonth]
FROM
(
SELECT
[MonthOfSale]
, [MonthlySales]
, LAG([MonthlySales], 12) OVER(ORDER BY [MonthOfSale]) AS [PrevYearMonth]
FROM
(
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0) AS [MonthOfSale]
, SUM([salesAmount]) AS [MonthlySales]
FROM   sales
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0)
) t1
) t2
WHERE YEAR([MonthOfSale]) = 2015

Let’s take a look at the result of this query:

sql5

 

 

 

 

 

 

The results show us what the monthly sales were in a particular month in 2015, as well as a column that shows us what the monthly sales were for that same month last year (or 12 months ago) for any given month. We have also limited the results to only display 2015 because there were no sales in 2013. As a result, the PrevYearMonth column would display NULL for those months in 2014, and that is not useful for us.

Now let’s see what this query looks like in a chart:

sql6

Interesting. We can now see that sales were indeed better in 2015 in every month except January and July. This seems to be great news for our fictional business!

Now seems like a great time to use SQL to see how the business is growing.

Math Expressions in SQL

Let’s use the LAG function again, but this time to pull in the value of the previous month’s sales.

Once we have done that, we can use simple expression to calculate the month-over-month growth.

([PeriodSales] – [PreviousPeriodSales]) / [PreviousPeriodSales] * 100

This will tell us what percentage our sales have grown (or shrunk) from the previous month.

SELECT
[MonthOfSale]
, [MonthlySales]
, [PrevMonth]
, ([MonthlySales]  [PrevMonth]) / [PrevMonth] * 100 AS [GrowthRate]
FROM
(
SELECT
[MonthOfSale]
, [MonthlySales]
, LAG([MonthlySales], 1) OVER(ORDER BY [MonthOfSale]) AS [PrevMonth]
FROM
(
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0) AS [MonthOfSale]
SUM([salesAmount]) AS [MonthlySales]
FROM   sales
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0)
) t1
) t2

Let’s take a look at the result of the query:

sql7

 

 

 

 

 

…and now in in a Scatter Plot chart with a linear regression:

sql8

It seems that even though our fictional business is making more money, growth is actually gradually slowing over the course of the two year period. While this isn’t great news, now our stakeholder knows action can be taken to turn things around.

Table Joins and More…

You may remember from the start of this blog that three tables were created. So far we have only used the sales table in examples.

Upon closer inspection of the other tables, AwesomeCo has two sales divisions: Red and Blue. What if we want to compare the monthly sales performance of these two divisions for every month over the past two years?

Taking a peek at the sales table it becomes apparent that there is no hint of which division made the sale. There are some other clues we can follow though. It seems that there is a column titled RegionID. We also have a table called “regions”, so let’s take a look at that one.

In the regions table, we have three columns: ID, Region, and DivisionID. It seems that every Region in AwesomeCo belongs to one distinct Division. This, of course, leads us to the Divisions table.

Now that we know how these three tables can be related, we can do so with a JOIN. We will join the tables like the screenshot below:

sql9

SELECT
[saleDate]
, [Division]
, [salesAmount]
FROM   sales AS s
INNER JOIN regions AS r ON
s.regionID = r.ID
INNER JOIN divisions AS d ON
d.ID = r.DivisionID

As we see in the result below. Using JOINs, we were able to bring in which division each sale is accociated with.

sql10

 

 

 

 

 

Now that we have our tables joined together. We can figure out the total sales for each division by month. Let’s use the PIVOT relational operator in SQL Server to place each Division in its own column. See the query below.

SELECT
[MonthOfSale]
, [Blue]
, [Red]
FROM
(
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, [saleDate]), 0) AS [MonthOfSale]
, [Division]
, [salesAmount]
FROM   sales AS s
INNER JOIN regions AS r ON s.regionID = r.ID
INNER JOIN divisions AS d ON d.ID = r.DivisionID
t1 PIVOT(SUM(t1.salesAmount) FOR t1.Division IN(
[Blue]
, [Red])) pvt

As you can see from the result below, PIVOT worked like a charm. We can now easily see the total sales from each division by month.

sql11

 

 

 

 

 

 

Finally, as always, let’s see what this would look like in a chart:

sql12

Once we visualized the data, seeing which division performed better for AwesomeCo in any given month becomes almost effortless.

Conclusion

SQL allows for powerful analysis, but when combined with impactful visuals and on-demand access, stakeholders in your organization can more quickly reach data-driven conclusions and take action on business goals.

We are just scratching the surface of what is possible with SQL + iDashboards. Did you know you can parameterize your SQL code within iDashboards with dropdown lists or calendar pickers or even use other database objects like Views or Stored Procedures? The only limit is your imagination.

[/fusion_text]

Comments

Your email address will not be published. Required fields are marked *