SQL: total days per month

Question

I have the following formula:

"Value1" * 100 * "Total day in a month" * "Value2" 

I have the following table:

 ID Date Time Value1 Value2 1 2014-01-01 00:00 10 20 2 2014-01-01 01:00 20 5 

I want to select data for a year using only one parameter Year .
How to apply a formula in a query?

The result should be:

 ID Date Time Value1 Value2 TotalDayinMonth Result 1 2014-01-01 00:00 10 20 31 620000 2 2014-01-01 01:00 20 5 31 310000 ff. 

Answer 1

You can get the number of days of a certain date, for example:

 DECLARE @date DATETIME = '2014-01-01' SELECT DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date)) 

And request:

 SELECT ID ,[Date] ,[Time] ,Value1 ,Value2 ,DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) AS TotalDayinMonth ,Value1 * 100 * DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) * Value2 AS Result FROM yourTable 

Answer 2

This expression will give you the number of days in the month during which date does not matter:

 datediff(day, dateadd(month,datediff(month, 0, date),0), dateadd(month,datediff(month, 0, date)+1,0)) 

Answer 3

Mark this answer. You can use the EOMONTH AND DAY SQL function to get the number of days per month.

 SELECT ID ,[Date] ,[Time] ,Value1 ,Value2 ,DAY(EOMONTH(Date)) AS TotalDaysInMonth ,Value1 * 100 * DAY(EOMONTH(Date)) * Value2 AS Result FROM TABLENAME 

Answer 4

You can also check this out.

 declare @t table (ID int, Date date, Time time, Value1 int, Value2 int) insert into @t values (1,'2014-01-01','00:00',10,20 ) , (2,'2014-01-01','00:00',20,5 ), (3,'2014-02-01','00:00',20,5 ) select * from @t ; with cte as ( select id, day(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATE)+1,0))) Totaldayofmonth from @t ) --select * from cte select *, Value1 * 100 * Totaldayofmonth * Value2 from @tt inner join cte on cte.ID = t.id