DemiB DemiB - 1 month ago 9
SQL Question

The column 'IncidentNumber' was specified multiple times for 'inv'

I am having issue finding why I am having this Error.

Here is the statement: `SELECT Incident.salesrep AS Salesman, Incident.Incidentnumber AS Incidentent, bc.Name AS Customer, Incident.Address3 AS [Ship to Customer], Incident.UserText6 AS [Customer Type],
inv.InvoiceNumber AS Invoice, sopHdr.DOCDATE AS [Document Date], sopHdr.GLPOSTDT AS [GL Post Date],
CASE WHEN sopHdr.DOCAMNT < 0 THEN (sopHdr.DOCAMNT) WHEN (sopHdr.SOPTYPE = 4 AND sopHdr.DOCAMNT > 0) THEN (sopHdr.DOCAMNT * - 1)
ELSE CASE WHEN Paid.Paid IS NULL THEN 0.00 ELSE Paid.Paid END END AS [Amount Received], CASE WHEN sopHdr.SOPTYPE = 4 THEN (sopHdr.DOCAMNT * - 1)
ELSE sopHdr.DOCAMNT END AS [Sales Amount], CASE WHEN sopHdr.SOPTYPE = 4 THEN (inv.CostTotal * - 1) ELSE inv.CostTotal END AS [Ext Cost],
CASE WHEN sopHdr.SOPTYPE = 4 THEN (inv.TaxTotal * - 1) ELSE inv.TaxTotal END AS [Tax Amount],
CASE WHEN Incident.UserText8 = 'AVATAX' THEN 'S' WHEN Incident.UserText8 = 'COGS' THEN 'U' ELSE 'X' END AS [Tax Type],
CASE WHEN sopHdr.SOPTYPE = 4 THEN ((sopHdr.DOCAMNT - inv.TaxTotal) * - 1) ELSE (sopHdr.DOCAMNT - inv.TaxTotal) END AS [Net Sales],
CASE WHEN sopHdr.SOPTYPE = 4 THEN ((sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal) * - 1) ELSE (sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal)
END AS [Gross Profit],
--CASE WHEN (Incident.UserText1 IS NULL OR
-- Incident.UserText1 = '') THEN 0.00 ELSE CASE WHEN sopHdr.SOPTYPE = 4 THEN (((sopHdr.DOCAMNT - inv.TaxTotal) - inv.CostTotal) * - 1) * (Incident.UserText1 / 100.0)
-- ELSE ((sopHdr.DOCAMNT - inv.TaxTotal) - inv.CostTotal) * (Incident.UserText1 / 100.0) END END AS [Accrued Commission],
--CASE WHEN (Incident.UserText1 IS NULL OR
-- Incident.UserText1 = '') THEN '0.00' ELSE Incident.UserText1 END AS [Commission %], Incident.UserText21 AS [Not Commissionable], Incident.UserText18 AS [Date 1st Comm PD],
CASE WHEN (sopHdr.SUBTOTAL IS NULL OR
sopHdr.SUBTOTAL = 0) THEN 0.00
ELSE
CASE WHEN sopHdr.SOPTYPE = 4 THEN ((sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal) / (sopHdr.SUBTOTAL - inv.TaxTotal)) * - 100
ELSE (((sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal) / (sopHdr.SUBTOTAL - inv.TaxTotal)) * 100)
END END AS [Sale Gross Profit %],
CASE WHEN (sopHdr.DOCAMNT < 0) OR
(sopHdr.SOPTYPE = 4 AND sopHdr.DOCAMNT > 0) THEN 100.00
ELSE
CASE WHEN (Paid.Paid IS NULL OR Paid.Paid = 0) OR (sopHdr.DOCAMNT = 0 OR sopHdr.DOCAMNT IS NULL) THEN 0.00
ELSE (Paid.Paid / sopHdr.DOCAMNT) * 100 END END AS [Paid %], Incident.UserText25 AS Direct, Incident.UserText26 AS [Split w/Rep],
Incident.UserText27 AS [% of Job Rep 1], Incident.UserText28 AS [% of Job Rep 2], Incident.UserText23 AS [Date 2nd Comm PD], inv.InvoiceTotal AS [Invoiced Total]

FROM (SELECT Record, BatchNumber, InvoiceNumber, InvoiceDate, Status, InvoiceType, InvoiceSource, ApplyTo, IncidentNumber, IncidentNumber, TaskID,
BillToCompanyID, BillToCompanyCode, BillToCompanyName, BillToAddressID, BillToAddressCode, BillToAddress1, BillToAddress2, BillToAddress3,
BillToAddress4, BillToCity, BillToState, BillToZip, BillToCountry, PONumber, BillingContactID, BillingFirstName, BillingLastName, BillingPhone,
BillingExtension, BillingEmail, IntegrationValue, InternalNotes, ExternalNotes, CreateDate, ModifyDate, EnteredByUser, ModifiedByUser, ContractNumber,
Division, IsEmailed, UserNotes, UserNotes2, DocumentType, PaymentTerms, TransferToBatchNumber, AdjustmentType, IsPaid, UserText1, UserText2,
UserText3, UserText4, UserText5, UserText6, UserText7, UserText8, UserText9, UserText10, UserDate1, UserDate2, UserDate3, UserNotes3,
(SELECT SUM(SalesAmount) AS Expr1
FROM AlertCRM.dbo.InvoiceDetail
WHERE (InvoiceRecord = AlertCRM.dbo.Invoice.Record)) AS SubTotal,
(SELECT SUM(SalesTax) AS Expr1
FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_3
WHERE (InvoiceRecord = AlertCRM.dbo.Invoice.Record)) AS TaxTotal,
(SELECT ISNULL(SUM(SalesAmount), 0) + ISNULL(SUM(SalesTax), 0) AS Expr1
FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_2
WHERE (InvoiceRecord = AlertCRM.dbo.Invoice.Record)) AS InvoiceTotal,
(SELECT SUM(CostAmount) AS Expr1
FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_1
WHERE (InvoiceRecord = AlertCRM.dbo.Invoice.Record)) AS CostTotal
FROM AlertCRM.dbo.Invoice) AS inv INNER JOIN
AlertCRM.dbo.Incident AS Incident ON Incident.IncidentNumber = inv.IncidentNumber LEFT OUTER JOIN
AlertCRM.dbo.Company AS bc ON bc.CompanyID = Incident.BillingCompanyID LEFT OUTER JOIN
dbo.SOP30200 AS sopHdr ON sopHdr.SOPNUMBE = inv.InvoiceNumber LEFT OUTER JOIN
(SELECT APTODCNM AS Invoice, SUM(Paid) AS Paid
FROM (SELECT APTODCNM, SUM(ActualApplyToAmount) AS Paid
FROM dbo.RM20201
GROUP BY APTODCNM
UNION
SELECT APTODCNM, SUM(ActualApplyToAmount) AS Paid
FROM dbo.RM30201
GROUP BY APTODCNM) AS p
GROUP BY APTODCNM) AS Paid ON Paid.Invoice = sopHdr.SOPNUMBE`

