Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: Total days in a month

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 a data in a year with only using one parameter which is Year.
How can I apply the formula into 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.   
like image 742
Haminteu Avatar asked May 13 '15 04:05

Haminteu


2 Answers

You can get the number of days of a given date like this:

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

And the query:

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
like image 177
sqluser Avatar answered Sep 18 '22 16:09

sqluser


This expression will give you the number of days in the month that date is in no matter what day it is:

datediff(day,
  dateadd(month,datediff(month, 0, date),0),
  dateadd(month,datediff(month, 0, date)+1,0))
like image 20
Brian Pressler Avatar answered Sep 21 '22 16:09

Brian Pressler