How to restrict duplicate values in a column in sql server record?

To ensure that each FarmName has a unique TripsheetNo, you can add a unique constraint or unique index on the TripsheetNo and FarmName in your Tripsheet table. This will prevent any new records from being inserted that would result in a duplicate TripsheetNo for the same FarmName.

However, before you can add this constraint, you will need to deal with any existing duplicates in your data. You can identify these with a query like:

SELECT TripsheetNo, FarmName, COUNT(*)
FROM Tripsheet
GROUP BY TripsheetNo, FarmName
HAVING COUNT(*) > 1

 

Once you have dealt with the duplicates (for example, by deleting or modifying the duplicate rows), you can add the unique constraint:

 

ALTER TABLE Tripsheet
ADD CONSTRAINT UC_TripsheetNo_FarmName UNIQUE (TripsheetNo, FarmName);

 

This will ensure that you cannot have more than one FarmName with the same TripsheetNo. Please note that this is a significant change to your database schema, so you should make sure to backup your data before proceeding, and thoroughly test your application afterwards to ensure there are no unforeseen side effects.

Also, please note that this solution assumes that TripsheetNo and FarmName are columns in your Tripsheet table. If they are not, you will need to adjust the queries accordingly. If FarmName is not stored directly in the Tripsheet table and is only accessible via a join, then the situation is more complex and might require changes to your application logic. In such a case, you might need to handle this in your application code to check for duplicates before inserting new records.

Thanks

Hi Gcobani Mkontwana,

 

Try below solution

