时间戳

首页 > 解决方案 > SQL Self Join计算发票存储在同一张表中时的运费百分比

sql - SQL Self Join计算发票存储在同一张表中时的运费百分比

问题描述

我需要计算公司每批进货的运费百分比值。运费和货物发票存放在同一张表中，VSI表示货物发票，VBL表示运费发票，它们通过a连接在一起工作成本。我试图自行加入表集，但最终总和总是乘以匹配记录的数量。任何帮助，将不胜感激。

表

工作成本

JobCostID       JobCostDescription   JobCostNumber
4910            ITS-1005104          JBC-1004880
4911            ITS-1005105          JBC-1004881

采购发票

PIID          PINumber    ExchangeRate   VendorId     
1             VSI-1       1              1
2             VSI-2       1              2
3             VBL-1       1              3

采购发票项目

PIItemID      PIID        Item
1             1           ProductA
2             1           ProductB
3             2           ProductA
4             2           ProductB
5             3           Fuel
6             3           Handling

PurchaseInvoiceItemDetail

PIItemDetailID    QtyShipped     Cost      PIItemID    JobCostID
1                 2              2500      1           4910
2                 2              2500      2           4910  
3                 2              2500      3           4911
4                 2              2500      4           4911
5                 1              25        5           4910
6                 1              75        6           4910

小贩

VendorId    VendorCode    VendorName
1           VEN1          Vendor1
2           VEN2          Vendor2
3           Freight1      Agent1

期望的结果

JobCostID JobCostDescription VendorCode VendorName FreightAgentCode FreightAgentName InvoiceTotal FreightTotal FreightPercentage
4910      ITS-1005104        VEN1       Vendor1    Freight1           Agent1         10000        100          1%     
4920      ITS-1005105        VEN2       Vendor2                                      10000   



   SELECT tblJobCost1.JobCostID, tblJobCost1.JobCostDescription, tblVendor1.VendorCode, tblVendor1.VendorName, tblVendor2.VendorCode AS 'FreightAgentCode', tblVendor2.VendorName AS 'FreightAgentName',  
SUM(PurchaseInvoiceItemDetail1.QtyShipped*PurchaseInvoiceItemDetail1.Cost*PurchaseInvoice1.ExchangeRate) AS 'InvoiceTotal',
SUM(PurchaseInvoiceItemDetail2.QtyShipped*PurchaseInvoiceItemDetail2.Cost*PurchaseInvoice2.ExchangeRate) AS 'FreightTotal', 
SUM(PurchaseInvoiceItemDetail2.QtyShipped*PurchaseInvoiceItemDetail2.Cost*PurchaseInvoice2.ExchangeRate)/SUM(PurchaseInvoiceItemDetail1.QtyShipped*PurchaseInvoiceItemDetail1.Cost*PurchaseInvoice1.ExchangeRate)*100 AS 'FreightPercentage'
FROM tblJobCost AS tblJobCost1 
LEFT JOIN tblJobCost AS tblJobCost2 ON tblJobCost1.JobCostID  = tblJobCost2.JobCostID
LEFT JOIN tblPurchaseInvoiceItemDetail AS tblPurchaseInvoiceItemDetail1 ON tblJobCost1.JobCostID = tblPurchaseInvoiceItemDetail1.JobCostID
LEFT JOIN tblPurchaseInvoiceItemDetail AS tblPurchaseInvoiceItemDetail2 ON tblJobCost2.JobCostID = tblPurchaseInvoiceItemDetail2.JobCostID
LEFT JOIN tblPurchaseInvoiceItem AS tblPurchaseInvoiceItem1 ON tblPurchaseInvoiceItem1.PIItemID = tblPurchaseInvoiceItemDetail1.PIItemID
LEFT JOIN tblPurchaseInvoiceItem AS tblPurchaseInvoiceItem2 ON tblPurchaseInvoiceItem2.PIItemID = tblPurchaseInvoiceItemDetail2.PIItemID
LEFT JOIN tblPurchaseInvoice AS tblPurchaseInvoice1 ON tblPurchaseInvoiceItem1.PIID = tblPurchaseInvoice1.PIID
LEFT JOIN tblPurchaseInvoice AS tblPurchaseInvoice2 ON tblPurchaseInvoiceItem2.PIID = tblPurchaseInvoice2.PIID
LEFT JOIN tblVendor AS tblVendor1 ON tblVendor1.VendorId = tblPurchaseInvoice1.VendorID
LEFT JOIN tblVendor AS tblVendor2 ON tblVendor2.VendorId = tblPurchaseInvoice2.VendorID
WHERE tblPurchaseInvoice1.PINumber LIKE 'VSI%' AND tblPurchaseInvoice2.PINumber LIKE 'VBL%'
GROUP BY tblJobCost1.JobCostID, tblJobCost1.JobCostDescription, tblVendor1.VendorCode, tblVendor1.VendorName, tblVendor2.VendorCode, tblVendor2.VendorName

标签： sqlsql-serverdatabasesum

解决方案

那么你错过了一个严重的条件：

...
 LEFT JOIN tblPurchaseInvoiceItemDetail AS tblPurchaseInvoiceItemDetail2 
        ON tblJobCost2.JobCostID = tblPurchaseInvoiceItemDetail2.JobCostID
        AND tblPurchaseInvoiceItemDetail1.JobCostID = tblPurchaseInvoiceItemDetail2.JobCostID
...

推荐阅读