select
convert(varchar, calldate, 103) as 'DATE',
    case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 15 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) < 30
then convert(varchar(2), calldate, 108) + ':15'
else
case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 30 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) < 45
then convert(varchar(2), calldate, 108) + ':30'
else
case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 45 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) <= 59
then convert(varchar(2), calldate, 108) + ':45'
else
    convert(varchar(2), calldate, 108) + ':00'
end end end as 'TIME',
convert(int, sum(
    case when direction = 'I'
    and transfer = '0'
    then 1
    else 0 end)) as OFFERED,
convert(int, sum(
    case when direction = 'I'
    and transfer = '0'
    and unanswer = '0'
    then 1
    else 0 end)) as ANSWERED,
convert(int, sum(
    case when direction = 'I'
    and transfer = '0'
    and unanswer = '1'
    then 1
    else 0 end)) as ABANDONED,
convert(int, sum(
    case when direction = 'I'
    then duration
    else 0 end) / (sum(
    case when direction = 'I'
    then 1
    else 0 end) + 0.0000001)) as AVGHANDLETIME,
convert(int, sum(
    case when direction = 'I'
    and transfer = '0'
    then ringtime
    else 0 end) / (sum(
    case when direction = 'I'
    and transfer = '0'
    then 1
    else 0 end) + 0.00001)) as AVG_SPEED_OF_ANSWER,
round(((sum(
    case when unanswer = '0'
    and ringtime <= 20 then 1
    else 0 end) * 100) / ((sum(1) + 0.00001) * 100) * 100), 0) as gradeofservice

from tmsdata, tbl_extensions, tbl_departments

where convert(date, calldate) = convert(date, getdate())
and tbl_extensions.dept_id = tbl_departments.dept_id and(tbl_departments.dept_id = '1'
    or tmsdata.extno = '9999') and tmsdata.extno = tbl_extensions.extno

group by convert(varchar, calldate, 103),
case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 15 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) < 30
then convert(varchar(2), calldate, 108) + ':15'
else
case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 30 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) < 45
then convert(varchar(2), calldate, 108) + ':30'
else
case when convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) >= 45 and convert(int, substring(convert(varchar(5), calldate, 108), 4, 2)) <= 59
then convert(varchar(2), calldate, 108) + ':45'
else
    convert(varchar(2), calldate, 108) + ':00'
end end end