-
Notifications
You must be signed in to change notification settings - Fork 2
/
SQL004.sql
122 lines (110 loc) · 3.94 KB
/
SQL004.sql
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
/*
PLT011 - Script de exibição das parcelas de aluno
Create By Pablo Camilo
(04/08/2015)
O objetivo deste SQL é buscar todas as parcelas de um determinado período
1 para verificar se o sistema realmente realizava estas atividades.
CONCLUSÃO: Nunca essas atividades foram realizadas de forma automatica pelo sistema.
PARAMETROS: IDPERLET
*/
SELECT
SP.CODCONTRATO, SP.RA,
CAST(ISNULL(
F.VALORORIGINAL
,0) AS DECIMAL(18,2)) AS PARCELA1,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON (SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND
SPARCELA.PARCELA = '2' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA2,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON (SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND
SPARCELA.PARCELA = '3' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA3,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON ( SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND SPARCELA.PARCELA = '4' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA4,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON ( SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND
SPARCELA.PARCELA = '5' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA5,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON ( SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND
SPARCELA.PARCELA = '6' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA6,
CAST(ISNULL((
SELECT
FLAN.VALORORIGINAL
FROM
FLAN
INNER JOIN SLAN ON ( FLAN.IDLAN = SLAN.IDLAN AND FLAN.CODCOLIGADA = SLAN.CODCOLIGADA )
INNER JOIN SPARCELA ON (SPARCELA.CODCOLIGADA = SLAN.CODCOLIGADA AND SPARCELA.IDPARCELA = SLAN.IDPARCELA )
WHERE
SPARCELA.RA = SP.RA AND
SPARCELA.IDPERLET = SP.IDPERLET AND
FLAN.CODCFO <>'3000' AND
SPARCELA.PARCELA = '7' AND
SPARCELA.CODCONTRATO = SP.CODCONTRATO AND
SPARCELA.CODSERVICO = SP.CODSERVICO
),0) AS DECIMAL(18,2)) AS PARCELA7
FROM
FLAN F
INNER JOIN SLAN S ON ( F.IDLAN = S.IDLAN AND F.CODCOLIGADA = S.CODCOLIGADA )
INNER JOIN SPARCELA SP ON ( SP.CODCOLIGADA = S.CODCOLIGADA AND SP.IDPARCELA = S.IDPARCELA )
WHERE
SP.IDPERLET ='6' AND
SP.PARCELA ='1'