我有一个SQL查询,我在其中检索一些数据,JOINS
其中还有一个DateTime
字段Delivery
,但是在检索时,我将其转换为格式为String的字符串,MMM YYYY
但最后我想对整个列表进行排序,Delivery
但是当我将该列转换为字符串时, ,我不确定如何按原始DateTime
字段对结果进行排序Delivery
。我的查询如下
WITH deliveryProducts AS
(
SELECT DISTINCT FORMAT(a.Delivery,'MMMM yyyy') AS Delivery, b.Product
FROM DEALS as a, PRODUCTS as b Where a.TradeDate<=@tradeEndDate
)
, deliveryActuals AS
(
SELECT
FORMAT(a.Delivery,'MMMM yyyy') AS Delivery,
b.Product,COUNT(a.Id) AS Trades,
((6.2898*SUM(a.Volume ))/DAY(EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0,a.Delivery), 0))))*0.001 AS BBLperDay,
SUM(a.Volume) AS M3,
SUM(a.Volume*a.Price)/Sum(a.Volume) AS WeightedAverage
FROM Deals AS a right outer join Products AS b
ON a.Product=b.Product AND
a.TradeDate<=@tradeEndDate
GROUP BY b.Product,
DATEADD(MONTH, DATEDIFF(MONTH, 0,a.Delivery),0), FORMAT(a.Delivery,'MMMM yyyy')
)
SELECT
dp.Delivery, dp.Product, trades, BBLperDay, M3, WeightedAverage
FROM
deliveryProducts dp
LEFT JOIN deliveryActuals da
on dp.Delivery = da.Delivery
and dp.product = da.Product
ORDER BY dp.Delivery,dp.Product
上述查询给我所需要的数据,但通过种种Delivery
作为string
。
我可以知道解决此问题的更好方法吗?
ORDER BY cast(dp.Delivery as date), dp.Product
SQL Server可以自行处理将字符串'MMMM yyyy'或'MMM yyyy'强制转换为日期或日期时间。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句