For whatever reason, this query keeps deadlocking in SSRS, but runs in less than one second in SQL Management Studio.
An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset DataSet1. (rsErrorReadingNextDataRow)
Transaction (Process ID 111) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Here is the query:
;WITH all_dates AS (SELECT DATEADD(dd, Numbers.n - 1, @StartDate) AS Dates FROM Numbers WHERE Numbers.n <= DATEDIFF(dd, @StartDate, @EndDate) + 1) ,response_dates AS (SELECT DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date ,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count FROM Response_Master_Incident WHERE Response_Master_Incident.Time_First_Unit_Assigned IS NOT NULL AND CAST(Response_Master_Incident.Response_Date AS DATE) >= @StartDate AND CAST(Response_Master_Incident.Response_Date AS DATE) <= @EndDate AND EXISTS ( SELECT TOP 1 * FROM Activity_Log JOIN Unit_Names ON Unit_Names.Alias = Activity_Log.Radio_Name JOIN Vehicle ON Vehicle.UnitName_ID = Unit_Names.ID JOIN Division ON Vehicle.HomeDivisionID = Division.ID JOIN Jurisdiction ON Division.JurisdictionID = Jurisdiction.ID WHERE Jurisdiction.Name = @JurisdictionName AND Activity_Log.Master_Incident_ID = Response_Master_Incident.ID AND Activity_Log.Activity = 'On Scene' ) GROUP BY CAST(Response_Master_Incident.Response_Date AS DATE) ) SELECT a.Dates, ISNULL(Response_Count, 0) AS Response_Count FROM all_dates a LEFT JOIN response_dates r ON a.Dates = r.Response_Date