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