Hi Eddy,

After more than a year I finally got around to start working on the concurrent transaction report.

I followed your instructions for the second query, extended the amount of times it is compared during a day (24hr).


Code:
SELECT date_calendar.calendar_date, Hours.Hr, Trns.TransIdno
FROM date_calendar
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23)) AS Hours(Hr)
LEFT OUTER JOIN dbo.TRANSACTIONS Trns ON 
Trns.StartDate <= date_calendar.calendar_date AND (Trns.StopDate IS NULL OR Trns.StopDate >= date_calendar.calendar_date) 
AND Trns.StartHr <= Hours.Hr AND (Trns.StopHr IS NULL OR Trns.StopHr >= Hours.Hr)
WHERE date_calendar.calendar_date >= (GETDATE()-365) AND date_calendar.calendar_date <= (GETDATE())
One issue I ran into - Query Timeouts:
I kept experiencing "Query Timeouts" that I concluded to happen because of the near infinite (or simply too many) records the date_calendar table in join with the Hours values produces.

Solution - Automated Date Restriction:
To solve this, I simply added the WHERE clause trying to constrain the data for only the last 365 days.