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.
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). Continue reading →
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.
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.
Database Design: Solving Problems Before they Start!
Every Byte Counts: Why Your Data Type Choices Matter
Introduction to Microsoft Azure DocumentDB
Offline Database Development and Unit Testing with SSDT
Protecting Your Data with Encryption
BI Information Delivery
Introduction to the Power BI Designer
Understanding and Visualizing Data Using R in SQL Server
BI Platform Architecture, Development & Administration
Don’t Repeat Yourself – An Introduction to Agile SSIS Development
Improving Data Analytics with Azure Machine Learning
Optimizing your data warehouse for OLAP Processing
Rethink your ETL with BIML
SSAS Design and Performance Best Practices
Cloud Application Development & Deployment
Azure IOT: A Practical Introduction to Stream Analytics
Enterprise Database Administration & Deployment
Common SQL Server Mistakes and How to Avoid Them
Curious Cases of Availability Groups
Data Pages, Allocation Units, IAM chains… Oh My!
Overlord – tracking changes, real-time, in your environment
SQL Server Agent: The life preserver for the drowning DBA
The Art and the Science of Designing a Mission Critical SQL Server Solution
The Data Loading Performance Presentation
Trend Analysis of SQL Error Logs: Seeing Beyond the Error
Turbo-Charged Transaction Logs
Making the Leap from Developer to DBA
SQLSaturday #407 in Vancouver, BC
SQLSaturday #407 in Vancouver, BC is happening on June 27, 2015 at UBC Robson Square, 800 Robson Street, Vancouver. SQLSaturdays are one day free training events for SQL Server professionals. SQL Server professionals have developed a pretty tight knit community over the years, and this is one way of sharing information, learnings, experiences and best practices on the product. This event is also volunteer-driven – the organizers and presenters volunteer their time and effort to help foster the #SQLCommunity.
Sessions delivered range from SQL Server Administration, Development, Business Intelligence Application, Business Intelligence Delivery, and Professional Development.
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.
You can back up the database to Azure by using either PowerShell or T-SQL. Continue reading →
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.
Note: DSC can only be run if you have at least Windows Management Framework 4.0 and if your operating system is at least Windows 8.1 or Windows Server 2012 R2.
The PowerShell team has released the xSQLPs module as part of the Desired State Configuration (DSC) Resource Kit. xSQLPs contains xSqlServerInstall, which you can use as a starting script when installing SQL Server via PowerShell and DSC. Download the module here.
This is what I needed to do to install SQL Server using DSC and the xSQLPs module. Many of these steps are from the xSQLPs documentation page.