在SQL表中的日期字段中将值除以一个月中的天数 [英] Dividing a value by number of days in a month in a date field in SQL table
问题描述
我正在处理一个SQL查询,它根据日期列中的值对SQL表中的数据执行一些算术除法。
要详细说明我有以下SQL表格交易
id日期类型数量价格
3 2014-11-04 Sweet 2500 23
9 2014-12-04 Sweet 5000 30
10 2014-12-04 Midale 2500 25.4
11 2014-11-04 Sweet 5000 45
现在,我想对按类型和日期分组的数量列进行一些算术运算。
我使用以下查询
SELECT
类型,
COUNT(Id)AS交易,
SUM(数量)AS M3,
ROUND((6.2898 * SUM(数量))/ 31,4)AS BBLperDay,
CAST (日期作为日期)AS TradeMonth,
ROUND(SUM(Quantity * Price)/ Sum(Quantity),4)AS WeightedAverage
FROM Deals
GROUP BY
类型,CAST日期作为日期)
上述查询返回
类型交易M3 BBLperDay TradeMonth
甜蜜2 7500 1521.7258 2014-11-04
Midale 1 2500 507.2419 2014-12-04
甜蜜1 5000 1014.4839 2014-12-04
上面的结果是正确的,但我硬编码的天数为31表达式中的日期
6.2898 * SUM(数量)/ 31 AS BBLperDay
/ pre>
有没有办法我可以做一个值从Date列取得基于th一个月。如果该月份是日期列中的十二月,则值将自动为31,因为十二月份的天数为31。
解决方案找不到一天内的日子
选择DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM, getDate()),0)))
或者如果您使用
SQL SERVER 2012 +
选择DAY(EOMONTH(getdate()))
更改您的查询。
类型
COUNT(Id)AS交易,
SUM(数量)AS M3,
ROUND((6.2898 * SUM(Quantity))/ DAY(DATEADD (DD,-1,DATEADD(MM,DATEDIFF(MM,-1,TradeMonth),0))),4)AS BBLperDay,
CAST(日期作为日期)AS TradeMonth,
ROUND (数量*价格)/总计(数量),4)AS加权平均
从交易
GROUP BY
类型,CAST(日期作为日期),TradeMonth
I am working on an SQL query which performs some arithmetic division on the data in a SQL Table based on the value in a date column.
To elaborate I have the following SQL table Deals
id Date Type Quantity Price 3 2014-11-04 Sweet 2500 23 9 2014-12-04 Sweet 5000 30 10 2014-12-04 Midale 2500 25.4 11 2014-11-04 Sweet 5000 45
Now, I want to some arithmetic operations on Quantity column grouped by Type and Date.
I used the following query
SELECT Type, COUNT(Id) AS Trades, SUM(Quantity ) AS M3, ROUND((6.2898*SUM(Quantity ))/31,4) AS BBLperDay, CAST(Date as date) AS TradeMonth, ROUND(SUM(Quantity*Price)/Sum(Quantity),4) AS WeightedAverage FROM Deals GROUP BY Type,CAST(Date as date)
The above query returns
Type Trades M3 BBLperDay TradeMonth Sweet 2 7500 1521.7258 2014-11-04 Midale 1 2500 507.2419 2014-12-04 Sweet 1 5000 1014.4839 2014-12-04
The above results are correct but I hard coded number of days as 31 in the Date in the expression
6.2898*SUM(Quantity )/31 AS BBLperDay
Is there a way I can do just have a value taken from Date column based on the month. If the month is December in the Date column the value will automatically be 31 as number of days in december are 31.
解决方案To find no. of days in a month
Select DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,getdate()),0)))
or If you are using
SQL SERVER 2012+
Select DAY(EOMONTH(getdate()))
Change your query like this.
SELECT Type, COUNT(Id) AS Trades, SUM(Quantity ) AS M3, ROUND((6.2898*SUM(Quantity ))/DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,TradeMonth),0))),4) AS BBLperDay, CAST(Date as date) AS TradeMonth, ROUND(SUM(Quantity*Price)/Sum(Quantity),4) AS WeightedAverage FROM Deals GROUP BY Type,CAST(Date as date),TradeMonth
这篇关于在SQL表中的日期字段中将值除以一个月中的天数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!