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.
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
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With