Case/when, help us

Asked

Viewed 68 times

2

I did a search and I couldn’t find a logic to use in this case.

the table columns are: COD_DO_USUARIO_ONE | COD_DO_USUARIO_TWO | TEXT

I’m basically trying to put together a code that can identify who was the first to send a certain message in a forum’s private chat ...

I thought of the following Code (which is working)

case     
    when lower(texto) like '%oi, tudo bem?%' THEN 'user1'
    else 'user2'
    end AS usuario_forum

This returns me the COD of who sent the message, however, problem is as follows:

1 - the text may have been fired either skin user_one or user_two, so how was it to know who was the first to speak?

for example: user 1 - Hi, all right? user 2 - all

logic 2 user 1 - hi user 2 - Hi, all right?

As the logic of the first Cod was incorrect think I modified it and stayed as follows:

case
when lower(texto) like '%oi, tudo bem?%' = COD_DO_USUARIO_ONE THEN 'user1'
else 'user2'
end AS usuario_forum

does not work because the user code is not equal to the mentioned text, so I cannot match the 2

tried a IF no select, but it did not work for the same reason.

Does anyone have any idea how I can create bring that column with the right data?

sql case when

  • This you have put does not have any sense. What do you want to compare with COD_DO_USUARIO_ENVIO? Maybe you have thought of chaining two case/when?

  • So, I know the logic is wrong, I can’t match the COD_DO_USUARIO_ONE with the TEXT ... but how do I know who sent the message ? whether user 1 or user 2?

  • I believe that you will only be able to identify who first sent such text if your table also includes a timestamp.

  • Not necessarily, imagine the following text like "hi, okay" ... now the following conversation: user_one: hi user_two: hi, all right? who sent was the two

  • But the idea was good, in what I want should came the bulk of everything, in war some soldiers always leave wounded rsrs if you have any other idea, last case I will use the one you said even.

1 answer

1


From what I understand, it would be something like this:

DECLARE @TABELA_TEMP TABLE
(
    COD_DO_USUARIO INT
    , TEXTO VARCHAR(MAX)
    , DATA_ENVIO DATETIME
)

INSERT INTO @TABELA_TEMP VALUES (1, 'mensagem 1', '2019-02-13 18:12')
INSERT INTO @TABELA_TEMP VALUES (1, 'mensagem 2', '2019-02-13 18:11')
INSERT INTO @TABELA_TEMP VALUES (2, 'mensagem 1', '2019-02-13 18:11')
INSERT INTO @TABELA_TEMP VALUES (2, 'mensagem 2', '2019-02-13 18:16')
INSERT INTO @TABELA_TEMP VALUES (3, 'mensagem 1', '2019-02-13 18:15')
INSERT INTO @TABELA_TEMP VALUES (3, 'mensagem 2', '2019-02-13 18:10')

SELECT TOP 1 MAX(DATA_ENVIO) DATA_ENVIO, COD_DO_USUARIO FROM @TABELA_TEMP WHERE TEXTO = 'mensagem 2' GROUP BY COD_DO_USUARIO ORDER BY DATA_ENVIO DESC

Browser other questions tagged sql case when

You are not signed in. Login or sign up in order to post.