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).
Continue reading

SQLXML: How to get related role ad report from a Blackbaud Task (TASKPECXML)

When working with Blackbaud Enterprise CRM, you may create tasks that point to a report. Here’s how to extract the related report and roles from a task:

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                     'http://www.w3.org/2001/XMLSchema' AS xsd,
             'bb_appfx_commontypes' AS common,
             'bb_appfx_pagedefinition' AS pagedef,
              DEFAULT 'bb_appfx_task'), 
taskcte AS 
(
SELECT  
   TC.NAME TASKNAME,
   SR.NAME ROLENAME, 
   R.SYSTEMROLEID, 
   TC.TASKSPECXML,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowReport/@ReportID)[1]', 'varchar(100)') REPORTID_FROMTASKSPEC,
   TC.TASKSPECXML.value('(//TaskSpec/common:ShowPage/@PageID)[1]', 'varchar(100)') PAGEID 
FROM
    V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK R
    INNER JOIN SYSTEMROLE SR
    ON R.SYSTEMROLEID = SR.ID
    INNER JOIN TASKCATALOG TC
    ON R.TASKID = TC.ID
)

SELECT * 
FROM taskcte

Back up SQL Server database to Azure BLOB Storage

To back up your database to Azure BLOB storage, first you need to set up the BLOB storage in your Azure account. You will also need to create a container first.
azure blob storage and container
You can back up the database to Azure by using either PowerShell or T-SQL.
Continue reading

Determining Blackbaud Ad Hoc Queries that use Site in Designation

We had to trace which among our hundreds of Blackbaud Enterprise CRM (BBEC) ad hoc queries were using Site from the Designation node. We also needed to figure out if they were being used as output or being used as filter.

Here’s the script that did the trick.
Continue reading

Getting Grand Total and Subtotals using SQL Server Window Functions

Did you know that you can use SQL Server’s window function in an aggregate, and partition by NULL to get a grand total?

Let me illustrate. In the example below I use my trusty ol’ pubs database. (Hey, it’s still a great database for simple examples. Great for people starting to learn SQL too).

Let’s get some basic numbers first so we know what we are expecting to get.

Using a basic query that gets some grand total, we get 493. This the total for the whole table.

SELECT
SUM(qty)
FROM
sales

sum_sales

Continue reading

Demystifying T-SQL Subqueries–Part II

In the last post (Demystifying T-SQL Subqueries – Part I) we looked at how to use scalar subqueries. Let’s continue our subquery adventure.

As with Part I, the following T-SQL query samples are using the Chinook database.

Subqueries which return a single list of values

Result Description Sample
B Single Column
or Column List
or Single List of Values
clip_image004_thumb

Continue reading

Demystifying T-SQL Subqueries–Part I

When I teach SQL Server Development/T-SQL courses, subqueries is usually one of those topics that students find confusing. Usually it’s a head scratcher, although there are some who just get it.

So let’s try to demystify subqueries.

Very simply put, a subquery is just a query within a query. It’s a SELECT inside another SELECT.

Now let’s break it down. Let’s start with understanding what a query gives you first.

Understanding results of regular queries

Before we can understand subqueries, we need to understand first the different results we get from regular queries. A regular query always results in a dataset. It gives you the following variations of results:

Result Description Sample
A Scalar Value subquery that returns scalar value
B Single Column
or Column List
or Single List of Values
subquery that returns a list (single column) of values
C Table subquery that returns a table

Continue reading

Fixing Orphaned Users – the PowerShell/SMO way – NOT!

I’ve been working with quite a few PowerShell/SMO scripts for the last little while, and so far so good. Just recently I needed to map some orphaned users, and I thought – why not? Let’s do it in PowerShell.
This would have been the script that *should* work:

#unfortunately this doesn't work
$user.Login = "marymargaret";
$user.Alter();
$user.Refresh();

Continue reading