It is possible to perform two select with time range condition and present the result in a side column along with the other columns in the list.
Need check the table 1 select information and check the condition (When agent_state and sequence is not ready,reserved,talking,work ) to check the agent_state result from table 2 all comparing the date and time to matches. Table 1 have a sequence (ready, reserved, talking and work) and it is okay no need change the state, when happens (not ready, reserved, talking, work) the information should be consult the second table 2 and use the result from table 2 column agent_state to table 1 column agent_state.
For Example:
SELECT
agent_name,
agent_login_id,
agent_extension,
transition_time,
agent_state,
reason_code,
duration,
latestsynchedtime,
ccx_report_id,
CASE
WHEN agent_state ='Logged-in' THEN 'Logado'
WHEN agent_state ='Not Ready' THEN 'Nao disponivel'
WHEN agent_state ='Ready' THEN 'Disponivel'
WHEN agent_state ='Reserved' THEN 'Reservado'
WHEN agent_state ='Talking' THEN 'Em atendimento'
WHEN agent_state ='Work' THEN 'Trabalho'
WHEN agent_state ='Logout' THEN 'Deslogado'
ELSE agent_state
END AS agentstate,
CASE
WHEN agent_state ='Logged-in' THEN 'Logado'
WHEN agent_state ='Ready' THEN 'Disponivel'
WHEN agent_state ='Reserved' THEN 'Reservado'
WHEN agent_state ='Talking' THEN 'Em atendimento'
WHEN agent_state ='Work' THEN 'Trabalho'
ELSE reason_code
END AS reasoncode
FROM dbo.sp_agent_state_detail
SELECT agent_name,
agent_login_id,
agent_extension,
agent_state,
call_start_time,
call_end_time,
call_duration,
called_number,
calling_number,
csq, skills,
talk_time,
wrapup_time,
type_call,
call_abandon_time
FROM dbo.sp_agent_detail_ccx;