Dividing a value by number of days in a month in a date field in SQL table

Question

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.

What version of SQL Server are you using? In newer versions, there's a function ([`EOMONTH()`](http://technet.microsoft.com/en-us/library/hh213020.aspx)) that will give you the number of days in a given month. — alroc, Dec 04 '14 at 21:43

score 4 · Accepted Answer · answered Dec 05 '14 at 01:33

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

Dividing a value by number of days in a month in a date field in SQL table

1 Answers1

Linked