I have the following query
SELECT
C.NOMBRE,
CU.CUENTA AS [NUMERO PRODUCTO],
CU.CODIGOMENSAJE AS [CODIGO ESTADO PRODUCTO]
FROM CLIENTES C
INNER JOIN CUENTAS CU ON CU.CODIGO=C.CODIGO
Which gives me the accounts of each client and the codes that each one of the accounts has, the codes correspond to whether the accounts are blocked, inactive, etc... there are some client accounts that do not have any messages, so this field is empty
The result of that query is as follows:
As you can see the client "EMPRESA SEIYA" has 2 accounts, in one account he has 2 messages and in the other he has 3 messages, and the client "JORGE PEREZ" has a single account without any message, of course there are many more messages than can have an account, what I want is a validation so that if an account has within all the messages that have the code 11.22 then say 'CONTROLLED', if it has several but within those it has 11.22 and 41 then that says 'Insured', and if it doesn't have any or the field is NULL, tell me 'ACTIVE'
I mean I want the result to come out like this
I know that it can be done with a CASE, but when doing it I always get repeated values
if someone help me please
You can try, for example, to make a subselect with an APPLY(CROSS or OUTER depending on whether you want it to act as an inner join or as a left join), in which you make a case for when the message is 11 you assign 1 if it is 22 you assign 2 and if it is 41 you assign 3, then you do a distinct count of those values, if it has 1 or 2 then it is controlled, if it has 3 it is assured and if it has 0 then it is active.
It's the closest thing I see to the output you show you expect.