TSQL Difference between 2 rows

use AdventureWorks2012;

select

CustomerID,
OrderDate,
TotalDue,
sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as LastOrderAmount,
TotalDue - sum(Totaldue) over(partition by CustomerId order by OrderDate Desc ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) as ChangeFromLastOrder from sales.SalesOrderHeader

order by CustomerID, OrderDate Desc

Till Next Time

TSQL Playing with aggregates part 2

In part 1 we went back to the basics in this part I want to show more advanced possibility's.

Consider the following question: How much do my customers contribute to my sales on a yearly basis?

This means we have to have at least two numbers in the same query:

• Total Sales per year.
• Total Sales per year per customer.

To achieve this we can use the “PARTITION BY” clause in the “OVER ()” statement:

 

Don’t panic if you see the same number reappearing in SalesPerYearPerCustomer column. This is caused by the fact that the people who made the AdventureWorks base set weren’t very creative with the orders…….

To work around this I added an extra column named SalesAmountReal and filled this with a randomized amount:

This would give us:

Even in my randomized set there isn’t a huge spread in sales amount.

Till Next Time

TSQL Playing with aggregates part 1

One of the least understood functionalities in TSQL is the usage of aggregates. In this article I want to back to the basics. For my examples I’m using the AdventureWorks 2012 DW:

The most basic aggregates are count and sum. How many or how much do I have:

For an information point of view these number don’t tell much. Most of the time one is interested in amount per category. A category can be something like period, customer, product, salesrep etc. Most querys have a period based axis.

This introduces the “GROUP BY” part. As you can see the results aren’t ordered. For that we have to a an “ORDER BY”.

Both the “GROUP BY” and the “ORDER BY” work for the whole set. In part 2 of this series I will show you how to make them work for subsets.

Till Next Time