A B
1 Date 2010/08/27
2 Copy1 =B1
3 Copy2 =B1
SELECT t1.tb2_num_empregado * 1,
t1.tb2_fnc_cod_funcao,
t2.tb2_fnc_cod_funcao,
r1.trb_cct_cod_centro_custo,
r1.trb_cct_cod_centro_custo,
t1.tb2_arz_cod_accao
FROM rh_trbt0 AS r1
INNER JOIN (SELECT trb_emp_empresa,
trb_num_empregado,
MAX(trb_dt_efectiva) AS [md]
FROM rh_trbt0
WHERE trb_dt_efectiva < '20100716'
GROUP BY trb_emp_empresa,
trb_num_empregado
) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa
AND r1.trb_num_empregado = r2.trb_num_empregado
AND r1.trb_dt_efectiva = r2.md,
rh_tb2t0 AS t1
INNER JOIN (SELECT tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao,
MAX(tb2_dt_efectiva) AS [md]
FROM rh_tb2t0
WHERE tb2_dt_efectiva < '20100716'
GROUP BY tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao
) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa
AND t1.tb2_num_empregado = t2.tb2_num_empregado
AND t1.tb2_dt_efectiva > t2.md
WHERE tb2_dt_efectiva > '20100615'
AND tb2_arz_cod_accao = 'PRO'
AND tb2_arz_cod_razao = '002'
AND t1.tb2_emp_empresa = '32'
AND r1.trb_emp_empresa = '32'
AND r1.trb_num_empregado = t1.tb2_num_empregado
AND NOT EXISTS ( SELECT t3.tb2_num_empregado
FROM rh_tb2t0 AS t3
WHERE t3.tb2_emp_empresa = '32'
AND t3.tb2_num_empregado = t1.tb2_num_empregado
AND t3.tb2_dt_efectiva > t2.md
AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )
AND r1.trb_dep_departamento = '9000'
UNION ALL
SELECT t1.tb2_num_empregado * 1,
t1.tb2_fnc_cod_funcao,
t2.tb2_fnc_cod_funcao,
r1.trb_dep_departamento,
r1.trb_dep_departamento,
t1.tb2_arz_cod_accao
FROM rh_trbt0 AS r1
INNER JOIN (SELECT trb_emp_empresa,
trb_num_empregado,
MAX(trb_dt_efectiva) AS [md]
FROM rh_trbt0
WHERE trb_dt_efectiva < '20100716'
GROUP BY trb_emp_empresa,
trb_num_empregado
) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa
AND r1.trb_num_empregado = r2.trb_num_empregado
AND r1.trb_dt_efectiva = r2.md,
rh_tb2t0 AS t1
INNER JOIN (SELECT tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao,
MAX(tb2_dt_efectiva) AS [md]
FROM rh_tb2t0
WHERE tb2_dt_efectiva < '20100716'
GROUP BY tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao
) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa
AND t1.tb2_num_empregado = t2.tb2_num_empregado
AND t1.tb2_dt_efectiva > t2.md
WHERE tb2_dt_efectiva > '20100615'
AND tb2_arz_cod_accao = 'PRO'
AND tb2_arz_cod_razao = '002'
AND t1.tb2_emp_empresa = '32'
AND r1.trb_emp_empresa = '32'
AND r1.trb_num_empregado = t1.tb2_num_empregado
AND NOT EXISTS ( SELECT t3.tb2_num_empregado
FROM rh_tb2t0 AS t3
WHERE t3.tb2_emp_empresa = '32'
AND t3.tb2_num_empregado = t1.tb2_num_empregado
AND t3.tb2_dt_efectiva > t2.md
AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )
AND r1.trb_dep_departamento <> '9000'
ORDER BY 1
CREATE PROCEDURE usp_Suafuncao
@dt_efectiva1 datetime, -- Change the data types appropriately
@dt_efectiva2 datetime,
@arz_cod_accao varchar(3),
@arz_cod_razao varchar(3),
@emp_empresa varchar(2),
@dep_departamento varchar(4)
AS
SET NOCOUNT ON
SELECT t1.tb2_num_empregado * 1,
t1.tb2_fnc_cod_funcao,
t2.tb2_fnc_cod_funcao,
r1.trb_cct_cod_centro_custo,
r1.trb_cct_cod_centro_custo,
t1.tb2_arz_cod_accao
FROM rh_trbt0 AS r1
INNER JOIN (SELECT trb_emp_empresa,
trb_num_empregado,
MAX(trb_dt_efectiva) AS [md]
FROM rh_trbt0
WHERE trb_dt_efectiva < @dt_efectiva1 -- '20100716'
GROUP BY trb_emp_empresa,
trb_num_empregado
) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa
AND r1.trb_num_empregado = r2.trb_num_empregado
AND r1.trb_dt_efectiva = r2.md,
rh_tb2t0 AS t1
INNER JOIN (SELECT tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao,
MAX(tb2_dt_efectiva) AS [md]
FROM rh_tb2t0
WHERE tb2_dt_efectiva < @dt_efectiva1 -- '20100716'
GROUP BY tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao
) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa
AND t1.tb2_num_empregado = t2.tb2_num_empregado
AND t1.tb2_dt_efectiva > t2.md
WHERE tb2_dt_efectiva > @dt_efectiva2 -- '20100615'
AND tb2_arz_cod_accao = @arz_cod_accao -- 'PRO'
AND tb2_arz_cod_razao = @arz_cod_razao -- '002'
AND t1.tb2_emp_empresa = @emp_empresa -- '32'
AND r1.trb_emp_empresa = @emp_empresa -- '32'
AND r1.trb_num_empregado = t1.tb2_num_empregado
AND NOT EXISTS ( SELECT t3.tb2_num_empregado
FROM rh_tb2t0 AS t3
WHERE t3.tb2_emp_empresa = @emp_empresa -- '32'
AND t3.tb2_num_empregado = t1.tb2_num_empregado
AND t3.tb2_dt_efectiva > t2.md
AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )
AND r1.trb_dep_departamento = @dep_departamento -- '9000'
UNION ALL
SELECT t1.tb2_num_empregado * 1,
t1.tb2_fnc_cod_funcao,
t2.tb2_fnc_cod_funcao,
r1.trb_dep_departamento,
r1.trb_dep_departamento,
t1.tb2_arz_cod_accao
FROM rh_trbt0 AS r1
INNER JOIN (SELECT trb_emp_empresa,
trb_num_empregado,
MAX(trb_dt_efectiva) AS [md]
FROM rh_trbt0
WHERE trb_dt_efectiva < @dt_efectiva1 -- '20100716'
GROUP BY trb_emp_empresa,
trb_num_empregado
) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa
AND r1.trb_num_empregado = r2.trb_num_empregado
AND r1.trb_dt_efectiva = r2.md,
rh_tb2t0 AS t1
INNER JOIN (SELECT tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao,
MAX(tb2_dt_efectiva) AS [md]
FROM rh_tb2t0
WHERE tb2_dt_efectiva < @dt_efectiva1 -- '20100716'
GROUP BY tb2_emp_empresa,
tb2_num_empregado,
tb2_fnc_cod_funcao
) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa
AND t1.tb2_num_empregado = t2.tb2_num_empregado
AND t1.tb2_dt_efectiva > t2.md
WHERE tb2_dt_efectiva > @dt_efectiva2 -- '20100615'
AND tb2_arz_cod_accao = @arz_cod_accao -- 'PRO'
AND tb2_arz_cod_razao = @arz_cod_razao -- '002'
AND t1.tb2_emp_empresa = tb2_emp_empresa -- '32'
AND r1.trb_emp_empresa = tb2_emp_empresa -- '32'
AND r1.trb_num_empregado = t1.tb2_num_empregado
AND NOT EXISTS ( SELECT t3.tb2_num_empregado
FROM rh_tb2t0 AS t3
WHERE t3.tb2_emp_empresa = tb2_emp_empresa -- '32'
AND t3.tb2_num_empregado = t1.tb2_num_empregado
AND t3.tb2_dt_efectiva > t2.md
AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )
AND r1.trb_dep_departamento <> @dep_departamento -- '9000'
ORDER BY 1
strSQL = "EXEC usp_ContageMeDep CONVERT(datetime, " & Range("A1").Value &", 126) , '" & Range("B1").Value & "'"
Title | # Comments | Views | Activity |
---|---|---|---|
Excel | 6 | 18 | |
Convert .PDF | 6 | 38 | |
Index/Match with Multiple Criteria | 2 | 14 | |
TT Auto DashBoard | 4 | 12 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
17 Experts available now in Live!