Count and order records by day

[morlan]

I am trying to make a query that will count records and sort them day by day.

Here is the table example:

tblMain

RecordID ----- DeviceName --------- User ------------- DateTime
===============================================
00001 -------- PC001 ---------------- John Doe ------- 01/01/2003
00002 -------- PC001 ---------------- John Doe ------- 01/01/2003
00003 -------- PC001 ---------------- Jane Dale ------ 02/01/2003
00004 -------- PC001 ---------------- Jane Dale ------ 02/01/2003
00005 -------- PC001 ---------------- Jane Dale ------ 02/01/2003
00006 -------- PC001 ---------------- Mark Wilson --- 02/01/2003
00007 -------- PC002 ---------------- Jane Dale ------ 03/01/2003
00008 -------- PC002 ---------------- John Doe ------- 03/01/2003
00009 -------- PC002 ---------------- John Doe ------- 03/01/2003
00010 -------- PC002 ---------------- John Doe ------- 04/01/2003
00011 -------- PC002 ---------------- John Smith ----- 04/01/2003
00012 -------- PC002 ---------------- Mark Wilson ---- 04/01/2003



Right now I am manualy sorting the records by date and then pasting into a spreadsheet.

This is how the spreadsheet looks:

-------------------- John Doe ---- Jane Dale ---- Mark Wilson ---- John Smith ---- Totals
01/01/2003 ---- 2 --------------- 0 -------------- 0 ------------------ 0 ---------------- 2
02/01/2003 ---- 0 --------------- 3 -------------- 1 ------------------ 0 ---------------- 4
03/01/2003 ---- 2 --------------- 1 -------------- 0 ------------------ 0 ---------------- 3
04/01/2003 ---- 1 --------------- 0 -------------- 1 ------------------ 1 ---------------- 3

This is what I am trying to achieve in the query.
One method that was suggested was this

SELECT CONVERT(CHAR(10), [Date], 101) AS [Date],
SUM(CASE WHEN User = 'John Doe' THEN 1 ELSE 0 END) AS [John Doe],
SUM(CASE WHEN User = 'Jane Dale' THEN 1 ELSE 0 END) AS [Jane Dale],
SUM(CASE WHEN User = 'Mark Wilson' THEN 1 ELSE 0 END) AS [Mark Wilson],
SUM(CASE WHEN User = 'John Smith' THEN 1 ELSE 0 END) AS [John Smith],
SUM(1) AS Totals
FROM tblMain
--WHERE [Date] BETWEEN '...' AND '...'
GROUP BY CONVERT(CHAR(10), [Date], 101)

...but there are over 180 users! It would take forever to add them in to the query.

Any help appreciated

 

[Rich]

Use a crosstab query