SQL Syntax to count unique users completing a task

Posted by Belliez on Stack Overflow See other posts from Stack Overflow or by Belliez
Published on 2013-11-07T10:57:18Z Indexed on 2013/11/07 15:54 UTC
Read the original article Hit count: 429

Filed under:
|

I have the following code which shows me what users has completed ticket and this lists each user and the date they close a ticket. i.e.

Paul
Matt
Matt
Bob
Matt
Paul
Matt
Matt

At the moment I manually count each user myself to see their totals for the day.

EDIT: Changed output as columns instead of rows:

What I have been trying to do is get SQL Server to do this for me i.e. the final result to look like:

Paul  |  2
Matt  |  5
Bob   |  1

My code I am currently using is and I would be greatful if someone can help me change this so I can get it outputting something similar to above?

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;

-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today


SELECT  (select Username from Membership where UserId =  Ticket.CompletedBy) as TicketStatusChangedBy

FROM         Ticket INNER JOIN
                      TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID INNER JOIN
                      Membership ON Ticket.CheckedInBy = Membership.UserId
WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate --(GETDATE() - 1)
and Ticket.ClosedDate <= @EndDate --(GETDATE()-0)
ORDER BY Ticket.CompletedBy ASC, Ticket.ClosedDate ASC

Thank you for your help and time.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server