SQL Server - Aggregate the number of errors per user per day

Question

The application that I maintain stores user errors in a SQL Server table. When an error occurs it jots down the username of the person that caused it, the time, the error message, and some other housekeeping stuff.

I'm trying to build out a report where we could see the "top 3" errors each day for the past year - the three users with the most errors on each day, the three most common types of errors on each day, etc.

My goal is something like this:

DATE       USER1    ERR_COUNT1    USER2    ERR_COUNT2    USER3    ERR_COUNT3
1/1/18     BOB      70            BILL     50            JOE      30
1/2/18     JILL     55            JOY      30            BOB      20
...

I've got a rough loop set up to pull this data from our error logs but when I run it I get the error There is already an object named '#TempErrorLog'in the database. Loop code below:

DECLARE @StartDate AS DATE,
        @EndDate AS DATE

SET @StartDate = '2018.1.1'

WHILE @StartDate <= CONVERT(DATE, GETDATE())
    BEGIN
        SET @EndDate = DATEADD(DAY, 1,  @StartDate) 

        SELECT @StartDate AS date_err,
               ( u.name_frst+' '+u.name_lst ) AS user_err,
               COUNT(e.id_err) AS count_err
        INTO dbo.#TempErrLog
        FROM err_log AS e
             LEFT JOIN users AS u ON e.id_user = u.id_user
        WHERE e.dttm_err >= @StartDate AND
              e.dttm_err < @EndDate AND
              e.id_user <> 'system'
        GROUP BY ( u.name_frst+' '+u.name_lst )
        ORDER BY count_err DESC;

        SET @StartDate = DATEADD(DAY, 1,  @StartDate)

            CONTINUE
    END

SELECT * FROM #TempErrLog

My guess is that it is trying to create a new temporary table each time the loop iterates. Is there a better approach I should be using here?

This would be a dynamic pivot. Also when you use `SELECT INTO #Temp` it creates the table on the fly, inheriting the DDL from the base tables. If you want to stick with your looping method, you'll want to create the table first (outside of the loop) and then use `INSERT INTO #Temp SELECT....` — S3S, Oct 26 '18 at 19:22
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — S3S, Oct 26 '18 at 19:23
Simply creating #TempErrLog manually before the loop, and droping it after select * should work — Felipe Martins, Oct 26 '18 at 19:26

score 1 · Accepted Answer · answered Oct 26 '18 at 19:32

You can pivot this using conditional aggregation and row_number(). For the results in your question:

with ue as (
      select e.*, (u.name_frst + ' ' + u.name_lst ) as user_name,
             cast(e.dttm_err as date) as err_date
      from err_log e join
           users u
           on e.id_user = u.id_user
     )
select u.err_date,
       max(case when u.seqnum = 1 then u.user_name end) as user_1,
       max(case when u.seqnum = 1 then u.cnt end) as cnt_1,
       max(case when u.seqnum = 2 then u.user_name end) as user_2,
       max(case when u.seqnum = 2 then u.cnt end) as cnt_2,
       max(case when u.seqnum = 3 then u.user_name end) as user_3,
       max(case when u.seqnum = 3 then u.cnt end) as cnt_3
from (select err_date, user_name, count(*) as cnt,
             row_number() over (partition by err_date order by count(*) desc) as seqnum
      from ul
      group by err_date, user_name
     ) u 
group by u.err_date

SQL Server - Aggregate the number of errors per user per day

1 Answers1