Click here to Skip to main content
15,891,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please Help me As I want to get data for more than a month it throws this Exception..

C#
string startdate, enddate;
        startdate = StartDate.Text;
        enddate = EndDate.Text;

       
        SqlCommand cmd = new SqlCommand("Usp_QueueMISReport", con);
        cmd.CommandType = CommandType.StoredProcedure;
        DbParameter parm = cmd.CreateParameter();
        parm.ParameterName = "@StartDate";
        parm.Value = StartDate.Text;
        parm.DbType = DbType.String;
        cmd.Parameters.Add(parm);
        parm = cmd.CreateParameter();
        parm.ParameterName = "@EndDate";
        parm.Value = EndDate.Text;
        parm.DbType = DbType.String;
        cmd.Parameters.Add(parm);
        parm = cmd.CreateParameter();
        parm.ParameterName = "@DetailType";
        parm.Value = RADIOBUTTONLIST.SelectedValue;
        parm.DbType = DbType.Int32;
        cmd.Parameters.Add(parm);
        
        try
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            GridView1.DataSource = (dt);
            GridView1.DataBind();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception("Execption adding account. " + ex.Message);
        }
        finally
        {
            con.Close();

        }
       
    }

}


here is my stored procedure
SQL
ALTER PROCEDURE [dbo].[Usp_QueueMISReport]
	-- Add the parameters for the stored procedure here
	@StartDate varchar(20),
	@EndDate   varchar(20),
	@DetailType int =0
AS
BEGIN
-- @detailType Param Explain as follows
-- 1 is For mothly
-- 2 is For Daily
-- 3 is for hourly

Select
     Case when @DetailType = 1 then CONVERT(VARCHAR(20), Min(RectificationTime), 106) + ' To ' + CONVERT(VARCHAR(20), Max(RectificationTime), 106)
             when @DetailType = 0 then CONVERT(VARCHAR(20), Min(RectificationTime), 106) + ' To ' + CONVERT(VARCHAR(20), Max(RectificationTime), 106)
             when @DetailType = 3 then CONVERT(VARCHAR(20), Min(RectificationTime), 106)  
             else ''
             end												      AS [Date]
        ,Case when @DetailType = 3 then CONVERT(VARCHAR(20), Min(RectificationTime), 108) + ' To ' + CONVERT(VARCHAR(20), Max(RectificationTime), 108) 
              else ''
              end												      AS [Interval]
	 ,COUNT(Distinct FetchId)														AS [COMPLAINTS RECEIVED]
	 ,SUM(CLOSED)																	AS [COMPLAINTS CLOSED BY IVR]
	 ,Cast((SUM(ResponseTime)/SUM(isRsponse)) AS decimal(18,2))						AS [AVG RESPONSE TIME]
	 ,dbo.SecToTime(Avg(RectificationDIFF))											AS [AVG TAT]
	 ,Cast (Avg(TotalTries) AS decimal(18,2))										AS [AVG TOTAL NUMBER OF  OF TRIES]
	 ,SUM(SuccessCount)																AS [TOTAL NUMBER OF SUCCESSFUL ATTEMPTS	]
	 ,SUM(unSuccessCount)							                                AS [TOTAL  NUMBER OF UNSUCCESSFUL ATTEMPTS]
	 ,STR(Cast(((SUM(SuccessCount)/Sum(TotalTries))*100) AS decimal(8,2)),8,2)+ '%' AS [AVERAGE # OF SUCCESSFUL ATTEMPTS]
	 ,STR(Cast(((SUM(SuccessCount)/Sum(TotalTries))*100) AS decimal(8,2)),8,2)+ '%' AS [SUCCES RATIO (%)]
	 
	

FROM 

(
					Select TF.FetchId
							,DATEDIFF(SECOND,Tf.DateTimeOfFetch,TF.RectificationTime)				  AS RectificationDIFF
							,TF.RectificationTime													  AS RectificationTime
							,cast(SUM (1) AS decimal(18,2)) TotalTries
							,sum(		CASE  WHEN DA.ResultId IN(5,7,8,9,10,12,13,14)THEN 0
										WHEN DA.ResultId = 4 and TF.DispositionId = 2 THEN 0
										ELSE 1 
										END)                                                          AS SuccessCount
							,sum(		CASE  WHEN DA.ResultId IN(5,7,8,9,10,12,13,14)THEN 1
											 WHEN DA.ResultId = 4 and TF.DispositionId = 2 THEN 1
										ELSE 0 
										END )                                                         AS UnSuccessCount
							,CASE  WHEN TF.DispositionID IN(1,2,3,4,5,8,10) THEN 1 ELSE 0 end		  AS closed
							,sum(CASE  WHEN DA.Resultid =1 and ISNUMERIC(DA.subcode) = 1 THEN DA.subcode ELSE 0 end)				  AS ResponseTime
							,sum(CASE  WHEN DA.Resultid =1 THEN 1 ELSE 0 end)						  AS isRsponse
			from DialAttempts DA
			inner join TicketFetch  TF on DA.FetchId = tf.FetchId
			Inner join Dispositions DP on tf.DispositionId = DP.DispositionId
			inner join DialResult   DR on DA.ResultId = DR.ResultId
			WHERE 
			RectificationTime >= @StartDate
			and
     		RectificationTime <= @EndDate
			and 
			Tf.DispositionId !=  12

			group by TF.FetchId,Tf.DateTimeOfFetch,TF.RectificationTime,TF.DispositionId
)  as FetchDetail

GROUP BY
 CASE  WHEN @DetailType > 0  THEN  Year(RectificationTime)  end , -- For Monthly (1)
 CASE  WHEN @DetailType > 0  THEN  Month(RectificationTime) end , -- For Monthly (1)
 CASE  WHEN @DetailType > 1  THEN  Day(RectificationTime) end ,   -- For Daily   (2)
 CASE  WHEN @DetailType > 2  THEN  DatePart(Hour,RectificationTime)end -- For Hourly(3)
Order by MIN(RectificationTime) ASC
END
Posted
Updated 19-Apr-13 2:01am
v2
Comments
ZurdoDev 19-Apr-13 8:12am    
That's a lot of SQL. I would suggest removing parts of it until you find it. The error says that you are trying to put too big a number into an int.
CHill60 19-Apr-13 11:47am    
virtual +5 (again)
Joezer BH 22-Apr-13 3:25am    
Did you try to debug?
It often helps...

1 solution

First of all, find out where the error occurs - i.e. in your C# program or on the SQL Server: it is from SQL Server, so your question is tagged wrongly.
For a solution, see e.g.
http://stackoverflow.com/questions/8289310/arithmetic-overflow-error-converting-expression-to-data-type-int[^]
I cannot tell you which of your SUMs requires a bigint instead of an int...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900