Q:

I want to group data from a SQL Server database into hourly report

I would like to ask the community the following:
I have a table Productivity with the following columns:
SerialNumber (Primary Key) - Identity Column
Processed_Time_Stamp - DateTime
Login_ID - nvarchar(255)
Order_Number - Float
Order_Location - Float
Status - nvarchar(255)

I am using this query:
SELECT 
    Login_ID, COUNT(Login_ID) as [Total Number]
FROM 
    Productivity
WHERE 
    Processed_Time_Stamp >= '2014-12-03 10:30:00.000' 
    AND Processed_Time_Stamp <= '2014-12-04 10:30:00.000'
GROUP BY 
    Login_ID

This gives me these results:
Login_ID    Total Number
------------------------
Zohaib          10
XYX             20

However, I want to break this up in the following format:
Login_ID   10 AM   11 AM
-------------------------
zohaib       5       5
XYZ          7      13

Thanks in advance

A:

You can try this:
WITH DATA
AS
(
SELECT Login_ID, 1 AS N, DATEPART(HOUR, Processed_Time_Stamp) H
FROM Productivity
WHERE Processed_Time_Stamp >='2014-12-03 10:30:00.000' and Processed_Time_Stamp <= '2014-12-04 10:30:00.000'
)
SELECT Login_ID, [10], [11]
FROM DATA
PIVOT (SUM(N) FOR H IN ([10], [11])) AS P

Hope this helps.
Cheers.

