SQL Server 2014 Join Doubling Line Items

I can usually find answers to my questions if I search hard enough but I don't know how to word my question well enough to get the results I desire.

如果我足够努力地搜索,我通常可以找到问题的答案,但我不知道如何充分说出我的问题以获得我想要的结果。

The issue I am having is that I am trying to Join my Order Charge Table to my other Query so that I can see if there was a Discount or a Shipping charge applied.

我遇到的问题是我正在尝试将订单费用表加入我的其他查询,以便我可以查看是否应用了折扣或运费。

There are 3 scenarios that can happen per order in the Order Charge Table (Discount AND Shipping charge applied) OR (Discount applied) OR (Shipping applied)

订单费用表中的每个订单可能会发生3种情况(已应用折扣和运费)或(已应用折扣)或(已应用运费)

NULL values are NOT allowed so if there is no discount or shipping for the order, it does not show up in this table.

不允许使用NULL值,因此如果订单没有折扣或运费,则不会显示在此表中。

My query without the order charges applied is:

我没有收到订单费用的查询是:

SELECT  
    [Order].[OrderNumber],
    CASE
       WHEN [ShopifyOrder].[PaymentStatusCode] = '2' THEN 'Paid'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '4' THEN 'Refunded'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '5' THEN 'Voided'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '6' THEN 'Partially Refunded'
    END AS 'PaymentStatus',
    [Store].[StoreName] as 'MarketplaceNames',
    [OrderItem].[SKU],
    [LookupList].[MainSKU], [LookupList].[ProductName],
    [LookupList].[Classification] as 'Classification',
    [LookupList].[Cost],
    ([OrderItem].[Quantity] * [OrderItem].[UnitPrice]) AS 'Sales',
    (([OrderItem].[Quantity] * [LookupList].[Quantity]) * [LookupList].[Cost]) AS 'Total Cost',
    [OrderItem].[Quantity] * [LookupList].[Quantity] AS 'Total Qty'
FROM 
    [SHIPSERVER].[dbo].[Order]
JOIN 
    [SHIPSERVER].[dbo].[ShopifyOrder] ON [Order].[OrderID] = [ShopifyOrder].[OrderID]
JOIN 
    [SHIPSERVER].[dbo].[OrderItem] ON [OrderItem].[OrderID] = [Order].[OrderID]
JOIN 
    [SHIPSERVER].[dbo].[Store] ON [Order].[StoreID] = [Store].[StoreID]
LEFT JOIN 
    [SHIPSERVER].[dbo].[LookupList] ON [OrderItem].[SKU] = [LookupList].[SKU]
WHERE 
    ([Store].[StoreName]= 'Shopify')
    AND ([Order].[OrderDate] BETWEEN '2015-09-01 00:00:00.000' AND '2015-09-30 23:59:59.999')
    AND ([Order].[IsManual] = '0')

I am going to give 1 order as an example in the Results

我将在结果中给出1个订单作为示例

+-------------+---------------+------------------+---------------+---------------+----------------+-------+-------+------------+-----------+
| OrderNumber | PaymentStatus | MarketplaceNames | SKU           | MainSKU       | Classification | Cost  | Sales | Total Cost | Total Qty |
| 7177        | Paid          | Shopify          | 300TLSH SL PL | 300TLSH SL PL | Sheet Set      | 21.00 | 48.99 | 21         | 1         |
+-------------+---------------+------------------+---------------+---------------+----------------+-------+-------+------------+-----------+

Here is my query when I join the OrderCharge table:

当我加入OrderCharge表时,这是我的查询:

SELECT  
    [Order].[OrderNumber],
    CASE  
       WHEN [ShopifyOrder].[PaymentStatusCode] = '2' THEN 'Paid'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '4' THEN 'Refunded'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '5' THEN 'Voided'
       WHEN [ShopifyOrder].[PaymentStatusCode] = '6' THEN 'Partially Refunded'
    END AS 'PaymentStatus',
    [Store].[StoreName] as 'MarketplaceNames',
    [OrderItem].[SKU],
    [LookupList].[MainSKU], 
    [OrderCharge].[Type], [OrderCharge].[Description], [OrderCharge].[Amount],
    [LookupList].[Classification] as 'Classification', [LookupList].[Cost],
    ([OrderItem].[Quantity]* [OrderItem].[UnitPrice]) AS 'Sales',
    (([OrderItem].[Quantity] * [LookupList].[Quantity]) * [LookupList].[Cost]) AS 'Total Cost',
    [OrderItem].[Quantity] * [LookupList].[Quantity] AS 'Total Qty'
FROM 
    [SHIPSERVER].[dbo].[Order]
JOIN 
    [SHIPSERVER].[dbo].[ShopifyOrder] ON [Order].[OrderID] = [ShopifyOrder].[OrderID]
JOIN 
    [SHIPSERVER].[dbo].[OrderItem] ON [OrderItem].[OrderID] = [Order].[OrderID]
JOIN 
    [SHIPSERVER].[dbo].[Store] ON [Order].[StoreID] = [Store].[StoreID]
LEFT JOIN 
    [SHIPSERVER].[dbo].[LookupList] ON [OrderItem].[SKU] = [LookupList].[SKU]
JOIN 
    [SHIPSERVER].[dbo].[OrderCharge] ON [Order].[OrderID] = [OrderCharge].[OrderID]
WHERE 
    ([Store].[StoreName]= 'Shopify')
    AND ([Order].[OrderDate] BETWEEN '2015-09-01 00:00:00.000' AND '2015-09-30 23:59:59.999')
    AND ([Order].[IsManual] = '0')

Again I am going to give the same order as an example in the Results

我将再次给出与结果中的例子相同的顺序

+-------------+---------------+------------------+---------------+---------------+----------+------------------------+--------+----------------+-------+-------+------------+-----------+
| OrderNumber | PaymentStatus | MarketplaceNames | SKU           | MainSKU       | Type     | Description            | Amount | Classification | Cost  | Sales | Total Cost | Total Qty |
| 7177        | Paid          | Shopify          | 300TLSH SL PL | 300TLSH SL PL | DISCOUNT | 15chance               | -7.35  | Sheet Set      | 21.00 | 48.99 | 21         | 1         |
| 7177        | Paid          | Shopify          | 300TLSH SL PL | 300TLSH SL PL | SHIPPING | FREE Standard Shipping | 0.00   | Sheet Set      | 21.00 | 48.99 | 21         | 1         |
+-------------+---------------+------------------+---------------+---------------+----------+------------------------+--------+----------------+-------+-------+------------+-----------+

If I were to export this into an excel file, the Cost, Sales, and Total Qty fields are now all doubled for this order, this becomes an even bigger issue if there are multiple line items in an order.

如果我要将其导出到excel文件中,则此订单的“成本”,“销售”和“总数量”字段现在都加倍,如果订单中有多个订单项,这将成为更大的问题。

I thought a solution would be to make the Discount and Shipping Fields their own columns but all that did was put Discount and Shipping on the same line but all the line items were still doubled.

我认为解决方案是将折扣和运输字段设为自己的列,但所有这些都将折扣和运输放在同一行,但所有订单项仍然加倍。

I have to be over looking something.

我不得不寻找一些东西。

1 个解决方案

#1

SELECT  [Order].[OrderNumber]
        ,CASE   WHEN [ShopifyOrder].[PaymentStatusCode] = '2' THEN 'Paid'
                WHEN [ShopifyOrder].[PaymentStatusCode] = '4' THEN 'Refunded'
                WHEN [ShopifyOrder].[PaymentStatusCode] = '5' THEN 'Voided'
                WHEN [ShopifyOrder].[PaymentStatusCode] = '6' THEN 'Partially Refunded'
                END AS 'PaymentStatus'
        ,[Store].[StoreName] as 'MarketplaceNames'
        ,[OrderItem].[SKU]
        ,[LookupList].[MainSKU]
        ,[ShippingCharge].[Description] as shippingDescription
        ,[ShippingCharge].[Amount] as shippingAmount
        ,[DiscountCharge].[Description] as discountDescription
        ,[DiscountCharge].[Amount] as discountAmount
        ,[LookupList].[Classification] as 'Classification'
        ,[LookupList].[Cost]
        ,([OrderItem].[Quantity]* [OrderItem].[UnitPrice]) AS 'Sales'
        ,(([OrderItem].[Quantity] * [LookupList].[Quantity]) * [LookupList].[Cost]) AS 'Total Cost'
        ,[OrderItem].[Quantity] * [LookupList].[Quantity] AS 'Total Qty'