USE [Batcher]
GO
/****** Object:  StoredProcedure [dbo].[GetTripWeightReport]    Script Date: 2024/04/30 15:19:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTripWeightReport] --'2024-04-24', '2024-04-25'
(
    @StartDate Date,
    @EndDate Date
)
AS
BEGIN
    -- Temporary table to store distinct tripsheet numbers per FarmName
    SELECT
        t.TripsheetNo,
        t.ProductWeight AS 'TripSheetReport Weight',
        cu.Customer,
        f.FarmID,
        ROW_NUMBER() OVER (PARTITION BY f.FarmID ORDER BY t.TripsheetNo) AS RowNum
    INTO #FINISHEDPRODUCT
    FROM [dbo].[Tripsheet] t
    LEFT JOIN [dbo].[WeighbridgeTicket] wt ON t.TripsheetNo = wt.trip
    LEFT JOIN [dbo].[Customer] cu ON cu.CustomerID = wt.CustomerID
    LEFT JOIN [dbo].[Farm] f ON f.FarmID = cu.Farmid
    WHERE CONVERT(DATE, t.Tripdate, 103) BETWEEN @StartDate AND @EndDate
        AND topseal <> '' 
        AND bottomseal <> ''
    
    -- Filter out duplicates based on FarmName and TripsheetNo
    DELETE FROM #FINISHEDPRODUCT WHERE RowNum > 1;

    -- Your existing sales report query here
    SELECT DISTINCT
        s.ProdDay,
        s.Tripsheetno,
        s.[SalesReport Weight],
        ISNULL(cu.Customer, '') as Customer,
        ISNULL(f.FarmID, '') as FarmName,
        ISNULL(f.[TripSheetReport Weight], 0) as [TripSheetReport Weight],
        ISNULL(f.[TripSheetReport Weight], 0) - s.[SalesReport Weight] AS Discrepancy,
        CASE
            WHEN ISNULL(f.[TripSheetReport Weight], 0) != s.[SalesReport Weight] THEN 'Warning'
            WHEN s.[SalesReport Weight] = f.[TripSheetReport Weight] THEN 'PASS'
        END AS ErrorWarning
    FROM #SALES s
    LEFT JOIN #FINISHEDPRODUCT f ON s.Tripsheetno = f.TripsheetNo
    LEFT JOIN [dbo].[Customer] cu ON cu.CustomerID = cu.CustomerID
    LEFT JOIN [dbo].[Farm] fa ON f.FarmID = cu.Farmid;

    -- Drop temporary tables
    DROP TABLE #FINISHEDPRODUCT;
    DROP TABLE #SALES;
END

Hi All

I have modified the query for both answers, but somehow this still give me same output as before. 

Hi,

Problem

Upon analyzing the code, I have identified the following issues that may be causing the bug:

1. The temporary tables "#FINISHEDPRODUCT" and "#SALES" are being created with the "SELECT DISTINCT *" statement. This can lead to unexpected results if there are duplicate rows in the underlying data.

2. The join condition between the "#FINISHEDPRODUCT" and "#SALES" tables is using the "TripsheetNo" column. If there are duplicate "TripsheetNo" values in either table, it can result in incorrect data being joined.

3. The join condition between the "#FINISHEDPRODUCT" table and the "Customer" and "Farm" tables is using the wrong column names. It should be "cu.CustomerID" instead of "cu.CustomerID" and "fa.FarmID" instead of "f.FarmID".

Solution

 

To fix the bug and improve the code, I recommend the following solutions:

1. Instead of using the "SELECT DISTINCT *" statement, explicitly specify the columns needed for the temporary tables "#FINISHEDPRODUCT" and "#SALES". This will ensure that only the necessary columns are selected and eliminate any duplicate rows.

2. Modify the join condition between the "#FINISHEDPRODUCT" and "#SALES" tables to include additional columns that uniquely identify a trip. For example, you can include the "FarmID" column from the "#FINISHEDPRODUCT" table and the "ProdDay" column from the "#SALES" table in the join condition.

3. Correct the join condition between the "#FINISHEDPRODUCT" table and the "Customer" and "Farm" tables by using the correct column names. Replace "cu.CustomerID" with "cu.CustomerID" and "f.FarmID" with "fa.FarmID".

Here's the modified code with the suggested fixes:

USE [Batcher]
GO

ALTER PROCEDURE [dbo].[GetTripWeightReport] --'2024-04-24', '2024-04-25'
(
    @StartDate Date,
    @EndDate Date
)
AS
BEGIN
    -- Create temporary table #FINISHEDPRODUCT with explicit column names
    SELECT
        DISTINCT t.TripsheetNo,
        t.ProductWeight AS 'TripSheetReport Weight',
        cu.Customer,
        f.FarmID
    INTO #FINISHEDPRODUCT
    FROM [dbo].[Tripsheet] t
    LEFT JOIN [dbo].[WeighbridgeTicket] wt ON t.TripsheetNo = wt.trip
    LEFT JOIN [dbo].[Customer] cu ON cu.CustomerID = wt.CustomerID
    LEFT JOIN [dbo].[Farm] f ON f.FarmID = cu.Farmid
    WHERE
        CONVERT(DATE, t.Tripdate, 103) BETWEEN @StartDate AND @EndDate
        AND topseal <> ''
        AND bottomseal <> ''

    -- Create temporary table #SALES with explicit column names
    SELECT
        DISTINCT ProdDay = CASE
                                WHEN CAST(WT.LoadedTime AS Time) > '00:00:00' AND CAST(WT.LoadedTime AS Time) < '06:00:00'
                                THEN DATEADD(day, -1, Convert(DATE, WT.LoadedDate, 103))
                                ELSE Convert(DATE, WT.LoadedDate, 103)
                            END,
        tps.TripsheetNo,
        CASE
            WHEN tps.TripsheetNo = 229080 AND WT.LoadedDate = @StartDate THEN 28020
            ELSE SUM(tk.productweight)
        END AS 'SalesReport Weight',
        WT.LoadedTime,
        WT.LoadedDate
    INTO #SALES
    FROM [Batcher].[dbo].[Tripsheet] tps
    INNER JOIN [Batcher].[dbo].[WeighbridgeTicket] tk ON tps.tripsheetno = tk.trip
    LEFT JOIN (
        SELECT trip,
               LoadedDate = MIN(WeighTime),
               LoadedTime = SUBSTRING(CONVERT(VARCHAR, MIN(Weightime), 108), 12, 8)
        FROM [Batcher].[dbo].[WeighbridgeTicket]
        WHERE Compartment <> 'truck-empty'
        GROUP BY trip
    ) WT ON tk.trip = WT.trip
    LEFT JOIN Batcher.dbo.TripReturnRedelivery TRR ON TRR.Ticket = TK.ticketnumber
    WHERE tk.productweight > 0
    GROUP BY
        CASE
            WHEN CAST(WT.LoadedTime AS Time) > '00:00:00' AND CAST(WT.LoadedTime AS Time) < '06:00:00'
            THEN DATEADD(day, -1, Convert(DATE, WT.LoadedDate, 103))
            ELSE Convert(DATE, WT.LoadedDate, 103)
        END,
        tps.TripsheetNo,
        WT.LoadedTime,
        WT.LoadedDate
    WHERE CONVERT(datetime, Core.LoadedDate, 103) BETWEEN @StartDate AND @EndDate;

    -- Retrieve the final report with corrected join conditions
    SELECT DISTINCT
        s.ProdDay,
        s.Tripsheetno,
        s.[SalesReport Weight],
        ISNULL(cu.Customer, '') AS Customer,
        ISNULL(fa.FarmID, '') AS FarmName,
        ISNULL(f.[TripSheetReport Weight], 0) AS [TripSheetReport Weight],
        ISNULL(f.[TripSheetReport Weight], 0) - s.[SalesReport Weight] AS Discrepancy,
        CASE
            WHEN ISNULL(f.[TripSheetReport Weight], 0) != s.[SalesReport Weight] THEN 'Warning'
            WHEN s.[SalesReport Weight] = f.[TripSheetReport Weight] THEN 'PASS'
        END AS ErrorWarning
    FROM #SALES s
    LEFT JOIN #FINISHEDPRODUCT f ON s.Tripsheetno = f.TripsheetNo
    LEFT JOIN [dbo].[Customer] cu ON cu.CustomerID = cu.CustomerID
    LEFT JOIN [dbo].[Farm] fa ON fa.FarmID = cu.Farmid
END

The provided stored procedure has duplicate records because it doesn't enforce uniqueness on the TripsheetNo within each FarmName

1. Modify the #FINISHEDPRODUCT Selection:

Instead of SELECT DISTINCT, use SELECT TOP 1 WITH TIES in the first section where you populate the #FINISHEDPRODUCT temporary table. This ensures you only get one record per TripsheetNo for each FarmName, prioritizing the record with non-empty topseal and bottomseal values.

Here's the modified code:

SELECT TOP 1 WITH TIES
  t.TripsheetNo,
  t.ProductWeight AS 'TripSheetReport Weight',
  cu.Customer,
  f.FarmID
INTO #FINISHEDPRODUCT
FROM [dbo].[Tripsheet] t
LEFT JOIN [dbo].[WeighbridgeTicket] wt ON t.TripsheetNo = wt.trip
LEFT JOIN [dbo].[Customer] cu ON cu.CustomerID = wt.CustomerID
LEFT JOIN [dbo].[Farm] f ON f.FarmID = cu.Farmid
WHERE
  CONVERT(DATE, t.Tripdate, 103) BETWEEN @StartDate AND @EndDate 
  And topseal <> '' and bottomseal <> '';

2. No Need for Additional Filtering in #SALES:

The filtering logic within the subquery for #SALES already accounts for handling weights loaded before 6 AM on the current date by using DATEADD(day, -1). You can remove the additional filtering based on @StartDate and TripsheetNo=229080 in the CASE statement.

3. Update the Final SELECT Statement:

The final SELECT statement uses the temporary tables to join data. No changes are needed here.

With these modifications, the stored procedure will ensure unique TripsheetNo per FarmName in the #FINISHEDPRODUCT table, leading to accurate discrepancy calculations in the final result set.