none
Update em massa RRS feed

  • Pergunta

  • Tenho 4 tabelas, Funcionário, PSaude_func, PSaude_faixaetaria e PSaude_Plano estrutura abaixo:
    sendo que preciso criar uma rotina para atualizar todos os planos da tabela PSaude_func para a faixa etária correta de acordo com a idade do funcionário.

    PSaude_plano
    id_plano (pk)
    nome

    PSaude_faixaetaria
    id_faixa (pk)
    id_plano (fk)
    nome
    idadeDe (int)
    idadeAte (int)
    valor

    PSaude_Func
    id_plano_func (pk)
    id_faixa (fk)
    id_func (fk)
    ck_optante

    Funcionario
    id_func (pk)
    nome
    dt_nascimento


    • Editado Bruno Coca Medina terça-feira, 21 de setembro de 2021 12:27 sinalizar FK e PK
    terça-feira, 21 de setembro de 2021 12:26

Respostas

  • Não sei se vai funcionar mas segue uma sugestão para testes:

    with 
        CTE_Idade as
        (
            select 
                psf.*,
                fe.id_plano,
                datediff(year, DtCadsss, current_timestamp) -
                case 
                    when 
                        dateadd
                            (year, f.dt_nascimento
                             datediff(year, f.dt_nascimento, current_timestamp), 
                             f.dt_nascimento) > current_timestamp
                        then 1 
                        else 0 
                end as idade
            from PSaude_func psf
            INNER JOIN funcionario f on psf.id_func = f.id_func
            INNER JOIN PSaude_faixaetaria fe on psf.id_faixa = fe.id_faixa
        ),
    
        CTE_Faixa as
        (
            select
                c.*,
                (select pe.id_faixa
                 from PSaude_faixaetaria fe
                 where
                     fe.id_plano = c.id_plano and
                     c.idade between fe.idadeDe and fe.idadeAte) id_faixa_calc
            from CTE_Idade as c
        )
    
    update CTE_Faixa
    set id_faixa = id_faixa_calc
    where id_faixa <> id_faixa_calc

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 22 de setembro de 2021 14:54

