I have issue with one script , the error is the following:

ERROR: Arithmetic overflow error converting expression to data type int.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PCO_PERF_REPORT]
@IN_DATE VARCHAR(8) = NULL
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @version_script varchar(200)
SET @version_script = ‘StoredProcedure [dbo].[PCO_PERF_REPORT] v1.3 2021-03-17’

DECLARE @cdate date
IF (@IN_DATE IS NULL)
SET @cdate = GETDATE()
ELSE
BEGIN
SET @cdate = (SELECT convert(date, @IN_DATE))
IF (@cdate IS NULL)
BEGIN
print ‘Input date wrong format YYYYMMDD: ‘ + @IN_DATE
RETURN
END
END

---

Last 12 Month’s Number of Applications per Month ———————————————–

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Apps’
FROM [dbo].[ResponseTime]

WHERE [dbo].[ResponseTime].[ServiceID] IN (’01’,’02’,’03’) –To Remove internal interfaces

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

---

Last Month’s Number of Applications per Day —————————————————-

---

SELECT
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) AS ‘Date’
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Apps’
FROM [dbo].[ResponseTime]

WHERE SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) >= DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –start of last month (1st)
AND SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) <= DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –end of last month (last day)
AND [dbo].[ResponseTime].[ServiceID] IN (’01’,’02’,’03’) –To Remove internal interfaces

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8)

---

Last Month’s Number of Applications per Hour per Day ——————————————-

---

SELECT
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) AS ‘Date’
,SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),9,2)+’:00 > ‘+CAST(CAST(SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),9,2) AS INT)+1 AS VARCHAR)+’:00′ AS ‘Hour’
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Apps’
FROM [dbo].[ResponseTime]

WHERE SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) >= DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –start of last month (1st)
AND SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) <= DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –end of last month (last day)
AND [dbo].[ResponseTime].[ServiceID] IN (’01’,’02’,’03’) –To Remove internal interfaces

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8), SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),9,2)

---

Last 12 Month’s Enrichment Interface Response Times per Month (Badexcug) ————————-

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘Badexcug Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘BadexcugInterfacePack’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Enrichment Interface Response Times per Month ASNEF Detail ————————-

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘ASNEF Detail Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘ASNEF_Detail_IP’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Enrichment Interface Response Times per Month RiskScore & Severity ————————-

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘RiskScore and Severity Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘RiskScoreIndividualsAndSeverity_IP’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Enrichment Interface Response Times per Month (Gas Natural) ————————-

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘Gas Natural Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘GasNaturalInterfacePack’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Enrichment Interface Response Times per Month Incidencias Judiciales ———–

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘Incidencias Judiciales Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘Equifax_Judiciales_IP’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Enrichment Interface Response Times per Month Modelo R ———–

---

SELECT TOP 12
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,ROUND(AVG([Duration]), 2) AS ‘Modelo R Call Duration’

FROM [dbo].[ResponseTime]

WHERE [ResponseTime].[ServiceID] = ‘Modelo_R_IP’

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Evaluacion Service Response Times per Month ————————————

---

SELECT TOP 13
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Evaluacion Apps’
,AVG([ResponseTime].[Duration]) AS ‘bigint’
FROM [dbo].[ResponseTime]

WHERE [dbo].[ResponseTime].[ServiceID] IN (’01’,’02’) –To Remove Decision Final apps

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

Last 12 Month’s Decision Final Service Response Times per Month ————————————

---

SELECT TOP 13
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) AS ‘Month’
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Decision Final Apps’
,AVG([ResponseTime].[Duration]) AS ‘bigint’
FROM [dbo].[ResponseTime]

WHERE [dbo].[ResponseTime].[ServiceID] = ’03’ –To Remove Decision Final apps

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6)
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,6) DESC

---

Last Month’s Number of Total Calls per Day (Badexug and Asnef) ——————————–

---

SELECT
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) AS ‘Date’,
CONVERT(VARCHAR(10),replace (REPLACE(ErrorCode,’1′,’Fail’),’0′,’Correct’)) as ErrorCode,ServiceID
,COUNT([ResponseTime].[ApplicationID]) AS ‘Total Apps BADEXCUG, ASNEF Detail, RiskScore & Severity, Gas Natural, Incidencias Judiciales and Modelo R Calls’
FROM [dbo].[ResponseTime]

WHERE SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) >= DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –start of last month (1st)
AND SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) <= DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –end of last month (last day)
AND [dbo].[ResponseTime].[ServiceID] IN (‘BadexcugInterfacePack’, ‘ASNEF_Detail_IP’, ‘RiskScoreIndividualsAndSeverity_IP’, ‘GasNaturalInterfacePack’, ‘Equifax_Judiciales_IP’, ‘Modelo_R_IP’) –AND [dbo].[ResponseTime].[ErrorCode] IN (‘1’)–To Remove internal interfaces

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8),ErrorCode, ServiceID
ORDER BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8)

