Solved by SQL query in Oracle but failed to do so in Excel
I am giving my solution in SQL Query
Select (CASE
WHEN (cast(to_char(arrivaltime, 'mi') as int) < 30) THEN
(to_char(arrivaltime, 'hh:') || '00' || to_char(arrivaltime, ' PM') ||
' - ' || to_char(arrivaltime, 'hh:') || '29' ||
to_char(arrivaltime, ' PM'))
ELSE
(to_char(arrivaltime, 'hh:') || '29' || ' ' ||
to_char(arrivaltime, ' PM') || ' - ' ||
to_char(arrivaltime + interval '1' hour, 'hh:') || '00' ||
to_char(arrivaltime, ' PM'))
END) ServiceTime,
ArrivalDate,
name,
count(*) ServicedPeople
From (select sysdate as arrivaltime,'test' as name,trunc(sysdate) as ArrivalDate from dual)
group by (CASE
WHEN (cast(to_char(arrivaltime, 'mi') as int) < 30) THEN
(to_char(arrivaltime, 'hh:') || '00' || to_char(arrivaltime, ' PM') ||
' - ' || to_char(arrivaltime, 'hh:') || '29' ||
to_char(arrivaltime, ' PM'))
ELSE
(to_char(arrivaltime, 'hh:') || '29' || ' ' ||
to_char(arrivaltime, ' PM') || ' - ' ||
to_char(arrivaltime + interval '1' hour, 'hh:') || '00' ||
to_char(arrivaltime, ' PM'))
END, ArrivalDate, name)
order by ServiceTime
Select (to_char(arrivaltime, 'hh:') || '00' || ' ' || to_char(arrivaltime, ' PM') ||
' - ' || to_char(arrivaltime + interval '2' hour, 'hh:') || '00' ||
to_char(arrivaltime, ' PM')) ServiceTime,
ArrivalDate,
name,
count(*) ServicedPeople
From (select sysdate as arrivaltime,'test' as name,trunc(sysdate) as ArrivalDate from dual)
group by (to_char(arrivaltime, 'hh:') || '00' || ' ' ||
to_char(arrivaltime, ' PM') || ' - ' ||
to_char(arrivaltime + interval '2' hour, 'hh:') || '00' ||
to_char(arrivaltime, ' PM'), ArrivalDate, name)
order by ArrivalDate, ServiceTime
In the form clause I have used
select '','','' from dual union select '','','' from dual
and so on.
Hope this help others
Thank You all