Todas as Respostas

  • Bruno,

    Quais seriam os critérios para se aplicar as atualizações?

    Acredito que você possua as definições de regra de negócio.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 21 de setembro de 2021 19:09
  •  

    Pela dt_nascimento do funcionário descobrir qual a idade;
    Sabendo a idade, identificar quais cadastros da tabela PSaude_func estão fora da faixa etária correta;
    Com isso ajustar os cadastros da tabela PSaude_func para a faixa etária de acordo com a sua idade.


    • Editado Bruno Coca Medina quarta-feira, 22 de setembro de 2021 12:04 inserir imagens
    quarta-feira, 22 de setembro de 2021 11:50
  • Ex. a funcionária ID 24, Ana Lau está na faixa etária 3 do plano 1 (De 24 a 28) porem no dia 12/02/2021 ela completou 29 anos, então deve ser alterada a categoria do plano de saúde dela para a faixa etária 4 (De 29 a 33), do plano 1 (que é o plano dela).  
    quarta-feira, 22 de setembro de 2021 12:15
  • Bruno,

    Certo! Então teremos os seguintes passos:

    1 - Calcular a Idade de cada funcionario;

    2 - Verificar de acordo com a Idade se a faixa etária em relação ao plano de saúde esta correta;

    3 - Se necessário buscar na tabela Plano de Saúde o código de acordo com a faixa etária;

    4 - Atualizar a nova faixa etária para os respectivos funcionários.

    É isso?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 22 de setembro de 2021 12:51
  • Junior Galvão,

    Exato!! 
    Isso mesmo.. 

    quarta-feira, 22 de setembro de 2021 13:01
  • Bruno, como é possível identificar o plano do funcionário?

    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 22 de setembro de 2021 13:47
  • gapimex,

    a PSaude_func e a PSaude_plano estão "lincadas" na PSaude_faixaetaria.

    eu consigo identificar o plano do funcionário da seguinte forma.

    SELECT 
    f.nome AS Func
    , p.nome AS Plano
    , fe.nome AS FaixaEtaria
    , fe.valor
    FROM PSaude_func psf
    INNER JOIN funcionario f on psf.id_func = f.id_func
    INNER JOIN PSaude_faixaetaria fe on psf.id_faixa = fe.id_faixa
    INNER JOIN PSaude_plano p ON fe.id_plano = p.id_plano




    quarta-feira, 22 de setembro de 2021 13:58
  • Não sei se vai funcionar mas segue uma sugestão para testes:

    with 
        CTE_Idade as
        (
            select 
                psf.*,
                fe.id_plano,
                datediff(year, DtCadsss, current_timestamp) -
                case 
                    when 
                        dateadd
                            (year, f.dt_nascimento
                             datediff(year, f.dt_nascimento, current_timestamp), 
                             f.dt_nascimento) > current_timestamp
                        then 1 
                        else 0 
                end as idade
            from PSaude_func psf
            INNER JOIN funcionario f on psf.id_func = f.id_func
            INNER JOIN PSaude_faixaetaria fe on psf.id_faixa = fe.id_faixa
        ),
    
        CTE_Faixa as
        (
            select
                c.*,
                (select pe.id_faixa
                 from PSaude_faixaetaria fe
                 where
                     fe.id_plano = c.id_plano and
                     c.idade between fe.idadeDe and fe.idadeAte) id_faixa_calc
            from CTE_Idade as c
        )
    
    update CTE_Faixa
    set id_faixa = id_faixa_calc
    where id_faixa <> id_faixa_calc

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 22 de setembro de 2021 14:54
  • Obrigado, ajudaram-me a acertar as tabelas)
    quarta-feira, 22 de setembro de 2021 15:30
  • Show gapimex.

    eu só fiz pequenos ajustes e alterei o modo de calcular a idade.
    mas deu tudo certo.

    muito obrigado!

    O código final ficou assim:

    WITH 
        CTE_Idade AS
        (
            SELECT 
                psf.*,
    			pl.id_plano AS id_categ,
                fe.id_plano,
                YEAR(GETDATE()) - YEAR(f.dt_nascimento) - 
    				CASE WHEN month(getdate()) < month(f.dt_nascimento) THEN 1 
    					 WHEN month(getdate()) = month(f.dt_nascimento) AND day(getdate()) < day(f.dt_nascimento) 
    					 THEN 1 ELSE 0 END AS Idade
            FROM PSaude_func psf
            INNER JOIN funcionario f ON psf.id_func = f.id_func
            INNER JOIN PSaude_faixaetaria fe ON psf.id_faixa = fe.id_faixa
    		INNER JOIN PSaude_plano pl ON fe.id_plano = pl.id_plano
    		WHERE psf.ck_optante = 1
        ),
    
        CTE_Faixa AS
        (
            SELECT
                c.*,
                (SELECT fe.id_faixa
                 FROM PSaude_faixaetaria fe
                 WHERE
                     fe.id_plano = c.id_categ AND
                     c.idade BETWEEN fe.idadeDe AND fe.idadeAte) id_faixa_calc
            FROM CTE_Idade AS c
    	)
    
    UPDATE CTE_Faixa
    SET id_faixa_etaria = id_faixa_calc
    WHERE id_faixa_etaria <> id_faixa_calc


    quarta-feira, 22 de setembro de 2021 17:17
  • Bruno,

    Que bom!

    Eu gostaria somente de propor que você tenta-se qualquer a diferença entre ano utilizando a função DateDiff() ao invês de estabelecer a fórmula fazendo uso da função Year(), justamente por uma questão de maior assertividade no resultado.

    No mais, vamos em frente.

    @Gapimex, show de bola mais uma vez a sua sugestão.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 23 de setembro de 2021 10:41