Data mismatches are one of the most common problems in inventory-based systems.
Fields like Quantity, QuantityOnHand, QuantityAvailable, QuantityReserved, and QuantityIssued often fall out of sync after years of operations, faulty transactions, partial rollbacks, or integration errors.

This article explains how to design an automatic background job (SQL + .NET) that continuously scans mismatches and fixes them safely.

All headings are intentionally kept small, as per your preference.

1. Problem Overview

Typical mismatches include:

A robust job must handle:

2. Target Tables and Fields

Example: StockLine table

FieldMeaning
QuantityTotal quantity in stock line
QuantityOnHandPhysical available quantity
QuantityIssuedQuantity issued
QuantityReservedQuantity reserved
QuantityAvailableDerived: QuantityOnHand - Reserved

3. Architecture Overview

.NET Background Service or SQL Job
        ↓
Mismatch Detection SP (SQL)
        ↓
Fix Processor (SQL or .NET)
        ↓
Audit Log
        ↓
Email / Dashboard Notification

4. ER Diagram (Simplified)

StockLine (PK: StockLineId)
 |
 |--- StockTransactions (PK: TransactionId, FK: StockLineId)
 |
 |--- Reservations (PK: ReservationId, FK: StockLineId)

5. Workflow / Flowchart (Auto-Fix Job)

Start
  ↓
Scan mismatches (SP)
  ↓
For each mismatch:
  ↓
Set correct values
  ↓
Save fix into AuditFixLog
  ↓
Notify via email/dashboard
  ↓
End

6. SQL Procedure – Detect Mismatches

CREATE PROCEDURE usp_DetectStockMismatches
ASBEGIN
    SELECT 
        SL.StockLineId,
        SL.Quantity,
        SL.QuantityOnHand,
        SL.QuantityReserved,
        SL.QuantityIssued,
        SL.QuantityAvailable,

        (SL.QuantityOnHand - SL.QuantityReserved) AS ExpectedAvailable,
        (SL.Quantity - SL.QuantityIssued) AS ExpectedOnHand
    FROM StockLine SL
    WHERE 
        SL.QuantityAvailable <> (SL.QuantityOnHand - SL.QuantityReserved)
        OR SL.QuantityOnHand <> (SL.Quantity - SL.QuantityIssued)
        OR SL.Quantity < 0
        OR SL.QuantityOnHand < 0
        OR SL.QuantityReserved < 0;
END

7. SQL Procedure – Auto Fix the Mismatch

CREATE PROCEDURE usp_FixStockMismatch
    @StockLineId BIGINTASBEGIN
    DECLARE @Qty INT, @Issued INT, @Reserved INT;

    SELECT 
        @Qty = Quantity,
        @Issued = QuantityIssued,
        @Reserved = QuantityReserved
    FROM StockLine
    WHERE StockLineId = @StockLineId;

    DECLARE @CorrectOnHand INT = @Qty - @Issued;
    DECLARE @CorrectAvailable INT = @CorrectOnHand - @Reserved;

    -- Log before fix
    INSERT INTO StockFixLog(StockLineId, OldData, NewData, FixedAt)
    SELECT
        @StockLineId,
        (SELECT * FROM StockLine WHERE StockLineId = @StockLineId FOR JSON AUTO),
        NULL,
        GETDATE();

    -- Apply fix
    UPDATE StockLine
    SET
        QuantityOnHand = @CorrectOnHand,
        QuantityAvailable = @CorrectAvailable
    WHERE StockLineId = @StockLineId;

    -- Log after fix
    UPDATE StockFixLog
    SET NewData = (SELECT * FROM StockLine WHERE StockLineId = @StockLineId FOR JSON AUTO)
    WHERE StockLineId = @StockLineId AND NewData IS NULL;
END

8. Background Job (ASP.NET Core)

public class AutoFixJob : BackgroundService
{
    private readonly IServiceProvider _provider;

    public AutoFixJob(IServiceProvider provider)
    {
        _provider = provider;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await RunFixProcess();
            await Task.Delay(TimeSpan.FromMinutes(30), stoppingToken);
        }
    }

    private async Task RunFixProcess()
    {
        using var scope = _provider.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();

        var mismatches = await db.StockMismatches.FromSqlRaw(
            "EXEC usp_DetectStockMismatches"
        ).ToListAsync();

        foreach (var row in mismatches)
        {
            await db.Database.ExecuteSqlRawAsync(
                "EXEC usp_FixStockMismatch {0}", row.StockLineId);
        }
    }
}

9. Sample Dashboard Mockup (for Monitoring)

-----------------------------------------------------
| Stock Auto-Fix Dashboard                           |
-----------------------------------------------------
| Total Mismatches Today: 32                         |
| Auto-Fixed Successfully: 30                        |
| Failed Fixes: 2                                    |
-----------------------------------------------------
| Recent Fix Log                                     |
| StockLineId | Issue                | FixedAt       |
|--------------------------------------------------- |
| 55012        | Negative Available   | 12:15 PM      |
| 55013        | Wrong OnHand Calc    | 12:17 PM      |
-----------------------------------------------------

10. Scheduler Options

Choose any of the following:

11. Best Practices

12. Production-Level Enhancements