SQL Server 2005 Express :分组依据

Question

分销商有各种销售产品的渠道。产品是基于类别的。

网点可以从分销商和其他网点接收产品(转接)。日常期初余额、入库数量、调入数量、调出数量、销售数量、期初余额均维护在txnTransactions表中。表的结构如下：

Create table txnTransactions
(
  dDate datetime,
  iCategoryID int (FK),
  iProduct ID int (FK),
  iOutletID int (FK),
  iOpeningBalance int,
  iReceipt int,
  iTranIN int,
  iTranOut int,
  iSale int,
  iClosingBalance int
)

应向总代理商显示指定期间内总交易量(产品方面)的报告(例如2013年1月3日至2013年3月31日)，用于所有插座(我使用0作为值来检查是所有插座还是特定插座)。

在此报表中，所有产品的期初余额将仅在开始日(例如1/03/2013)，然后显示所有其他字段(Product-wise)的合计(sum)。

在临时表的帮助下，我也实现了同样的效果。但是查询需要很长时间，这会导致超时。如果我使用简单的group by，我需要使用聚合函数来打开余额，这会导致错误的数据。

Select 
   sum(iOpeningBalance), sum(iReceipt), sum(TranIN), sum(TranOut), sum(Sale) 
from 
   txnTransactions 
where 
   datediff(d, dDate, @startDate) <= 0 
   and datediff(d, dDate, @endDate) >= 0
group by 
   iProductID

不想使用sum(iOpeningBalance)...

示例输出如下：

ProductID OpeningBalance Receipts TranIN TranOut Sale Total/Balance
1         10             100      40     50      50   50
2         35             165      50     100     50   100
3         3              147      10     60      10   90

期初余额为startdate，所有其他字段为startdate & enddate提供的持续时间之和(程序的输入参数)。

Total/Balance is: openingbalance + sum(receipts) + sum(TranIn) - sum(TranOut) - sum(Sale)

需要帮助来优化查询。提前谢谢。

Answer 1

如果日期是每月的第一天或不是，则可以使用带有条件的CASE表达式：dDate = DATEADD(month, DATEDIFF(month, 0, dDate), 0)

SELECT
  SUM(CASE 
        WHEN CAST(dDate AS DATE) = DATEADD(month, 
                                           DATEDIFF(month, 0, dDate), 
                                           0) THEN iOpeningBalance
        ELSE 0 END) AS iOpeningBalance, 
  SUM(iReceipt), 
  SUM(TranIN), 
  SUM(TranOut), 
  SUM(Sale) 
FROM txnTransactions 
WHERE DATEDIFF(d, dDate, @startDate) <= 0 
  AND DATEDIFF(d, dDate, @endDate) >= 0
GROUP BY iProductID;

编辑

试试这个：

WITH CTE
AS
(
  SELECT
    ProductID,
    SUM(ReceiptS) TotalReceiptS, 
    SUM(TranIN) TotalTranIN, 
    SUM(TranOut) TotalTranOut, 
    SUM(Sale) TotalSale
  FROM txnTransactions 
  GROUP BY ProductID
)
SELECT
  c.*,
  [Total/Balance] = (SELECT TOP 1 OpeningBalance
                     FROM txnTransactions AS t
                     WHERE t.ProductID = c.ProductID
                     AND CAST(t.dDate AS DATE) = DATEADD(month,
                                                         DATEDIFF(month, 0, t.dDate), 
                                                         0)) + 

                     TotalReceiptS + 
                     TotalTranIN - 
                     TotalTranOut - 
                     TotalSale  
FROM CTE AS c;

SQL Fiddle Demo