We are SQL Server Experts. Call Us TODAY! +17738095456 or email us at sales@itimpact.com
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
  • The Team
  • Our Services
    • Dashboards Developers
    • Microsoft SQL Server
    • Microsoft SQL Server hosting for Access
    • SQL Server Integration Services (SSIS)
    • SQL Server Reporting Services (SSRS)
    • SQL Server Data Quality Services (DQS)
    • Microsoft Access
    • Microsoft VB.Net
    • Cross Functional / Business Unit Integration
    • Financial Statement Consolidation
    • Website Design
  • Case Studies
    • Automatic Reporting for Security Services Firm
    • Amazon API Integration with Access
    • Custom Quoting and Proposal Solution
    • Data Quality Analysis for Government Program
    • New Claim System Reduces Processing Time
    • Pipe Tracking Software
    • Product Search Analysis
    • Safety Training, Incident and Accident Tracking Tool
    • Union Dues and Membership Management System
  • Blog
  • Contact
Toggle panel Social icons
Blog

T-SQL: How to Calculate Row-Level Aggregate Values

In one of the systems we support, there is a SQL Server table with a bunch of date columns that store information on the most recent dates for several types of services a customer receives.  Some of these service types are called “basic”, “intensive”, and “training”, for example.  These are some sample rows:

img1

We needed to generate a report that indicated the most recent service a customer had received, of any type.  The end result we wanted was this:

img2

A couple of solutions came to mind:

1-      Use a scalar function with nested IF statements.
2-      Pivot the data and then calculate the MAX per customer.

The first one gets way too complex as more columns need to be considered (in our case, about 10).  The second one seemed to require a complex query or perhaps two.  The solution that appear to be both simpler and more elegant was to use a derived table for the column values of each row and then calculate the MAX, like this:

img3

Recent Posts

  • 23 Jan. 2019 7 essential SQL skills that can help you command a higher paycheck

  • 18 Jan. 2018 Quick Guide on Importing Data from SQL Server (or SQL Azure) to Cosmos DB

  • 10 Jan. 2018 Top 7 Questions about Basic Availability Groups

  • 04 Jan. 2018 An introduction to mssql-cli, a command-line client for SQL Server

  • Share and work together on the intranet with SharePoint Team Sites

ITImpact

About IT Impact, Inc.

IT Impact, Inc.

IT Impact, Inc. is a minority-owned software company specializing in custom software solutions. We work with SQL Server, Microsoft Access, web applications, and much more.

Our staff is top of the line and includes two Microsoft MVPs!

Twitter

Tweets by @ITImpactInc

Facebook

© ITImpact. Webdesign by GreatCustomWebsites.com