In the MySQL database I have these 3 tables:
CONTROLS: Here I make the trainings entries made. Note that you can insert multiple collaborators on the same line
EMPLOYERS:
SECTORS:
ThecontributorscolumnoftheCONTROLtableisaforeignkeyintheCOLLABORATORStable.ThesectorscolumnoftheCOLLABORATORStableisaforeignkeyoftheSECTORStable.
Iwouldlikeaselectthatwouldbringmethenumberofcontributorsbysectorwhoparticipatedinthetraining.
Itwouldlooklikethis:
You can easily get this result using group by , see this example:
Select s.nome, count(s.id) as quantidade from setores s, colaboradores c where s.id = c.SETOR Group by s.id
See the example working in SQL Findle
Friend, as you are probably using the JSON data type because of the content of the COLLABORATORS column in the CONTROLS table. You can do this .. (Note that I did not test this select, but that is the path)
SELECT
setor.nome as setor,
count(colaborador.id) as quantidade
FROM SETORES setor
JOIN COLABORADORES colaborador
ON colaborador.setor = setor.id
JOIN CONTROLES controle
ON JSON_CONTAINS(controle.colaboradores, CAST(colaborador.id as CHAR))
GROUP BY setor.id
Since you are working with JSON, it is worth looking at the documentation for some useful functions.
The most complete and secure solution I found was the following:
declare @aux_col varchar(255)
SELECT @aux_col = colaboradores FROM controles WHERE id = 1 --id do treinamento selecionado
-- esses três passos são necessários de acordo com os caracteres especiais mostrados no exemplo
select @aux_col = replace(@aux_col, '[', '')
select @aux_col = replace(@aux_col, ']', '')
select @aux_col = replace(@aux_col, '"', '')
-- esse passo é para facilitar a pesquisa, onde será sempre consultada a string ,codigo,
select @aux_col = ',' + @aux_col + ','
SELECT s.nome as setor, count(*) as quantidade
FROM colaboradores c
JOIN setores s on s.id = c.setor
WHERE LOCATE(',' + c.id + ',', @aux_col) > 0
GROUP BY s.nome
ORDER BY s.nome
Validation on locate using comma before and after id is required because if it did not exist, searching for code 1 would return true if code 11 existed, for example.
Good morning.
I have a function in VB to edit purchase documents.
Everything goes well but if you call %code% the header variables are changed, but when calling %code% always gives an error:
8169: Conversion failed when converting from a character string to uniqueidentifier.
It gives the idea that the doccompra gets lost after %code% .
Thank you for your help
%pre%When calling the %code% method with the second parameter equal to 5, it means that we will change the data related to %code% , %code% , %code% , etc. When you change data related to %code% , the %code% method will change the %code% by suggesting the next number in the series. To work around the problem, you can re-assign the %code% after calling the %code% method.
Code:
%pre%Another solution would be to call the %code% method with the second parameter equal to 2, so it only changes the entity's related data.
Code:
%pre%John
In the code you are running there are some improvements you can make, for example, as the property %code% is controlled by PRIMAVERA you do not have to do anything about it. The type of entity also does not have to be indicating, and by utlimo as Flavio Jardim said is to use parameter 2 because in reality it is only changing the entity and only this is that it has impact on other properties of the document. > %pre%
More information about the codes of this method.
%pre%