FROM [SHIPSERVER].[dbo].[Order]
JOIN [SHIPSERVER].[dbo].[ShopifyOrder]
ON [Order].[OrderID]=[ShopifyOrder].[OrderID]
JOIN [SHIPSERVER].[dbo].[OrderItem]
ON [OrderItem].[OrderID]=[Order].[OrderID]
JOIN [SHIPSERVER].[dbo].[Store]
ON [Order].[StoreID]=[Store].[StoreID]
LEFT JOIN [SHIPSERVER].[dbo].[LookupList]
ON [OrderItem].[SKU]=[LookupList].[SKU]
LEFT JOIN [SHIPSERVER].[dbo].[OrderCharge] [ShippingCharge]
ON [Order].[OrderID]=[ShippingCharge].[OrderID] AND [ShippingCharge].[Type] = 'SHIPPING'
LEFT JOIN [SHIPSERVER].[dbo].[OrderCharge] [DiscountCharge]
ON [Order].[OrderID]=[DiscountCharge].[OrderID] AND [DiscountCharge].[Type] = 'DISCOUNT'
WHERE ([Store].[StoreName]= 'Shopify')
AND ([Order].[OrderDate] BETWEEN '2015-09-01 00:00:00.000' AND '2015-09-30 23:59:59.999')
AND ([Order].[IsManual] = '0')

The differences are :

不同之处是:

        ,[ShippingCharge].[Description] as shippingDescription
        ,[ShippingCharge].[Amount] as shippingAmount
        ,[DiscountCharge].[Description] as discountDescription
        ,[DiscountCharge].[Amount] as discountAmount

and

LEFT JOIN [SHIPSERVER].[dbo].[OrderCharge] [ShippingCharge]
ON [Order].[OrderID]=[ShippingCharge].[OrderID] AND [ShippingCharge].[Type] = 'SHIPPING'
LEFT JOIN [SHIPSERVER].[dbo].[OrderCharge] [DiscountCharge]
ON [Order].[OrderID]=[DiscountCharge].[OrderID] AND [DiscountCharge].[Type] = 'DISCOUNT'

Basically, what I did is I left joined on OrderCharge twice, once for Discount and once for Shipping, with a different alias each time. This means that you're potentially linked to a discount row and potentially linked to a shipping row, and from there getting the data is incredibly easy.

基本上,我做的是我两次加入OrderCharge,一次用于折扣,一次用于装运,每次使用不同的别名。这意味着您可能链接到折扣行并可能链接到出货行,从那里获取数据非常容易。

As @thab pointed out in comments though, there are glaring issues with this. First of all, having more than one Shipping or Discount entries will duplicate rows, at which point you would have to use a sum on the [Amount] (and probably an XML concatenation on the description). This also means that the query must be altered whenever a new Type of ChargeOrder appears.

正如@thab在评论中指出的那样,这有明显的问题。首先,拥有多个送货或折扣条目将重复行,此时您必须使用[金额]上的总和(可能是描述上的XML连接)。这也意味着只要出现新的ChargeOrder类型,就必须更改查询。

The idea solution would be using Pivot, but I haven't dabbled with that yet so I can't help you with that one. I do believe that Pivot tables run slower though (well, at least dynamic ones do), so as long as your problem doesn't change you should be fine.

想法解决方案将使用Pivot,但我还没有涉足到那个,所以我无法帮助你。我确实认为Pivot表运行速度较慢(好吧,至少是动态表),所以只要你的问题没有改变就应该没问题。