sqlsql-serversql-scripts

declare table inserted values duplicated


I try to get two different table values to insert one declare table but the employee ID is duplicate,

DECLARE @WSotable TABLE 
                  (
                      Employee int, 
                      ChargedQty int,
                      ReturnQty int
                  )

--insert first table data

INSERT INTO @WSotable
    SELECT DISTINCT
        MD_Employee.ID,
        SUM(TR_InvoiceDetails.InvoiceQuantity) AS ChargedQty,
        0 AS ChargedQty
    FROM
        TR_SalesOrderHeader 
    INNER JOIN 
        TR_InvoiceHeader ON TR_InvoiceHeader.SalesOrderID  = TR_SalesOrderHeader.ID 
    INNER JOIN 
        TR_InvoiceDetails ON TR_InvoiceDetails.InvoiceID = TR_InvoiceHeader.ID
    INNER JOIN 
        MD_ItemMaster ON TR_InvoiceDetails.ItemID  =  MD_ItemMaster.ID
    INNER JOIN 
        MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
    INNER JOIN 
        MD_Employee ON TR_SalesOrderHeader.CreatedBy = MD_Employee.ID
    WHERE
        MD_Supplier.ID = 3 
        AND MD_ItemMaster.ID = 572
    GROUP BY
        MD_Employee.ID

--insert second table data

INSERT INTO @WSotable
    SELECT DISTINCT
        MD_Employee.ID,
        0 AS ChargedQty,
        SUM(TR_SalesReturnDetails.ReturnQuantity) AS ReturnQty
    FROM
        TR_SalesReturnHeader
    INNER JOIN
        TR_SalesReturnDetails ON TR_SalesReturnDetails.SalesReturnID = TR_SalesReturnDetails.ID
    INNER JOIN 
        MD_ItemMaster ON TR_SalesReturnDetails.ItemID  =  MD_ItemMaster.ID
    INNER JOIN 
        MD_Supplier ON MD_ItemMaster.SupplierID= MD_Supplier.ID
    INNER JOIN 
        MD_Employee ON TR_SalesReturnHeader.CreatedBy = MD_Employee.ID
    WHERE
        MD_Supplier.ID = 3 
        AND MD_ItemMaster.ID = 572
    GROUP BY
        MD_Employee.ID

Result:

enter image description here

I need to stop EmployeeID duplicates


Solution

  • Return the data with a final aggregation:

    select Employee, sum(ChargedQty) ChargedQty, sum(ReturnQty) ReturnQty
    from @WSotable
    group by Employee