Please advise.

Answer

try this

SELECT Incident.salesrep AS Salesman,
       Incident.Incidentnumber AS Incidentent,
       bc.Name AS Customer,
       Incident.Address3 AS [Ship to Customer],
       Incident.UserText6 AS [Customer Type],
       inv.InvoiceNumber AS Invoice,
       sopHdr.DOCDATE AS [Document Date],
       sopHdr.GLPOSTDT AS [GL Post Date],
       CASE
           WHEN sopHdr.DOCAMNT < 0
           THEN(sopHdr.DOCAMNT)
           WHEN(sopHdr.SOPTYPE = 4
                AND sopHdr.DOCAMNT > 0)
           THEN(sopHdr.DOCAMNT * -1)
           ELSE CASE
                    WHEN Paid.Paid IS NULL
                    THEN 0.00
                    ELSE Paid.Paid
                END
       END AS [Amount Received],
       CASE
           WHEN sopHdr.SOPTYPE = 4
           THEN(sopHdr.DOCAMNT * -1)
           ELSE sopHdr.DOCAMNT
       END AS [Sales Amount],
       CASE
           WHEN sopHdr.SOPTYPE = 4
           THEN(inv.CostTotal * -1)
           ELSE inv.CostTotal
       END AS [Ext Cost],
       CASE
           WHEN sopHdr.SOPTYPE = 4
           THEN(inv.TaxTotal * -1)
           ELSE inv.TaxTotal
       END AS [Tax Amount],
       CASE
           WHEN Incident.UserText8 = 'AVATAX'
           THEN 'S'
           WHEN Incident.UserText8 = 'COGS'
           THEN 'U'
           ELSE 'X'
       END AS [Tax Type],
       CASE
           WHEN sopHdr.SOPTYPE = 4
           THEN((sopHdr.DOCAMNT - inv.TaxTotal) * -1)
           ELSE(sopHdr.DOCAMNT - inv.TaxTotal)
       END AS [Net Sales],
       CASE
           WHEN sopHdr.SOPTYPE = 4
           THEN((sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal) * -1)
           ELSE(sopHdr.SUBTOTAL - inv.TaxTotal - inv.CostTotal)
       END AS [Gross Profit]
