在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屋！