无法在查询结果中获得单个日期 [英] not able to get individual date in query result
本文介绍了无法在查询结果中获得单个日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的查询是:
SELECT 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 between convert(datetime,'1 Mar 2013')
and convert(datetime,'4 Mar 2013') group by Scrip_Code,ExpiryDate,sauda_date
通过此查询,我得到的结果为:
正如我们在2013-03-11所看到的,有很多记录. 我想将它们的总和用于BuyQty,SellQty等列. 即按日期的总和记录为
As we can see at 2013-03-11 there are multple records. I wanted to have their sum for BuyQty,SellQty,etc columns. i.e. Datewise sum record as
sauda_date scrip_code BuyQty
2013-03-11 BANKNIFTY 150 [sum for this date]
我已经尝试过:
SELECT Scrip_Code,sauda_date,
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 between convert(datetime,'1 Mar 2013')
and convert(datetime,'10 Mar 2013')
group by Scrip_Code,convert(datetime,sauda_date)
order by convert(varchar(11),sauda_date)
但是我得到的结果是:
请帮助我.
推荐答案
将convert(datetime,sauda_date)
更改为datediff(d,0,sauda_date)
并按同一列分组
Change convert(datetime,sauda_date)
to datediff(d,0,sauda_date)
and group by the same column
即
SELECT datediff(d,0,sauda_date) sauda_date,Scrip_Code,...
group by Scrip_Code,ExpiryDate,datediff(d,0,sauda_date);
这篇关于无法在查询结果中获得单个日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文