SELECT	DATEADD(hh, DatePart(hour, LD.LoginDate), Convert(datetime, Convert(varchar(8), LD.LoginDate, 112))) as [Date & Time],  
	Count(*) as [Hour Total]
	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

quote:

---

Originally posted by timmy

(SELECT Count(*) FROM dbo.LoginData WHERE UserID = LD.UserID AND convert(varchar, LD.LoginDate , 101) = convert(varchar, LoginDate , 101)) as LoginsToday

---

CREATE TABLE #LoginTotals (								-- Temp table is needed to self-join
	LoginHour	SMALLDATETIME NOT NULL PRIMARY KEY,
	HourTotal	INT NOT NULL)


INSERT INTO #LoginTotals (LoginHour, HourTotal)						-- Gather the hourly data for the period of interest
	
	SELECT	DATEADD(hh, DatePart(hour, LD.LoginDate), Convert(datetime, Convert(varchar(8), LD.LoginDate, 112))) As LoginHour,  
		COUNT(*) as HourTotal
	
		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)))

SELECT	A.LoginHour As [Date & Time], A.HourTotal As [Hour Total],			-- Return a result set with Day Totals 
	CASE	WHEN	B.MaxLoginHour IS NULL						-- Only on the last hour of the day
		THEN	NULL
		ELSE	DayTotal
	END	AS [Day Total]

	FROM (
		SELECT	A.LoginHour, A.HourTotal, SUM(B.HourTotal) As DayTotal		-- Calculate the Day total for every hour in the day
		
			FROM	#LoginTotals A
			LEFT OUTER JOIN #LoginTotals B
				ON	DATEDIFF(dd, B.LoginHour, A.LoginHour) = 0	-- Join on same day
				AND	B.LoginHour <= A.LoginHour			-- But only where B occurs before A
		
			GROUP BY A.LoginHour, A.HourTotal
	) A
	LEFT OUTER JOIN (								-- Identify the last hour in the day for filtering
		SELECT	MAX(LoginHour) As MaxLoginHour
			FROM	#LoginTotals
			GROUP BY CONVERT(VARCHAR, LoginHour, 112)
	) B	ON	B.MaxLoginHour = A.LoginHour

	ORDER BY A.LoginHour DESC

DROP TABLE #LoginTotals

Row  	 Date & Time  	 Hour Total  	 Day Total
1 	7/13/2005 9:00:00 AM 	108 	176
2 	7/13/2005 8:00:00 AM 	53 	 
3 	7/13/2005 7:00:00 AM 	11 	 
4 	7/13/2005 6:00:00 AM 	1 	 
5 	7/13/2005 2:00:00 AM 	2 	 
6 	7/13/2005 		1 	 
7 	7/12/2005 11:00:00 PM 	3 	1595
8 	7/12/2005 10:00:00 PM 	1 	 
9 	7/12/2005 9:00:00 PM 	2 	 
10 	7/12/2005 8:00:00 PM 	14 	 
11 	7/12/2005 7:00:00 PM 	33 	 
12 	7/12/2005 6:00:00 PM 	45