FROM
(
    SELECT Record,
           BatchNumber,
           InvoiceNumber,
           InvoiceDate,
           Status,
           InvoiceType,
           InvoiceSource,
           ApplyTo,
           IncidentNumber,    
           TaskID,
           BillToCompanyID,
           BillToCompanyCode,
           BillToCompanyName,
           BillToAddressID,
           BillToAddressCode,
           BillToAddress1,
           BillToAddress2,
           BillToAddress3,
           BillToAddress4,
           BillToCity,
           BillToState,
           BillToZip,
           BillToCountry,
           PONumber,
           BillingContactID,
           BillingFirstName,
           BillingLastName,
           BillingPhone,
           BillingExtension,
           BillingEmail,
           IntegrationValue,
           InternalNotes,
           ExternalNotes,
           CreateDate,
           ModifyDate,
           EnteredByUser,
           ModifiedByUser,
           ContractNumber,
           Division,
           IsEmailed,
           UserNotes,
           UserNotes2,
           DocumentType,
           PaymentTerms,
           TransferToBatchNumber,
           AdjustmentType,
           IsPaid,
           UserText1,
           UserText2,
           UserText3,
           UserText4,
           UserText5,
           UserText6,
           UserText7,
           UserText8,
           UserText9,
           UserText10,
           UserDate1,
           UserDate2,
           UserDate3,
           UserNotes3,
    (
        SELECT SUM(SalesAmount) AS Expr1
        FROM AlertCRM.dbo.InvoiceDetail
        WHERE(InvoiceRecord = AlertCRM.dbo.Invoice.Record)
    ) AS SubTotal,
    (
        SELECT SUM(SalesTax) AS Expr1
        FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_3
        WHERE(InvoiceRecord = AlertCRM.dbo.Invoice.Record)
    ) AS TaxTotal,
    (
        SELECT ISNULL(SUM(SalesAmount), 0) + ISNULL(SUM(SalesTax), 0) AS Expr1
        FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_2
        WHERE(InvoiceRecord = AlertCRM.dbo.Invoice.Record)
    ) AS InvoiceTotal,
    (
        SELECT SUM(CostAmount) AS Expr1
        FROM AlertCRM.dbo.InvoiceDetail AS InvoiceDetail_1
        WHERE(InvoiceRecord = AlertCRM.dbo.Invoice.Record)
    ) AS CostTotal
    FROM AlertCRM.dbo.Invoice
) AS inv
INNER JOIN AlertCRM.dbo.Incident AS Incident ON Incident.IncidentNumber = inv.IncidentNumber
LEFT OUTER JOIN AlertCRM.dbo.Company AS bc ON bc.CompanyID = Incident.BillingCompanyID
LEFT OUTER JOIN dbo.SOP30200 AS sopHdr ON sopHdr.SOPNUMBE = inv.InvoiceNumber
LEFT OUTER JOIN
(
    SELECT APTODCNM AS Invoice,
           SUM(Paid) AS Paid
    FROM
    (
        SELECT APTODCNM,
               SUM(ActualApplyToAmount) AS Paid
        FROM dbo.RM20201
        GROUP BY APTODCNM
        UNION
        SELECT APTODCNM,
               SUM(ActualApplyToAmount) AS Paid
        FROM dbo.RM30201
        GROUP BY APTODCNM
    ) AS p
    GROUP BY APTODCNM
) AS Paid ON Paid.Invoice = sopHdr.SOPNUMBE;