SQLTeam.com | Weblogs | Forums

Is it possible have to select with conditions WHEN and WHERE

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;

It would be soooooooooo much easier if you supplied DDL, sample data and expected results

1 Like

Hi @mike01

Fiat, Thank you for answer.
Could you show me an example:
DDL

  • ALTER
  • Collations
  • CREATE
  • DROP
  • DISABLE TRIGGER
  • ENABLE TRIGGER
  • RENAME
  • UPDATE STATISTICS

What Mike is asking of you and all others that ask help is to produce data we can use to answer your question

--this is ddl
create table #fejuad(I'd int, name varchar(50))

--this is dml
Insert into #fejuad
Select 1, "neymar' union
Select 2, 'ronaldinho' union
Select 3, 'Péle'

This will help us help you because we do not have direct access to your database and table. Otherwise it will just be a guess

1 Like
Hi @mike01

Fiat, Thank you for answer.
Could you show me an example:
DDL

* ALTER
* Collations
* CREATE
* DROP
* DISABLE TRIGGER
* ENABLE TRIGGER
* RENAME
* UPDATE STATISTICS

Please see this link
All examples are there ...

https://www.w3schools.com/sql/