Please Help me As I want to get data for more than a month it throws this Exception..
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
ALTER PROCEDURE [dbo].[Usp_QueueMISReport]
@StartDate varchar(20),
@EndDate varchar(20),
@DetailType int =0
AS
BEGIN
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 ,
CASE WHEN @DetailType > 0 THEN Month(RectificationTime) end ,
CASE WHEN @DetailType > 1 THEN Day(RectificationTime) end ,
CASE WHEN @DetailType > 2 THEN DatePart(Hour,RectificationTime)end
Order by MIN(RectificationTime) ASC
END