Nice work, Peter! Thanks for posting this up.
Couple of suggestions...you might be able to cut the reads down on the ros table using something like this:;WITH ros AS (
SELECT VolunteerID,
DateFrom = Cast(ROS.DateFrom As Date),
DateTo = Cast(IsNull(ROS.DateTo , GetDate()) As Date)
FROM dbo.RecordOfService
)
SELECT
t.VolunteerID,
[FromWhen] = CONVERT(nvarchar(12), t.FromWhen, 105),
[ToWhen] = CONVERT(nvarchar(12), t.ToWhen, 105),
t.NoOfWorkingDays,
[VirtualEndDate] = DATEADD(D, t.NoOfWorkingDays - 1, t.FromWhen),
[DateDiffAsYMD] = dbo.fnDateDiffsYMD(t.FromWhen, DATEADD(D, t.NoOfWorkingDays - 1, t.FromWhen))
FROM (
SELECT
ros.VolunteerID,
[NoOfWorkingDays] = COUNT(DISTINCT d.[Date]),
[FromWhen] = MIN(ros.DateFrom),
[ToWhen] = MAX(ros.DateTo)
FROM dbo.DimDate d
INNER JOIN ros
ON d.[Date] BETWEEN ros.DateFrom AND ros.DateTo
GROUP BY ros.VolunteerID
) t
Secondly, that UDF will inhibit parallelism - I wonder if it can be converted to an inline table-valued function...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden