public void Test()
{
// https://pgexercises.com/questions/date/utilisationpermonth.html
// Work out the utilisation percentage for each facility by month, sorted by name and month,
// rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat
// every month as a full month, regardless of if there were some dates the club was not open.
//
//select name, month,
// round((100*slots)/
// cast(
// 25*(cast((month + interval '1 month') as date)
// - cast (month as date)) as numeric),1) as utilisation
// from (
// select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.facid, month
// ) as inn
//order by name, month
EFServiceProvider.RunInContext(context =>
{
var items = context.Bookings
.Select(booking => new
{
booking.Facility.Name,
booking.StartTime.Year,
booking.StartTime.Month,
booking.Slots,
DaysInMonth = EF.Functions.DateDiffDay(
booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day),
booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day).AddMonths(1)
)
})
.GroupBy(b => new { b.Name, b.Year, b.Month, b.DaysInMonth })
.Select(g => new
{
g.Key.Name,
g.Key.Year,
g.Key.Month,
Utilization = SqlDbFunctionsExtensions.SqlRound(
100 * g.Sum(b => b.Slots) / (decimal)(25 * g.Key.DaysInMonth),
1)
})
.OrderBy(r => r.Name)
.ThenBy(r => r.Year)
.ThenBy(r => r.Month)
.ToList();
/*
* SELECT [f].[Name],
* DATEPART(year, [b].[StartTime]) AS [Year],
* DATEPART(month, [b].[StartTime]) AS [Month],
* ROUND(CAST((100 * SUM([b].[Slots])) AS decimal(18,2))
* / CAST((25 * DATEDIFF(DAY,
* DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int),
* CONVERT(date, [b].[StartTime])),
* DATEADD(month, CAST(1 AS int),
* DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int),
* CONVERT(date, [b].[StartTime]))))) AS decimal(18,2)), 1) AS [Utilization]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name],
* DATEPART(year, [b].[StartTime]),
* DATEPART(month, [b].[StartTime]),
* DATEDIFF(DAY, DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int), CONVERT(date, [b].[StartTime])), DATEADD(month, CAST(1 AS int), DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int), CONVERT(date, [b].[StartTime]))))
* ORDER BY [f].[Name], DATEPART(year, [b].[StartTime]), DATEPART(month, [b].[StartTime])
*/
var expectedResult = new[]
{
new { Name = "Badminton Court", Year = 2012, Month = 7, Utilization = 23.2M },
new { Name = "Badminton Court", Year = 2012, Month = 8, Utilization = 59.2M },
new { Name = "Badminton Court", Year = 2012, Month = 9, Utilization = 76.0M },
new { Name = "Massage Room 1", Year = 2012, Month = 7, Utilization = 34.1M },
new { Name = "Massage Room 1", Year = 2012, Month = 8, Utilization = 63.5M },
new { Name = "Massage Room 1", Year = 2012, Month = 9, Utilization = 86.4M },
new { Name = "Massage Room 2", Year = 2012, Month = 7, Utilization = 3.1M },
new { Name = "Massage Room 2", Year = 2012, Month = 8, Utilization = 10.6M },
new { Name = "Massage Room 2", Year = 2012, Month = 9, Utilization = 16.3M },
new { Name = "Pool Table", Year = 2012, Month = 7, Utilization = 15.1M },
new { Name = "Pool Table", Year = 2012, Month = 8, Utilization = 41.5M },
new { Name = "Pool Table", Year = 2012, Month = 9, Utilization = 62.8M },
new { Name = "Pool Table", Year = 2013, Month = 1, Utilization = 0.1M },
new { Name = "Snooker Table", Year = 2012, Month = 7, Utilization = 20.1M },
new { Name = "Snooker Table", Year = 2012, Month = 8, Utilization = 42.1M },
new { Name = "Snooker Table", Year = 2012, Month = 9, Utilization = 56.8M },
new { Name = "Squash Court", Year = 2012, Month = 7, Utilization = 21.2M },
new { Name = "Squash Court", Year = 2012, Month = 8, Utilization = 51.6M },
new { Name = "Squash Court", Year = 2012, Month = 9, Utilization = 72.0M },
new { Name = "Table Tennis", Year = 2012, Month = 7, Utilization = 13.4M },
new { Name = "Table Tennis", Year = 2012, Month = 8, Utilization = 39.2M },
new { Name = "Table Tennis", Year = 2012, Month = 9, Utilization = 56.3M },
new { Name = "Tennis Court 1", Year = 2012, Month = 7, Utilization = 34.8M },
new { Name = "Tennis Court 1", Year = 2012, Month = 8, Utilization = 59.2M },
new { Name = "Tennis Court 1", Year = 2012, Month = 9, Utilization = 78.8M },
new { Name = "Tennis Court 2", Year = 2012, Month = 7, Utilization = 26.7M },
new { Name = "Tennis Court 2", Year = 2012, Month = 8, Utilization = 62.3M },
new { Name = "Tennis Court 2", Year = 2012, Month = 9, Utilization = 78.4M }
};
items.Should().BeEquivalentTo(expectedResult);
});
}