CODEWITHSUNDEEP
sqlsql-server
Belliez
Belliez

Reputation: 5376

SQL Syntax to count unique users completing a task

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.

Upvotes: 1

Views: 101

Answers (3)

M.Ali
M.Ali

Reputation: 69554

CREATE TABLE  SomeTable 
(Name NVARCHAR(20))
GO

INSERT INTO SomeTable 
VALUES ('Mark'), ('Mark'),('Mark'),('SAM'),('SAM'),('Josh')
GO

;WITH CTE
AS
(
    SELECT NAME, COUNT(*) AS Total FROM SomeTable
    GROUP BY Name
)

SELECT * 
FROM CTE
        PIVOT (
                MAX(Total)
                FOR NAME 
                IN ([Mark], [SAM], [Josh])      
               )l

Result Set

Mark SAM  Josh
3    2    1

Obviously If there are many values in the table you will need to use Dynamic Sql, 1st build the Comma deliminited list of all the distinct values/names then pass the list of names in the 'IN' cluase of above query it to the Dynamic Sql.
Using Dynamic SQL

DECLARE @NameList NVARCHAR(MAX) = ''
DECLARE @Sql NVARCHAR(MAX)

SELECT @NameList =   @NameList + ',['  + Name + ']' 
                    FROM (SELECT DISTINCT NAME FROM SomeTable)t
SET @NameList =    STUFF(@NameList, 1, 1, '')
PRINT @NameList      --<---- for checking purpose to see if list is as expected

SET @Sql = ';WITH CTE
AS
(
    SELECT NAME, COUNT(*) AS Total FROM SomeTable
    GROUP BY Name
)

SELECT * 
FROM CTE
        PIVOT (
                MAX(Total)
                FOR NAME 
                IN ('+ @NameList + ')       
               )l'
EXECUTE sp_executesql @Sql

Result Set

Mark SAM  Josh
3    2    1

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Declare @t table(names varchar(100))

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;

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

insert into @t(names)
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

Select 
sum(case when names='Paul' then 1 else 0 end) as Paul,
sum(case when names='Matt' then 1 else 0 end) as Matt,
.
.
.
from @t

Upvotes: 0

ydaetskcoR
ydaetskcoR

Reputation: 56967

Not sure if you specifically want it in that format but if you had it in rows it's a lot simpler as a query:

SELECT Names, COUNT(Names)
FROM Table1
GROUP BY Names

SQL Fiddle

Upvotes: 1

Related Questions