sql - 高级 SQL：日期范围内的销售额总和

问题描述

我正在尝试获取某个日期范围内特定产品的销售额总和。不幸的是，两个日期结果的销售额总和是相同的。右图 2019/01/01 的总销售额为 5000，第二天 2019/01/02 的总销售额为 3000。结果显示这两天的总销售额为 8000。这是错误的。任何专家都可以帮助改进这个查询吗？

Declare @BusinessDate datetime ='2019-01-01'
Declare @end datetime ='2019-01-02'
DECLARE @StoreId int = 100
SELECT  [Terminals].[Id] AS [TerminalId],
        [Terminals].[StoreId],
        [EOD].[Id] AS [EODId],
        SUM([Sales].[SalesAmount]) AS [SalesAmount],
        [EOD].BusinessDate
FROM    [CEPP]..[Stores] WITH (NOLOCK)
        INNER JOIN [CEPP]..[Terminals] WITH (NOLOCK)
 ON     [Stores].[Id] = [Terminals].[StoreId]
        AND [Terminals].[MWorkFlowStatusId] = 2
        AND ([Terminals].[MStatusId] = 1
        OR ([Terminals].[MStatusId] = 0
        AND [Terminals].[SuspendedDate] > @BusinessDate ))
        LEFT JOIN [EndOfDays] AS [EOD] WITH (NOLOCK)
 ON     [Terminals].[Id] = [EOD].[TerminalId]
        AND [EOD].[BusinessDate] >= @BusinessDate and [EOD].[BusinessDate]<=@end
        CROSS APPLY (
            SELECT SUM([Products].[Deno]) AS [SalesAmount]
            FROM [SalesOrders] AS [SO] WITH (NOLOCK)
                INNER JOIN [SalesTransactions] AS [ST] WITH (NOLOCK)
            ON  [SO].[Id] = [ST].[SalesOrderId]
                LEFT JOIN [VoidOrders] AS [VO] WITH (NOLOCK)
                INNER JOIN [VoidTransactions] AS [VT] WITH (NOLOCK)
            ON  [VO].[Id] = [VT].[VoidOrderId]
            ON  [SO].[DealerId] = [VO].[DealerId]
                AND [SO].[StoreId] = [VO].[StoreId]
                AND [SO].[TerminalId] = [VO].[TerminalId]
                AND [ST].[ProductId] = [VT].[ProductId]
                AND [ST].[SerialNo] = [VT].[SerialNo]
                AND [ST].[BusinessDate] = [VT].[BusinessDate]
                AND [VT].[MVoidTypeId] = 1
                INNER JOIN [CEPP].[dbo].[Products] WITH (NOLOCK)
            ON  [ST].[ProductId] = [Products].[Id]
            WHERE [EOD].[Id] IS NOT NULL
                AND [VT].[SerialNo] IS NULL
                AND [SO].[TerminalId] = [Terminals].[Id]
                AND [ST].[BusinessDate] >= @BusinessDate and [ST].[BusinessDate] <= @end
        ) AS [Sales]
WHERE  [Stores].[DealerId] = 1 AND (@StoreId IS NULL OR [Terminals].[StoreId] = @StoreId)
GROUP BY [Terminals].[Id], [Terminals].[StoreId], [EOD].[Id], [Stores].[Code], [Terminals].[Code],[EOD].BusinessDate
ORDER BY ISNULL([EOD].[Id], 0), [Stores].[Code], [Terminals].[Code]

我得到的意外结果是：

TerminalId  StoreId   EODId   SalesAmount   BusinessDate
  21598      100     5427531    8000.00    2019-01-01 00:00:00.000
  21598      100     5427532    8000.00    2019-01-02 00:00:00.000

结果应该是这样的：

TerminalId  StoreId   EODId   SalesAmount   BusinessDate
  21598      100     5427531    5000.00    2019-01-01 00:00:00.000
  21598      100     5427532    3000.00    2019-01-02 00:00:00.000

标签： sqlsql-servertsqlstored-procedures

sql - 高级 SQL：日期范围内的销售额总和

问题描述

解决方案

推荐阅读