Monthly Archives: August 2015

Getting maximum consecutive years in T-SQL using Common Table Expressions (CTE)

This is an expansion (and slight variation) of Joe’s answer in StackOverflow regarding the thread Find the maximum consecutive years for each ID’s in a table (Oracle SQL) using SQL Server. I’ve tested this in SQL Server 2014, but should work from SQL Server 2005 onwards where the ranking functions are supported.

The problem Joe solved is not uncommon – i.e. how to get the consecutive years of something (consecutive year sales, consecutive years in school, consecutive years volunteering etc), and his solution is pretty clever.

Let’s assume you are working on a sales database. You have multiple clients who could be purchasing from your store several times a year, or a couple of times every five years. You may want to know what’s the maximum consecutive years they’ve purchased from you. Why? Perhaps in a marketing campaign, you may want to give your loyal customers (purchased in 5 or more consecutive years) a special discount.

Your data may look like the following screenshot. Notice that in this example, Client 00001, 00002 and 00003 purchased only once. Client 00004 purchased several times, and it looks like there were purchases in consecutive years.

Download sample T-SQL script to create and populate the SAMPLESALES table

01 All Revenue

Let’s take it step by step to understand both the problem and solution better. It will be easy to visually identify which clients have purchased consecutive years if we first display all the unique years that client has purchased:

SELECT 
    DISTINCT
    CLIENTID,
    CLIENTNAME,
    YEAR(REVENUEDATE) REVENUEYEAR
FROM
    SAMPLESALES

Here’s the result:

Client 00004

Here we can see Client 00004 purchased in 2004 and 2005 (2 consecutive years), but did not purchase in 2006. Starting 2007, Client 0004 started purchasing again every year until 2012 (6 consecutive years).
Read more

More reasons why I teach

This is my fuel. This is why I teach. Sure, I won’t please everyone. Not everyone will appreciate or will be happy with the way I teach, but even if there’s one person in the class who thinks the time they spent in my class was worth it – that’s all the reason I need.

(All names removed from cards below)

This is what I got today from a student:

card from studentcard content

Read more

Automate SSRS Report Generation using PowerShell

This blog post is tested on SQL Server 2014 and PowerShell V5.

PowerShell has become more feature-rich in every version. We are now looking at PowerShell V5 (currently can be downloaded as part of Windows Management Framework v5 April 2015 Preview).

The support landscape with SQL Server hasn’t changed much. There isn’t a drastic increase in SQL Server cmdlets. However, the language and feature improvements in PowerShell in general improve how we can work with SQL Server.

One area where we can use PowerShell is with SQL Server Reporting Services (SSRS). I blogged about this a while back, but it’s time to revisit and expand on how we can use PowerShell to automate report generation.

In this blog post I will focus on generating PDF reports via scripting. Let’s tackle this piece by piece first, and we’ll put everything in a nice little script at the end of the post.

Read more

Windows 10 and Visual Studio 2015 Jumpstart and Resources

Get Started. Design. Develop.Publish

Windows 10 and Visual Studio 2015 have been released.

Microsoft claims Windows 10 as the best OS yet and Visual Studio 2015 has been enriched with ability to work on desktop, mobile (Windows, Android, and iOS), web and cloud applications and services. (It’s pretty sweet that there is a free Visual Studio Community version too!)

Here are some resources you’ll need to get started:

Once you have the tools and gears in place, have fun!