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
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:
Here’s the result:
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).
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.
Do you know how dates work in Tableau? It could be tricky, but once you get a handle on it, it can make your Tableau life much simpler.
The first step to understanding dates in Tableau is understanding the concept of discrete and continuous first. These are two very important concepts in Tableau that, if not clearly understood, can definitely cause a lot of confusion (and headaches). It can make you think you’re getting unexpected vizzes or behavior from the application, and that it’s not doing what it’s supposed to do.
A quick Google search lands us the definitions for these two terms:
discrete – individually separate and distinct
continuous – forming an unbroken whole; without interruption
Let’s tie these two terms back to Tableau:
|definition: individually separate and distinct
||definition: forming an unbroken whole; without interruption
|gives you headers
||gives you axes
Last week at the PASS Summit 2014, I attended Matt Masson and Tessa Palmer’s session on Power Query Deep Dive. One of Tessa Palmer’s demo was a shout out to the Vancouver Canucks and how she has kept track of the standings of the Canucks by using Power Query.
That demo really piqued my interest (I like the Canucks too, but they keep on breaking my heart), so I wanted to try it out myself. This is my first stab at it, so I will probably look back at this post a few months from now and see where I could improve the process.
1.First thing to do, after installing and setting up Power Query, is to get the URL for the NHL standings site. The division standings can be found in: http://www.nhl.com/ice/standings.htm?type=DIV#&navid=nav-stn-div
The data is deeply embedded and formatted in HTML tables, as can be seen in the screenshot below. Getting this data pre-Power Query would have been possible, but would have required some nifty programming
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
or Column List
or Single List of Values
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:
or Column List
or Single List of Values
I will be doing a series of tutorials (some simple, some more involved) in SSIS in the next little while. In addition to step by step instructions, I will also be providing the video tutorial equivalents which will be posted at the Black Ninja Software website. Once the video tutorials are up, I will be updating the individual links.
Now for this simple problem. You have a MySQL database, and you want to be able to dump data from it to SQL Server using SSIS.
Here are the steps:
1. If you haven’t already, download and install MySQL Connector for ODBC.
2. Create a DSN for your MySQL
a. Go to Start > Administrative Tools > Data Sources (ODBC)
b. Create a new User DSN (in my case I called it MySQL55)
Every now and then I need to resize my VMWare Server disks.
The is the easiest and least troublesome way, from my experience, is to use GParted Live (Gnome Partition Editor)
1. Download GParted (Gnome Partition Editor) Live (http://sourceforge.net/projects/gparted/files/gparted-live-stable/). It is an iso file that we will mount to our vm
2. Locate where your vmware-vdiskmanager.exe is. It would typically reside in the install folder, for example:
C:Program Files (x86)VMwareVMware Servervmware-vdiskmanager.exe
3. Note the path to your virtual disk (vmdk). For example:
vmware-vdiskmanager.exe -x 20GB "C:Virtual MachinesSQL2005SP3
Windows Server 2003 Enterprise EditionWindows Server 2003 Enterprise Edition.vmdk"
Download PDF file
I typically use SQL Server VMs for my development and administration classes. I want the students to have full autonomy over the machines they are using, so that they can try out different features and configurations without worrying about wrecking a whole lab machine.