Combine multiple records into one, using name from only one record I've got a query that returns a table of technicians and the number of work orders they've filled. The problem is that sometimes technicians get entered using their full name, and sometimes they get entered using their userID. I want this table to use full names, and combine records where the userID is used with records corresponding to their full name. UserID comes as either 'FirstInitial' + 'LastName' or '$FirstName' + '$LastInitial', where + is string concatenation

Example of my table:

 Maddoc 74 - 229
 M74 - 21
 Maddoc7 - 14
 John Smith - 215
 jsmith - 47
 johns - 19
 Anna Kellog - 251
 akellog - 67
 annak - 14


What I want it to look like:

 Maddoc 74 - 264
 John Smith - 281
 Anna Kellog - 332


Here's my current query:

SELECT "RESPONDEDBY" AS Technician, COUNT("RESPONDEDBY") AS Tickets
FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE
WHERE DATEPART(m,"CLSDDATE") = DATEPART(m, {?reportingDate})
AND DATEPART(yyyy,"CLSDDATE") = DATEPART(yyyy, {?reportingDate})
AND "RESPONDEDBY" NOT IN (SELECT "RESPONDEDBY" FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE WHERE "RESPONDEDBY" = 'EmailMonitor')
GROUP BY "RESPONDEDBY"
ORDER BY COUNT("RESPONDEDBY") DESC
this query will get you the results you wanted for your example

CREATE TABLE tracking(name VARCHAR(50), cnt INT);
INSERT INTO tracking
VALUES( 'Maddoc 74',229),
      ('M74',21),
      ('Maddoc7',14),
      ('John Smith',215),
      ('jsmith',47),
      ('johns',19),
      ('Anna Kellog',251),
      ('akellog',67),
      ('annak',14);
WITH cte AS(
SELECT ROW_NUMBER() OVER(ORDER BY name) AS rn,name,SUBSTRING(name,1,1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,LEN(name)) AS firstl,SUBSTRING(name,1,CHARINDEX(' ',name,1)-1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,1) AS flast
FROM tracking
WHERE name LIKE '% %')
SELECT MIN(c.name),SUM(t.cnt)
FROM tracking t
INNER JOIN cte c
ON t.name = c.name
OR t.name = c.firstl
OR t.name = c.flast
GROUP BY rn;


You will have issues if you also have a Jane smith, John Sanders, etc. this also doesn't account for middle names in the full name