Working with legacy applications can sometimes present challenges, especially when dealing with poorly designed databases. In this article, we will discuss a common problem encountered in a legacy application and explore a solution to handle aggregated calculations in SQL Server.

The Problem

Imagine working on a desktop application that uses SQL views to display data and stored procedures to interact with the database. The application has a module for timekeeping, where users log their hours. Each user is assigned to a team, and the logged hours are recorded in a separate table called WorkLog.

However, the database design is flawed. Users can be assigned to multiple teams, resulting in duplicate entries in the Users table. Additionally, there may be records in the Users table with no related records in the WorkLog table. This poses a challenge when calculating aggregated values.

The Solution

To address this issue, we can create a separate view that adjusts the aggregated calculations. Here are the steps:

1. Fetch all users assigned to more than one team.
2. Calculate the count of teams they are assigned to.
3. Subtract 1 from the count.
4. Fetch the actual hours worked by the user.
5. Calculate AdjustedHours as -(count * actual hours worked).

We can then create a view, vwWorkLogByUserAdjusted, that combines the original data with the adjusted values using a UNION. This ensures that the aggregated count returns the exact match.

Implementation

Let’s take a look at the SQL script to create the necessary tables and views:

CREATE TABLE Teams (
  TeamID INT NOT NULL IDENTITY,
  TeamName VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Teams PRIMARY KEY (TeamID)
)

CREATE TABLE Users (
  UserID INT NOT NULL IDENTITY,
  UserKey INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  TeamID INT NOT NULL,
  CONSTRAINT PK_Users PRIMARY KEY (UserID),
  CONSTRAINT FK_Users_Teams FOREIGN KEY (TeamID) REFERENCES Teams(TeamID)
)

CREATE TABLE WorkLog(
  LogID INT NOT NULL IDENTITY,
  UserKey INT NOT NULL,
  HoursWorked INT NOT NULL,
  CONSTRAINT PK_WorkLog PRIMARY KEY (LogID)
)

CREATE VIEW [dbo].[vwWorkLogByUserAdjusted]
AS
SELECT
  us.Name,
  SUM(wl.HoursWorked) HoursWorked
FROM Users us
INNER JOIN WorkLog wl ON wl.UserKey = us.UserKey
GROUP BY us.Name
UNION
SELECT
  us.Name,
  -(AdjustedUsers.UserCount - 1) * (SUM(wl.HoursWorked)/AdjustedUsers.UserCount) HoursWorked
FROM Users us
INNER JOIN WorkLog wl ON wl.UserKey = us.UserKey
CROSS APPLY (
  SELECT UserKey, COUNT(UserKey) UserCount
  FROM Users 
  WHERE Users.UserKey = us.UserKey
  GROUP BY UserKey 
  HAVING COUNT(UserID) > 1
) AdjustedUsers
GROUP BY us.Name, AdjustedUsers.UserCount

ALTER VIEW [dbo].[vwWorkLogByUser]
AS
SELECT 
  Name,
  SUM(HoursWorked) HoursWorked
FROM [dbo].[vwWorkLogByUserAdjusted]
GROUP BY Name

By modifying the original view definition to fetch data from the adjusted view, we can ensure that the calculations are accurate.

Conclusion

Handling aggregated calculations in SQL Server can be challenging, especially when dealing with poorly designed databases. In this article, we explored a solution to address inaccurate results caused by duplicate entries and missing records. By creating a separate view and adjusting the calculations, we can ensure accurate aggregated values.

While this solution may not be the most optimized, it provides a starting point for handling similar scenarios. We encourage the community to share their thoughts and suggestions on this implementation.