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.

See full article here at sqlbelle.com

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)

See full blog post at sqlbelle.com

Safe document sharing and virtual data room (VDR) solutions simplify workflows, reduce administrative overhead and enforce strict access controls across all environments. By automating and managing data, sensitive files are organized, easily found and systematically archived to prevent version conflicts and breaches. Here securevdr.info explains how to choose and integrate VDRs with encryption, custom permissions and audit trails for better information handling and knowledge transfer.

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.

First, we need to add the ReportViewer assembly to the script. For this I am using the WinForms report viewer assembly. There is similar report viewer assembly for web forms.

See full article

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 (including tools, online courses).

We can use PowerShell to discover many SSRS properties, including the different parameters available in a report. This blog post is based on SQL Server 2014 and PowerShell V5.

The example report utilizes different variations of parameters. In the example, Customer ID is an integer. Order Date From and To are dates. Online Flag is a boolean. Min Total Due is a float. Status is a multi-value text.

Additional properties can be found in the Report Parameter window, including whether the parameter is nullable, if it’s multivalued, or if it’s visible or hidden, etc.

To do this in PowerShell, the first thing to do is to add the assembly that will allow us to create the ReportViewer object. Note that the version and public key token are specified, so check the version you have on your machine to ensure the accuracy.

See full blog post here

It’s time for a change. I will be posting new blogs over at http://sqlbelle.com

It’s PASS Summit time again! #Summit14 is happening in Seattle, WA – November 3-7, 2014 at the Washington State Convention and Trade Center.

I still remember the first time I attended PASS (started with a pre-con from Brian Knight no less )…

I was wide-eyed, starstruck. Who would have thought that attending a technical conference would feel like attending your first rock concert ever? That’s what I felt like. I was meeting and talking to people who have authored books, articles, blog posts. I remember going up to a few of them, their authored books in hand, asking for their autographs

At the time, tweeting wasn’t really a big thing yet. But I did see the bloggers table (more like watched in amazement), where a lot of the rockstars blogged their way through the keynote. I would refresh my browser every so often and check who has already posted a new entry for the day (or morning). A lot of them also summarized sessions, Day 1 adventures, Day 2 adventures, #sqlkaraoke etc.

The first couple years I attended PASS, I admit I was quite intimidated and shy, and would resort to a corner whenever I got the chance. Although through the years I was able to muster up some courage to talk to some of the rockstar bloggers and meet a few more folks. You know what I discovered, a lot of them are really nice. And no they don’t bite.

It might have been the second or third year of me attending the PASS Summit when live tweeting became popular. My phone battery always drained like crazy, so I had to switch between phone and laptop to keep up with the tweets. This was a fun time. I got to “meet” more people through the tweets. And before long, I did meet a lot of them in person. It was hard not call them by their twitter handles at first though. I still remember calling Jorge Sergarra @sqlchicken in person the first time I met him at PASS.

Aahh, time flies. Attending PASS for me this year, I’m sure, will still feel like the first time I attended PASS. I will still feel giddy, and nervous, and shy. But all it takes is that first chat with #sqlfamily …

My advice to anyone attending PASS for the first time?
– Just enjoy it.
– Go and say hi. Muster up that courage and say hi to one of the SQL Rockstars you look up to. Go and say hi to a fellow SQL Server professional at the breakfast/lunch table.
– Pat yourself on the back. You made a good decision attending PASS. It could be the start of something good, something better. No I am not saying what you have right now or where you are right now is not good. Not at all. All I am saying is, there is a lot to gain by meeting the #sqlcommunity.
– If and when you can, attend a pre-con. I enjoy attending the pre-cons when I get the chance. You learn a LOT. Your brain will be so full, and the conference hasn’t even started yet.
– If and when you can, get those conference recordings. It’s a good investment. I knew there were too many good sessions, and I just can’t physically attend all of them. So I have tried to get the PASS recordings whenever I can. It’s like my Netflix of SQL Server stuff. At night, I can just load up my laptop with a session, watch one, or half, or two, before I head to bed.

A few more weeks. I can’t wait. The SQL Server community (#sqlfamily hash tag in twitterverse) is an amazing community. I can’t wait to join them again in #Summit14. I have no doubt, it will feel like the first time.

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

Read the rest of this entry »