日期的问题，由于在DATEDIFF函数

Question

之间我有以下查询：

SELECT 
    datediff(d, 0, sauda_date), 
    Scrip_Code, 
    SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty ELSE 0 END) AS BuyQty, 
    SUM(CASE WHEN Buy_sell = 1 THEN Market_Rate ELSE 0 END) AS BuyRate, 
    SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty * Market_Rate ELSE 0 END) AS BuyAmount, 
    SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty ELSE 0 END) AS SellQty, 
    SUM(CASE WHEN Buy_sell = 2 THEN Market_Rate ELSE 0 END) AS SellRate, 
    (CASE WHEN SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty ELSE 0 END) > 
      SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty ELSE 0 END) THEN 'BF' 
     ELSE 'BT' END) as TradeType, 
    SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty * Market_Rate ELSE 0 END) AS SellAmount, 
    SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty ELSE 0 END) - 
    SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty ELSE 0 END) as NETQTY, 
    SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty * Market_Rate ELSE 0 END) - 
    SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty * Market_Rate ELSE 0 END)as NetAmt, 
    SUM(CASE WHEN Buy_sell = 2 THEN Trade_Qty * Market_Rate ELSE 0 END) - 
    SUM(CASE WHEN Buy_sell = 1 THEN Trade_Qty * Market_Rate ELSE 0 END) as PNLAmt 
FROM tradeFile 
where Inst_Type = 'FUTIDX' 
    OR Inst_Type='FUTSTK' 
    and Sauda_Date = convert(datetime,'1 Mar 2013') 
group by Scrip_Code, ExpiryDate, datediff(d,0,sauda_date) 

结果：

该查询采取Buy_Qty的总和，Sell_Qty datewise [附。裁判这个任务：not able to get individual date in query result

但是，正如我们所看到DATEDIFF（d，0，sauda_date）列（即第一列）给了我一些结果，但我想在此结果中添加sauda_date作为日期。

我应该在这个查询中做出什么改变。

我尝试了通过添加查询直接sauda_date但它给我的错误。

请指导我。

Answer 1

如果您在查询中使用GROUP BY然后（在T-SQL的情况下）在查询的字段的一部分，你只能使用领域从GROUP BY或聚合函数（SUM，COUNT，....）。所以你不能只添加sauda_date。你应该添加它与聚合函数。例如MIN(sauda_date)。或者您可以将其添加到GROUP BY部分。

Answer 2

不知道您的结果要求..

简单地增加，如果你在组includ它的列表中，我已经尝试过belwo及其工作sauda_date在选择列表中不应该给错误。

Select datediff(d,0,f.Date), 
     sum(f.price), 
     f.OrderID, 
     f.Date 
from test f 
group by f.Date,OrderID,datediff(d,0,f.Date) 

否则 您可以使用自连接得到的只有赛于达更新你的键列