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

Today has been quite an eventful day for me, feels like a mishmash of stuff happened today. Definitely worth a blog post.

Here are some of the things I learned and did today.

And man it amazes me how many smart smart helpful people are out there

Service Broker

I possibly might need to work on SQL Server Service Broker. I think this might be the right tool for a project, but I need to be certain so I need to give it a test drive.

Thanks to Aaron Bertrand (blog | twitter) for pointing me to these excellent excellent resources on SQL Server Broker.

http://mrdenny.com

http://http://itknowledgeexchange.techtarget.com/sql-server/tag/service-broker/

http://rusanu.com

Read the rest of this entry »

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================

   2: -- Object       : OPENXML1.sql

   3: -- Object Type  : Script

   4: -- Description  : Various examples

   5: -- Developer    : Donabel Santos

   6: -- Origin       : 2008/08/17

   7: -- Last Modified: 2008/10/04

   8: -- Notes        : 

   9: -- ==========================================================================

  10:  

  11: -- bulk load

  12: -- For this example, XML file must be saved in C:

  13: -- XML file also should specify UTF-8 encoding, ie:

  14: -- <?xml version="1.0" encoding="UTF-8"?>

  15:  

  16: DECLARE @XMLTable TABLE

  17: (

  18:    xmlcol XML

  19: )

  20:  

  21: INSERT INTO @XMLTable(xmlcol)

  22: SELECT

  23:     InvoicesXML

  24: FROM

  25: (

  26:     SELECT * 

  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices

  28: ) AS Invoices(InvoicesXML)

  29:  

  30: SELECT *

  31: FROM @XMLTable

  32:  

  33: