SQL COM C?LCULO
Bom dia!
Estou montando um novo módulo que conterá algumas tabelas relacionada. A estrutura é bastante complexa, mas abaixo está simplificado só para eu tentar fazer o cálculo necessário.
Os registros principais terão de pegar um só valor da tabela relacionada para cada campo e somar, se o valor < tabValorCorte.Pontos, então SIG = false, se o valor >= abValorCorte.Pontos então SIG = true.
A estrutura é:
tabValorCorte
ID, IDEmpresa, Pontos (todos tipo inteiros)
tabRegistros
ID, IDEmpresa, Descricao, IDItemGrupo1, IDItemGrupo2, IDIemGrupo3, IDItemGrupo4 (Os campos ID são todos Inteiros). Aqui na verdade são 10 campos IDItemGrupo de 1 a 10, sendo que se o valor do campo for 0 (zero) siginifica que não há registro associado a ela na tabItensGrupo. Isso porque no cadastro é possÃvel definir de 1 a 10 grupos, e se não existir alguns grupos, esse campo recebe o valor zero para não fazer parte do cálculo.
tabItensGrupo
IDItem, Descricao, Pontos (ID e Pontos são tipo inteiro)
Problema:
Como os pontos de tabItensGrupo pode ser alterado, bem como o ValorCorte, não posso gravar esses valores na tabela Registros só os IDs de relacionamento. Assim tenho de fazer o cálculo no momento da consulta. Essa consulta será no formulário (edição dos registro) e gerar relatório.
1- No formulário de edição, como eu poderia listar todos os registros cujo campo SIG fosse true ou false, se esse campo não existe fisicamente na tabela e seu resultado é fruto do cálculo citado acima?
2- A consulta também pode ser na listagem do relatório, nesse caso ocorre o mesmo problema acima.
Pensei algo assim, mas não sei o calculo para gerar o SIG corretamente
Nota: No sistema antigo que tenho, esse módulo tinha o camo SIG na tabRegistros, mas se o valor de algum Item de grupo fosse alterado, este campo não teria como ser atualizado, o que certamente acarretaria erro de status. Estou agora tentando fazer o cálculo na hora da pesquisa, e isso que estou com dificuldade.
Alguém saberia como fazer esse SQL? Uso SQL Server 2008 R2 Express
Estou montando um novo módulo que conterá algumas tabelas relacionada. A estrutura é bastante complexa, mas abaixo está simplificado só para eu tentar fazer o cálculo necessário.
Os registros principais terão de pegar um só valor da tabela relacionada para cada campo e somar, se o valor < tabValorCorte.Pontos, então SIG = false, se o valor >= abValorCorte.Pontos então SIG = true.
A estrutura é:
tabValorCorte
ID, IDEmpresa, Pontos (todos tipo inteiros)
tabRegistros
ID, IDEmpresa, Descricao, IDItemGrupo1, IDItemGrupo2, IDIemGrupo3, IDItemGrupo4 (Os campos ID são todos Inteiros). Aqui na verdade são 10 campos IDItemGrupo de 1 a 10, sendo que se o valor do campo for 0 (zero) siginifica que não há registro associado a ela na tabItensGrupo. Isso porque no cadastro é possÃvel definir de 1 a 10 grupos, e se não existir alguns grupos, esse campo recebe o valor zero para não fazer parte do cálculo.
tabItensGrupo
IDItem, Descricao, Pontos (ID e Pontos são tipo inteiro)
Problema:
Como os pontos de tabItensGrupo pode ser alterado, bem como o ValorCorte, não posso gravar esses valores na tabela Registros só os IDs de relacionamento. Assim tenho de fazer o cálculo no momento da consulta. Essa consulta será no formulário (edição dos registro) e gerar relatório.
1- No formulário de edição, como eu poderia listar todos os registros cujo campo SIG fosse true ou false, se esse campo não existe fisicamente na tabela e seu resultado é fruto do cálculo citado acima?
2- A consulta também pode ser na listagem do relatório, nesse caso ocorre o mesmo problema acima.
Pensei algo assim, mas não sei o calculo para gerar o SIG corretamente
SELECT REG.ID, REG.IDEmpresa, REG.Descricao, REG.IDItemGrupo1, ITEM1.Descricao, ITEM1.Pontos, REG.IDItemGrupo2, ITEM2.Descricao, ITEM2.Pontos, REG.IDItemGrupo3, ITEM3.Descricao, ITEM3.Pontos, REG.IDItemGrupo4, ITEM4.Descricao, ITEM4.Pontos, FROM tabRegistros as REG LEFT OUTER JOIN tabItensGrupo AS ITEM1 ON REG.IDItensGrupo1 = ITEM1.IDITEM LEFT OUTER JOIN tabItensGrupo AS ITEM2 ON REG.IDItensGrupo2 = ITEM2.IDITEM LEFT OUTER JOIN tabItensGrupo AS ITEM3 ON REG.IDItensGrupo3 = ITEM3.IDITEM LEFT OUTER JOIN tabItensGrupo AS ITEM4 ON REG.IDItensGrupo4 = ITEM4.IDITEM WHERE ((REG.IDEmpresa)=@IDEmpresa) ORDER BY REG.Descricao ASC;Nota: No sistema antigo que tenho, esse módulo tinha o camo SIG na tabRegistros, mas se o valor de algum Item de grupo fosse alterado, este campo não teria como ser atualizado, o que certamente acarretaria erro de status. Estou agora tentando fazer o cálculo na hora da pesquisa, e isso que estou com dificuldade.
Alguém saberia como fazer esse SQL? Uso SQL Server 2008 R2 Express
LUIS HERRERA,
P: 1- No formulário de edição, como eu poderia listar todos os registros cujo campo SIG fosse true ou false, se esse campo não existe fisicamente na tabela e seu resultado é fruto do cálculo citado acima?
R:
Bom ... você pode declarar uma variável ou uma tabela e atribuir um valor a ela. No caso uma variável boolean (BIT que pode ser [ô]0[ô] ou [ô]1[ô]).
Exemplo de BIT:
==================================
DECLARE @teste bit;
SET @teste = 0;
select @teste
==================================
Veja mais em:
==================================
DECLARE @local_variable (Transact-SQL)
http://msdn.microsoft.com/pt-br/library/ms188927.aspx
==================================
P: 2- A consulta também pode ser na listagem do relatório, nesse caso ocorre o mesmo problema acima.
R: A consulta pode chamar uma Stored Procedure?
... Outro assunto ...
... é legal usar estas ferramentas online para formatar seu código SQL (fica visualmente melhor):
http://sqlformat.org/
http://www.sql-format.com/
[][ô]s,
Tunusat.
P: 1- No formulário de edição, como eu poderia listar todos os registros cujo campo SIG fosse true ou false, se esse campo não existe fisicamente na tabela e seu resultado é fruto do cálculo citado acima?
R:
Bom ... você pode declarar uma variável ou uma tabela e atribuir um valor a ela. No caso uma variável boolean (BIT que pode ser [ô]0[ô] ou [ô]1[ô]).
Exemplo de BIT:
==================================
DECLARE @teste bit;
SET @teste = 0;
select @teste
==================================
Veja mais em:
==================================
DECLARE @local_variable (Transact-SQL)
http://msdn.microsoft.com/pt-br/library/ms188927.aspx
==================================
P: 2- A consulta também pode ser na listagem do relatório, nesse caso ocorre o mesmo problema acima.
R: A consulta pode chamar uma Stored Procedure?
... Outro assunto ...
... é legal usar estas ferramentas online para formatar seu código SQL (fica visualmente melhor):
http://sqlformat.org/
http://www.sql-format.com/
SELECT REG.ID,
REG.IDEmpresa,
REG.Descricao,
REG.IDItemGrupo1,
ITEM1.Descricao,
ITEM1.Pontos,
REG.IDItemGrupo2,
ITEM2.Descricao,
ITEM2.Pontos,
REG.IDItemGrupo3,
ITEM3.Descricao,
ITEM3.Pontos,
REG.IDItemGrupo4,
ITEM4.Descricao,
ITEM4.Pontos,
FROM tabRegistros AS REG
LEFT OUTER JOIN tabItensGrupo AS ITEM1 ON REG.IDItensGrupo1 = ITEM1.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM2 ON REG.IDItensGrupo2 = ITEM2.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM3 ON REG.IDItensGrupo3 = ITEM3.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM4 ON REG.IDItensGrupo4 = ITEM4.IDITEM
WHERE ((REG.IDEmpresa)=@IDEmpresa)
ORDER BY REG.Descricao ASC;[][ô]s,
Tunusat.
Desculpe mas não entendi.
1) Essa declaração de variável eu faria onde? Pelo que entendi ela conteria o resultado do cálculo para cada tagRegistro encontrado, true ou false em relação a soma de cada um dos campos ItemGrupo1, 2, 3, etc...
Onde ela seria declarada e como eu atribuiria a ela o resultado do cálculo (não demonstrado) e como eu a incluiria como uma coluna para o resultado do SELECT acima?
2) No SELECT que passei só tem o esqueleto dos campos, não sem a forma de calcular a soma dos campos, nem como saber se seu conteúdo é maior que zero, pois se for zero não terá correlação com o item de tabItemGrupo?
Nota: Infelizmente não gostaria de incluir StoreProcedure visto que o sistema rodará em vários clientes e não terei acesso aos bancos de dados deste. é um sistema corporativo para n empresas, assim prefiro que tudo fique no EXE para fácil manutenção e atualização.
3) Obrigado pelos links de formatação, não conhecia este recurso.
1) Essa declaração de variável eu faria onde? Pelo que entendi ela conteria o resultado do cálculo para cada tagRegistro encontrado, true ou false em relação a soma de cada um dos campos ItemGrupo1, 2, 3, etc...
Onde ela seria declarada e como eu atribuiria a ela o resultado do cálculo (não demonstrado) e como eu a incluiria como uma coluna para o resultado do SELECT acima?
2) No SELECT que passei só tem o esqueleto dos campos, não sem a forma de calcular a soma dos campos, nem como saber se seu conteúdo é maior que zero, pois se for zero não terá correlação com o item de tabItemGrupo?
Nota: Infelizmente não gostaria de incluir StoreProcedure visto que o sistema rodará em vários clientes e não terei acesso aos bancos de dados deste. é um sistema corporativo para n empresas, assim prefiro que tudo fique no EXE para fácil manutenção e atualização.
3) Obrigado pelos links de formatação, não conhecia este recurso.
Você deve somar os pontos de cada grupo e comparar usando o CASE. Dê um alias para o CASE, veja o exemplo abaixo.
Depois bastar usar normalmente o campo, assim: dr[[Ô]resultado[Ô]]
No tabValorCorte.Pontos você pode usar uma subquery ou utilizar um parâmetro ao executar o comando ou, ainda, se a sua tabela tabValorCorte tiver alguma campo que possa ser relacionado a tabela tabRegistros.
Qualquer dúvida faça contato
Depois bastar usar normalmente o campo, assim: dr[[Ô]resultado[Ô]]
SELECT REG.ID,
CASE WHEN (ITEM1.Pontos + ITEM2.Pontos + ITEM3.Pontos + ITEM4.Pontos) < tabValorCorte.Pontos THEN 0
ELSE 1
END CASE AS resultado
FROM tabRegistros AS REG
LEFT OUTER JOIN tabItensGrupo AS ITEM1 ON REG.IDItensGrupo1 = ITEM1.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM2 ON REG.IDItensGrupo2 = ITEM2.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM3 ON REG.IDItensGrupo3 = ITEM3.IDITEM
LEFT OUTER JOIN tabItensGrupo AS ITEM4 ON REG.IDItensGrupo4 = ITEM4.IDITEM
WHERE ((REG.IDEmpresa)=@IDEmpresa)
ORDER BY REG.Descricao ASC;No tabValorCorte.Pontos você pode usar uma subquery ou utilizar um parâmetro ao executar o comando ou, ainda, se a sua tabela tabValorCorte tiver alguma campo que possa ser relacionado a tabela tabRegistros.
Qualquer dúvida faça contato
Grande amigo Fabiano, sempre quebrando meus galhos.
Ainda não tenho os dados na tabela e estou fazendo a tela de cadastro, então testei sem dados. Tive de fazer só um ajuste no case, pois a sintaxe não funcionava, e [Ô]Rodou[Ô].
Segunda termino o cadastro e incluo alguns registro pra testar e dou o retorno.
Nota: Sim a tabela de valorcorte tem um campo relacionável IDEmpresa, assim fica fácil.
o código do Case no SQL 2008 é assim:
campo = CASE WHEN (ITEM1.Pontos + ITEM2.Pontos + ITEM3.Pontos + ITEM4.Pontos) < tabValorCorte.Pontos THEN 0
ELSE 1
END
Obrigadão por enquanto e Ótimo final de semana para todos.
Ainda não tenho os dados na tabela e estou fazendo a tela de cadastro, então testei sem dados. Tive de fazer só um ajuste no case, pois a sintaxe não funcionava, e [Ô]Rodou[Ô].
Segunda termino o cadastro e incluo alguns registro pra testar e dou o retorno.
Nota: Sim a tabela de valorcorte tem um campo relacionável IDEmpresa, assim fica fácil.
o código do Case no SQL 2008 é assim:
campo = CASE WHEN (ITEM1.Pontos + ITEM2.Pontos + ITEM3.Pontos + ITEM4.Pontos) < tabValorCorte.Pontos THEN 0
ELSE 1
END
Obrigadão por enquanto e Ótimo final de semana para todos.
Veja se ajuda minha ideia
Primeiro mudaria a estrutura de tabela, ficaria assim:
Os grupos ficariam fora da tabela de Registro, em uma tabela chamada tabRegistrosGrupos, portanto podendo ter mais de 10 e não poluindo a tabela. A quantidade pode ser limitada via aplicação.
E o SQL ficaria assim:
Não realizei testes, mas creio que vai funcionar
Primeiro mudaria a estrutura de tabela, ficaria assim:
CREATE TABLE tabValorCorte (
ID INT,
IDEmpresa INT,
Pontos INT
);
CREATE TABLE tabRegistros (
ID INT,
IDEmpresa INT,
Descricao VARCHAR(50)
);
CREATE TABLE tabRegistrosGrupos (
IDRegistro INT,
IDEmpresa INT,
IDItemGrupo INT
);
CREATE TABLE tabItensGrupo (
IDItem INT,
Descricao VARCHAR(50),
Pontos INT
);Os grupos ficariam fora da tabela de Registro, em uma tabela chamada tabRegistrosGrupos, portanto podendo ter mais de 10 e não poluindo a tabela. A quantidade pode ser limitada via aplicação.
E o SQL ficaria assim:
SELECT
A.RE_ID,
A.RE_DESCRICAO,
A.REG_IDITEMGRUPO,
A.IG_DESCRICAO,
A.IG_PONTOS,
CASE
WHEN A.IG_PONTOS < VC.Pontos THEN [ô]FALSO[ô]
ELSE [ô]VERDADEIRO[ô]
END AS SIG
FROM (
SELECT
RE.ID AS RE_ID,
RE.IDEmpresa AS RE_IDEMPRESA,
RE.Descricao AS RE_DESCRICAO,
REG.IDItemGrupo AS REG_IDITEMGRUPO,
IG.Descricao AS IG_DESCRICAO,
COALESCE(SUM(IG.Pontos),0) AS IG_PONTOS
FROM tabRegistros RE
LEFT JOIN tabRegistrosGrupos REG ON (REG.IDRegistro = RE.ID AND REG.IDEmpresa = RE.IDEmpresa)
LEFT JOIN tabItensGrupo IG ON (IG.IDItem = REG.IDItemGrupo)
WHERE RE.IDEmpresa = @IDEmpresa
GROUP BY RE.ID,RE.IDEmpresa,RE.Descricao,REG.IDItemGrupo,IG.Descricao
) A
INNER JOIN tabValorCorte VC ON (VC.IDEmpresa = A.RE_IDEMPRESA)
ORDER BY A.RE_DESCRICAO
Não realizei testes, mas creio que vai funcionar
Tópico encerrado , respostas não são mais permitidas