0

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
  • Could you add a few lines of an example output from your query to the original question? Also, I think it would help if you add the source of the CreateCalendar function. Use the "edit" button in the lower-left corner of the question. – Daniel Hutmacher Feb 15 '16 at 11:34
  • Unfortunately I'm unable to open/edit the table function. It's controlled by our main database administrators. If I absolutely needed to request the code, I may be able to obtain it although they are an external company and may be reluctant to share. I've used it before though and it outputs a column titled 'cal_date' and ~400 rows are created with dates starting from 01/01 of the year passed over for @p_year. I will edit my code to show an example output of what I want. – Boltie Feb 15 '16 at 11:44
2

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).

  • Thanks for the quick response. I'll give them a go an report back later. – Boltie Feb 15 '16 at 12:09
  • I forgot to mention that I'd like to have each day listed even when no count for an error is found. This is how my original method came about. – Boltie Feb 15 '16 at 14:02
  • That's what the LEFT JOIN does. If you only want to see days with counts, you can change it to INNER JOIN. – Daniel Hutmacher Feb 15 '16 at 14:06
  • I thought the LEFT JOIN would do that but for some reason it only returns dates where there is a count for the error. Could it be becasue cal_date is grouped with ERRCODE? I would expect it to be; cal_date | 0001 | 0002 | 0003 | 01/01/2015 | 5 | | 7 | 02/01/2015 | | | | 03/01/2015 | 3 | 1 | | Instead, row 2 from above isn't returned when I'd like it to be. I'll play around a little more and get back to you. It's probably something I've not done correctly. – Boltie Feb 15 '16 at 14:22
  • Have you added anything, like a WHERE clause? – Daniel Hutmacher Feb 15 '16 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.