In this tip, we will see how to build a report which shows the cumulative sales amount for the current month, current quarter (QTD) and current year (YTD) in a single crosstab (tablix) report, which makes it easy to compare the data.
The standard SQL language has a number of aggregate functions like: SUM, MIN, MAX, AVG, but a common statistics function that SQL Server does not have is a built-in aggregate function for median. The median is the value that falls in the middle of a sorted resultset with equal parts that are smaller and equal parts that are greater. Since there is no built-in implementation for the median, the following is a simple solution I put together to find the median.
This func returns the max value,min value and count of values from collection of values
We asked Phil Factor to write a nice simple quick-start guide about aggregation, pivoting and un-pivoting techniques. To do so, he takes us all the way from getting the data from a published source, transferring it to SQL Server, un-pivoting it, storing it in a relational table, aggregating it and finally pivoting the data in a variety of ways.