Hi,
I am hoping someone can help me with some sample SQL to achieve the even distribution of costs ( calculated on a daily basis) but reported per month.
So the Input would be :
Startdate = 27 Jan 2016 ( Note that the start / end dates could be any month )
End date = 15 April 2016
Total cost = £1000
The Output I would like is
Month, days, value
using the above information,
the total days = 80 [ DATEDIFF(d,'27 Jan 2016','15 April 2016') +1 ]
number of days in Jan = 5 [ DATEDIFF(d,'27 Jan 2016','31 Jan 2016') +1 ]
number of days in Feb = 29 [ DATEDIFF(d,'01 Feb 2016','29 Feb 2016') +1 ]
number of days in Mar = 31 [ DATEDIFF(d,'01 Mar 2016','31 Mar 2016') +1 ]
number of days in Apr = 15 [ DATEDIFF(d,'01 Apr 2016','15 Apr 2016') +1 ]
thus the value per month is ( Value / totaldays * daysinMonth)
So for the above, a sample output would be
Date, days, value
Jan 2016, 5, 62.5
Feb 2016, 29, 362.50
Mar 2016, 31,387.50
Apr 2016, 15,187.50
If anyone can provide an example it would be appreciated.
Create aNnumbers table (aka Tally table) if you don't already have one in your database like this:
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY);
;WITH N(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 )
INSERT INTO #N SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM
N a CROSS JOIN N b CROSS JOIN N c CROSS JOIN N d CROSS JOIN N e
Then write your query like this
SELECT
Year(DATEADD(dd,n,'20160127')),
MONTH(DATEADD(dd,n,'20160127')),
SUM(1000.0/(DATEDIFF(dd,'20160127','20160415')+1))
FROM #N
WHERE
n <= DATEDIFF(dd,'20160127','20160415')
GROUP BY
Year(DATEADD(dd,n,'20160127')),
MONTH(DATEADD(dd,n,'20160127'))
Sorry about no code formatting. If I apply code formatting the forum software tells me it cannot post.