SQL Server Window Functions (WTD, MTD, YTD, FY) – OVER-clause

Since SQL Server 2012, you’re able to use the OVER-clause for all sorts of things…as seen in my previous post:
https://clinthuijbers.wordpress.com/2015/03/25/sql-server-window-functions-cheat-sheet-over-clause/
(Items like: ‘ROWS … PRECEDING/FOLLOWING…etc.’)


But…let me show you how you could use this to calculate week-to-date (WTD), month-to-date (MTD), year-to-date (YTD) or full-year (FY).
And best of all, it is fast 🙂

Code:
SQL Server window functions WTD MTD YTD FY code


Results:
SQL Server window functions WTD MTD YTD FY results


Download:
SQL Server window functions WTD MTD YTD FY T-SQL code








Advertisements

SSRS Heat Map in a Table or Matrix

Today I wanted to create a Heat Map (not the geographical one, but in a matrix) and stumbled upon this blog post:
http://www.arrangeactassert.com/how-to-create-heat-maps-in-sql-server-reporting-services/

It uses the colors from green to red, but I’m more of a subtle guy who wants to use softer tones for that. So I picked my own set of pre-defined colors for the VB code to use and here is it:

ssrs-heat-map-matrix-table-code-example

Code:

Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
Dim colours As String() = New String() {"White", "WhiteSmoke", "Gainsboro", "Silver", "DarkGray"}
If textBoxValue = 0 Then
Return colours(0)
End If
If minDataSetValue = maxDataSetValue Then
Return colours(0)
End If
If textBoxValue > maxDataSetValue Then
Return colours(colours.Length - 1)
End If
Dim divider As Decimal = (maxDataSetValue - minDataSetValue + 1) / 4
Dim index As Decimal = (textBoxValue - minDataSetValue) / divider
GetHeatmapColor = colours(index)
End Function

Simply copy/paste this code into Report Properties -> Code and use this expression for your BackgroundColor of the cell in your matrix or table:


=Code.GetHeatmapColor(Sum(Fields!NumberOfUsers.Value), Min(Fields!NumberOfUsers.Value, "DataSet1"), Max(Fields!NumberOfUsers.Value, "DataSet1"))

Note: In my case I set the minimum to 8.0 to ignore very low values which will mess up the color scheme.








T-SQL Metadata Queries

Stumbled upon this great post by Marco Schreuder about how to collect source system metadata with T-SQL queries:
The blog post is a few years old, but still the queries might come in handy.

TSQL Metadata Queries

The queries:

1. Get column information from all tables
2. Get column information from all views
3. Get details of foreign key constraints
4. Get details of the indices created on user tables