---

Last Month’s Number of Total Calls per Day ——————————————-

---

–table for storing all potential missing combinations, built dynamically for every single month
DECLARE @pivotMonthTable TABLE(pivotDate varchar(8), pivotErrorCode varchar(100), pivotServiceId varchar(100))

DECLARE @monthStartDay datetime
DECLARE @monthEndDay datetime

–calculate both first day and last day of current month
set @monthStartDay = DATEADD(month,0, DATEADD(month, DATEDIFF(month, 0, DATEADD(month,-1, @cdate)), 0))
set @monthEndDay = DATEADD(month,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(month,-1, @cdate))+1,0)))

–debug
–select @monthStartDay, @monthEndDay

–populate the temp table with all month days with all potential missing combinations (day, errorcode and serviceid)
WHILE(@monthStartDay < @monthEndDay)
BEGIN
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘BadexcugInterfacePack’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘BadexcugInterfacePack’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘ASNEF_Detail_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘ASNEF_Detail_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘RiskScoreIndividualsAndSeverity_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘RiskScoreIndividualsAndSeverity_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘GasNaturalInterfacePack’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘GasNaturalInterfacePack’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘Equifax_Judiciales_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘Equifax_Judiciales_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Correct’, ‘Modelo_R_IP’
INSERT INTO @pivotMonthTable
SELECT convert(varchar(8),@monthStartDay,112), ‘Fail’, ‘Modelo_R_IP’
SELECT @monthStartDay = DATEADD(day, 1,@monthStartDay)
END

–join from prod log table and temp pivot table, keeping existing records and filling the missing records in prod log table with the temp pivot table ones
select isnull(q2.Date, pivotMonthTable.pivotDate) as ‘Date’, isnull (q2.ErrorCode,pivotMonthTable.pivotErrorCode) as ‘ErrorCode’,
isnull (q2.ServiceID, pivotMonthTable.pivotServiceId) as ‘ServiceID’, isnull (q2.[Total Apps BADEXCUG, ASNEF Detail, RiskScore & Severity, Gas Natural, Incidencias Judiciales and Modelo R Calls], ‘00000’) as ‘Total Apps BADEXCUG, ASNEF Detail, RiskScore & Severity, Gas Natural, Incidencias Judiciales and Modelo R Calls’
from @pivotMonthTable pivotMonthTable
left join (
SELECT
SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) AS ‘Date’,
CONVERT(VARCHAR(10),replace (REPLACE(ErrorCode,’1′,’Fail’),’0′,’Correct’)) as ErrorCode,[ResponseTime].ServiceID as ServiceID,
right(‘00000’ + CONVERT(VARCHAR(5),COUNT([ResponseTime].[ApplicationID])),5) AS ‘Total Apps BADEXCUG, ASNEF Detail, RiskScore & Severity, Gas Natural, Incidencias Judiciales and Modelo R Calls’
FROM [dbo].[ResponseTime]

WHERE SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) >= DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –start of last month (1st)
AND SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8) <= DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@cdate),0)) –end of last month (last day)
AND [dbo].[ResponseTime].[ServiceID] IN (‘BadexcugInterfacePack’, ‘ASNEF_Detail_IP’, ‘RiskScoreIndividualsAndSeverity_IP’, ‘GasNaturalInterfacePack’, ‘Equifax_Judiciales_IP’, ‘Modelo_R_IP’) –AND [dbo].[ResponseTime].[ErrorCode] IN (‘1’)–To Remove internal interfaces

GROUP BY SUBSTRING(CONVERT(VARCHAR, [ResponseTime].[StartTime], 23),1,8),
CONVERT(VARCHAR(10),replace (REPLACE(ErrorCode,’1′,’Fail’),’0′,’Correct’)),
[ResponseTime].ServiceID) as q2
on (pivotMonthTable.pivotDate = q2.Date and pivotMonthTable.pivotErrorCode = q2.ErrorCode and pivotMonthTable.pivotServiceId = q2.ServiceID)
ORDER BY isnull(q2.Date, pivotMonthTable.pivotDate), isnull (q2.ErrorCode,pivotMonthTable.pivotErrorCode), isnull (q2.ServiceID, pivotMonthTable.pivotServiceId)

END

GO

```
``

Nothing worked. Maybe the bigint should be without brackets and I should use cast in order to avoid overflow, but I don’t know really what should be done here in the exact situation.