Counting Consecutive Failed Deliveries in TSQL

Learn how to effectively count consecutive failed deliveries in TSQL, ensuring that only unique instances are recorded. This guide provides a clear solution to streamline your delivery tracking process.
---
This video is based on the question https://stackoverflow.com/q/68158445/ asked by the user 'Jessica Yoskovich' ( https://stackoverflow.com/u/14086266/ ) and on the answer https://stackoverflow.com/a/68160671/ provided by the user 'lptr' ( https://stackoverflow.com/u/12403612/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Count consecutive values once in TSQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Counting Consecutive Failed Deliveries in TSQL

In the world of courier delivery, accurately tracking failed deliveries is crucial for enhancing operations. However, when dealing with consecutive failures, simply counting each failed day can lead to inflated numbers. If a delivery fails over consecutive days, we want to log it just once, counting as a single failure instance. This guide will provide a comprehensive solution to dynamically count these consecutive failed deliveries using TSQL.

Understanding the Problem

Let’s clarify the scenario with an example dataset. Consider the following entries in your deliveries table:

DeliveryIdDeliveryDateStatus12021-04-14Success22021-04-15Failure32021-04-16Success42021-04-17Success52021-04-18Failure62021-04-19Failure72021-04-20Failure82021-04-21SuccessFrom this dataset, you can observe that while deliveries failed on the 15th, 18th, 19th, and 20th, we want to count the failure instances as just two: one on the 15th and another failure that encompasses the three consecutive failed days from the 18th to the 20th.

The Solution

To achieve this, we will leverage TSQL’s window functions. Below is a structured breakdown of how to implement a dynamic query to capture the first failed delivery ID for each group of consecutive failures.

Step 1: Sample Data Creation

First, let’s set up a sample in-memory table with your delivery data:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Grouping the Failures

Next, we will utilize the LAG function to identify previous statuses and a calculated column to handle grouping consecutive failures:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

LAG Function: This checks the previous delivery status, enabling us to determine if it's a continuation of a failure.

GROUPING: The SUM of the helper column addorcountme helps us group deliveries based on their failure status. Once a failure is recorded, subsequent failures are considered part of the same group.

Calculating the Group's First Delivery ID: The MIN function extracts the earliest delivery ID from each group when a failure occurs.

Result Interpretation

The resulting table will include a new column “FailedDeliveryId”, which will show the first delivery ID of the consecutive failure events, leading to a clearer summary of failed deliveries without double counting.

Conclusion

Using this TSQL approach, you can effectively track failed deliveries while considering consecutive failure periods as single instances. This reduces reporting clutter and provides more actionable insights into your delivery operations. Implementing this solution will streamline how you analyze failure rates and improve overall efficiency in your delivery process.

This technique can be applied to various scenarios beyond deliveries, making it a valuable addition to your TSQL skills.