Create Calculated Fields from Distinct Values in Second Table

Question

I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code from the 'ERRCODE' field in 'Errors'. For each of the error codes, a count will be done for that error code from the 'Errors' table.

I already have a table function called 'CreateCalendar' that generates a date field with dates populated for the year specified. What I'm struggling to do is use the unique error codes from table 'Errors' and then create the calculated fields from those values.

The 'CreateCalendar' table function requires a 4 digit integer which is set as a parameter. This table function works fine alone and with another query I've used it with.

At the moment I have the below query but I have a feeling I'm going about this the wrong way. I'm still very new to SQL and have no formal training which I hope won't hinder me too much or annoy yourselves.

;WIth ErrorList As
(
 SELECT DISTINCT
  Errors.ERRCODE
 FROM
  Errors
 WHERE
  Errors.ERRTYPE = 'BATCH'
)

DECLARE @cnt int = 0;
DECLARE @name nvarchar(4);

SELECT
 CreateCalendar.cal_date,
 WHILE @cnt < (SELECT Count(ERRCODE) FROM ErrorList)
 BEGIN
  SET @name = (SELECT (ErrorList.ERRCODE) FROM ErrorList WHERE ROW_NUMBER() = @cnt);
  (SELECT Count(ErrorList.ERRCODE) FROM Errors WHERE Errors.ERRDATE = CreateCalendar.cal_date) As @name
  SET @cnt = @cnt + 1;
 END;
FROM
 CreateCalendar(@p_year)

If this is a workable approach, I believe a lot of where I'm going wrong is with my syntax and also picking the error code based on its row number. I could be way off the mark completely.

Any help is appriciated.

EDIT

EXAMPLE EXPECTED OUTPUT

cal_date   | 0001 | 0002 | 0003
01/01/2016 |   20 |   54 |    0
02/01/2016 |   15 |   22 |    2
03/01/2016 |   18 |   38 |    5
04/01/2016 |    0 |    0 |    0
05/01/2016 |   10 |    2 |    7

Answer 1

This looks lika a PIVOT operation, where you want an aggregate (the count) of your error codes, grouped by date (on rows) and error code (on columns).

First off, pivot reports are probably easier to design in your reporting tool, Report Builder, so I would try this query first and try to format the results into a pivot table in the GUI. I don't know Report Builder very well, but the query should look something like this:

SELECT cal.cal_date,
       ec.ERRCODE,
       COUNT(*) AS [count]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date, ec.ERRCODE;

If that doesn't work, you can make SQL Server do the pivoting for you, but the downside is that you have to explicitly specify each column (error code) manually in the query, which is repetitive and any future error code won't show up without editing the query later on.

SELECT cal.cal_date,
       SUM((CASE WHEN ec.ERRCODE='0001' THEN 1 ELSE 0 END)) AS [0001],
       SUM((CASE WHEN ec.ERRCODE='0002' THEN 1 ELSE 0 END)) AS [0002],
       SUM((CASE WHEN ec.ERRCODE='0003' THEN 1 ELSE 0 END)) AS [0003],
       SUM((CASE WHEN ec.ERRCODE='0004' THEN 1 ELSE 0 END)) AS [0004]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date
ORDER BY cal.cal_date;

For more reading on this type of operation, take a look at GROUP BY, aggregate functions (like COUNT() and SUM()) and PIVOT in T-SQL (although I used a "manual" construct to build a pivot table instead of the built-in PIVOT syntax).