CREATE PROCEDURE dbo.QR_CourseLoginLog
	@CallerAdminID	INT ,
	@CourseID	INT ,
	@StartDate	DATETIME = NULL ,
	@FinishDate	DATETIME = NULL 
AS
SET NOCOUNT ON
-- This routine relies on the GROUP BY to return a result set grouped by hours
-- Note that the Convert(varchar(8), LastLogin, 112) converts LastLogin to date only (no time part)
-- Converting this back to datetime returns that date at midnight.
-- We then add just the hour back to that date and voila!  datetime with hour granularity..
-- GROUP BY then groups all date/hour together, and we can count'em using count(*) as Total
-- Parameterizing the granularity would require some thought.  hour, day, week, month would be interesting.

DECLARE	@ClientID	INT
SELECT	@ClientID = ClientID FROM dbo.Admins WHERE AdminID = @CallerAdminID

-- The BETWEEN date operator requires that we round the dates to midnights to properly bracket the condition
SET	@StartDate = CAST(CONVERT(VARCHAR, @StartDate, 101) AS DATETIME) -- Resets time to 00:00:00
SET	@FinishDate = CAST(CONVERT(VARCHAR, DATEADD(dd, 1, @FinishDate), 101) AS DATETIME) -- Resets time to 00:00:00 of NEXT DAY

SELECT	DATEADD(hh, DatePart(hour, LD.LoginDate), Convert(datetime, Convert(varchar(8), LD.LoginDate, 112))) as [Date & Time],  
	Count(*) as [User Login Count]
	FROM	dbo.LoginData LD
	INNER JOIN dbo.Users  U ON U.UserID = LD.UserID
	WHERE	LD.LoginDate BETWEEN @StartDate AND @FinishDate
	AND	U.ClientID = @ClientID
	AND	LD.CourseID = @CourseID
	GROUP BY	DATEADD(hh, DatePart(hour, LD.LoginDate), Convert(datetime, Convert(varchar(8), LD.LoginDate, 112)))
	ORDER BY [Date & Time] DESC

SET NOCOUNT OFF
GO