-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathQRY_TerminoDoContrato
100 lines (76 loc) · 4.25 KB
/
QRY_TerminoDoContrato
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SELECT
QRY_Termino_do_Contrato0.NomeEGTTV,
QRY_Termino_do_Contrato0.CodContratos,
QRY_Termino_do_Contrato0.NomeContrato,
QRY_Termino_do_Contrato0.SIGES,
QRY_Termino_do_Contrato0.UF,
CASE WHEN [FimProrr8]>[FimProrr7] THEN [FimProrr8]
WHEN [FimProrr7]>[FimProrr6] THEN [FimProrr7]
WHEN [FimProrr6]>[FimProrr5] THEN [FimProrr6]
WHEN [FimProrr5]>[FimProrr4] THEN [FimProrr5]
WHEN [FimProrr4]>[FimProrr3] THEN [FimProrr4]
WHEN [FimProrr3]>[FimProrr2] THEN [FimProrr3]
WHEN [FimProrr2]>[FimProrr1] THEN [FimProrr2]
WHEN [FimProrr1]>[FimProrr0] THEN [FimProrr1] ELSE [FimProrr0] END AS DataFimContrato,
QRY_Termino_do_Contrato0.Tipo,
QRY_DuracaoContrato.Meses
FROM
(
/* ****************************************************** Início do QRY_Termino_DoContrato0 ******************************************************* */
SELECT
TBL_EGTTV.NomeEGTTV,
TBL_Contratos.CodContratos,
TBL_Contratos.NomeContrato,
TBL_Contratos.Tipo,
TBL_Contratos.Situacao,
TBL_Contratos.SIGES,
MIN(TBL_Prorrogacoes.ProrrDataInicio) AS MínDeProrrDataInicio,
MAX(CASE NumeroProrrogacao WHEN 0 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr0,
MAX(CASE NumeroProrrogacao WHEN 1 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr1,
MAX(CASE NumeroProrrogacao WHEN 2 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr2,
MAX(CASE NumeroProrrogacao WHEN 3 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr3,
MAX(CASE NumeroProrrogacao WHEN 4 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr4,
MAX(CASE NumeroProrrogacao WHEN 5 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr5,
MAX(CASE NumeroProrrogacao WHEN 6 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr6,
MAX(CASE NumeroProrrogacao WHEN 7 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr7,
MAX(CASE NumeroProrrogacao WHEN 8 THEN TBL_Prorrogacoes.ProrrDataFim ELSE 0 END) AS FimProrr8,
TBL_Contratos.UF
FROM (TBL_EGTTV INNER JOIN TBL_Contratos ON TBL_EGTTV.CodEGTTV = TBL_Contratos.CodEGTTV) INNER JOIN TBL_Prorrogacoes ON TBL_Contratos.CodContratos = TBL_Prorrogacoes.CodContratos
GROUP BY TBL_EGTTV.NomeEGTTV, TBL_Contratos.CodContratos, TBL_Contratos.NomeContrato, TBL_Contratos.Tipo, TBL_Contratos.UF, TBL_Contratos.Situacao, SIGES
/* ******************************************************* Fim do QRY_Termino_DoContrato0 ******************************************************** */
) AS QRY_Termino_do_Contrato0 INNER JOIN
(
/* ******************************************************** Início do QRY_DuracaoContrato ********************************************************* */
SELECT
TBL_Contratos.CodContratos,
TBL_Contratos.NomeContrato,
QRY1.DataInicio,
QRY2.DataFim,
CASE WHEN Day([DataInicio]) = 1 THEN
CASE
WHEN Day([DataFim]) = 28 THEN (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio])+1
WHEN Day([DataFim]) = 29 THEN (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio])+1
WHEN Day([DataFim]) = 30 THEN (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio])+1
WHEN Day([DataFim]) = 31 THEN (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio])+1
ELSE (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio]) END
ELSE (Year([DataFim])-Year([DataInicio]))*12+Month([DataFim])-Month([DataInicio])
END AS Meses
FROM
(TBL_Contratos INNER JOIN (
SELECT
TBL_Contratos.CodContratos,
Min(TBL_Prorrogacoes.ProrrDataInicio) AS DataInicio
FROM
TBL_Contratos INNER JOIN TBL_Prorrogacoes ON TBL_Contratos.CodContratos = TBL_Prorrogacoes.CodContratos
GROUP BY TBL_Contratos.CodContratos
) AS QRY1 ON TBL_Contratos.CodContratos = QRY1.CodContratos) INNER JOIN (
SELECT
TBL_Contratos.CodContratos,
Max(TBL_Prorrogacoes.ProrrDataFim) AS DataFim
FROM
TBL_Contratos INNER JOIN TBL_Prorrogacoes ON TBL_Contratos.CodContratos = TBL_Prorrogacoes.CodContratos
GROUP BY TBL_Contratos.CodContratos
) AS QRY2 ON TBL_Contratos.CodContratos = QRY2.CodContratos
/* ******************************************************* Fim do QRY_DuracaoContrato ******************************************************** */
) AS QRY_DuracaoContrato ON QRY_Termino_do_Contrato0.CodContratos = QRY_DuracaoContrato.CodContratos
ORDER BY CodContratos;