Soluções para relatórios gerenciais (T-SQL)

O objetivo desse artigo é criar soluções para diversos tipos de relatórios.

Vamos a um exemplo:

O Usuário solicitou um relatório com as seguintes informações do funcionário:
Nome, matrícula, salário inicial, salário atual, salário anterior

Vou passar uma solução utilizando, funções analíticas, CTE e funções de agregação…

use GUIADBA;
Go
if object_id('tb_Funcionario')is not null
Drop Table tb_Funcionario
Go
Create table tb_Funcionario
(
id_Funcionario int identity,
nm_Funcionario varchar(175) not null,
nu_Matricula varchar(10) not null,
dt_Cadastro datetime not null default getdate()
);
Go
Insert into tb_Funcionario (nm_Funcionario,nu_Matricula)
Select 'Alexandre Oliveira', '0001'
union
Select 'Carolina', '0004'
union
Select 'Joãozinho', '0003'
union
Select 'Mariazinha', '0002'
Select * From tb_Funcionario

if object_id('tb_Funcionario_Remuneracao')is not null
Drop Table tb_Funcionario_Remuneracao
Go
Create table tb_Funcionario_Remuneracao
(
id_Funcionario_Remuneracao int identity,
id_Funcionario int not null,
vl_Remuneracao money not null
);
Go
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 1,'1000';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 1,'1500';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 1,'2700';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 2,'980';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 2,'1350';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 3,'10000';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 3,'12005';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 3,'13300';
Insert into tb_Funcionario_Remuneracao (id_Funcionario,vl_Remuneracao)
Select 3,'14300';
Select * From tb_Funcionario_Remuneracao ;

Para gerar esse relatório, usarei a função analítica LAG que retorna o registro da linha anterior.
Está função está disponível no SQL SERVER 2012 e versões posteriores.

Criarei uma CTE (Common table Expression) que tem a finalidade de armazenar temporariamente o resultado de uma determinada consulta e no caso em questão, com o objetivo de armazenar o resultado de uma consulta onde fiz o uso da função LAG para retornar o salário anterior ao atual.

Em seguida farei um join da minha CTE com as tabelas tb_Funcionario_Remuneracao e tb_Funcionario, para retornar o salário inicial e atual através das funções de agregação min() e max(), nome do funcionário e matrícula.
Vejam com o ficou…

With CTE (vl_Remuneracao,id_Funcionario_Remuneracao,id_Funcionario)
As(
Select LAG(vl_Remuneracao)Over (Partition by id_Funcionario Order by id_Funcionario),id_Funcionario_Remuneracao,id_Funcionario
From tb_Funcionario_Remuneracao
Group by id_Funcionario,vl_Remuneracao,id_Funcionario_Remuneracao,id_Funcionario)
Select c.nm_Funcionario,c.nu_Matricula,Salario_Inicial = min(b.vl_Remuneracao), Salario_Atual = max(b.vl_Remuneracao), Salario_Anterior= max(a.vl_Remuneracao)
From CTE a
Inner Join tb_Funcionario_Remuneracao b
On a.id_Funcionario_Remuneracao = b.id_Funcionario_Remuneracao
And a.id_Funcionario = b.id_Funcionario
Inner Join tb_Funcionario c
On c.id_Funcionario = a.id_Funcionario
Group by c.nm_Funcionario,c.nu_Matricula
Go