Finance Summary SQL

Sql code posted by JBG
created at 24 Mar 01:33

Edit | Back 
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
 
SELECT   sd.qbclass, 
         Sum(sd.amount)   AS invoiceamount, 
         Sum(scd1.amount) AS paymentsperiod1, 
         Sum(scd2.amount) AS paymentsperiod2, 
         Sum(scd3.amount) AS paymentsperiod3 
FROM     studentdebit AS sd 
         LEFT JOIN (SELECT studentcreditdetail.studentdebitid, 
                           studentcreditdetail.amount, 
                           studentcredit.date, 
                           credittype.credittype 
                    FROM   studentcreditdetail 
                           INNER JOIN studentcredit 
                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                AND studentcredit.status = 1   /* Successful  */ 
                                /* PERIOD 1 */ 
                                AND studentcredit.date < Now() 
                                /* PERIOD 1 */ 
                                AND studentcredit.date > Now() - INTERVAL 1 MONTH 
                           LEFT JOIN credittype 
                             USING(credittypeid)) AS scd1 
           ON sd.studentdebitid = scd1.studentdebitid 
         LEFT JOIN (SELECT studentcreditdetail.studentdebitid, 
                           studentcreditdetail.amount, 
                           studentcredit.date, 
                           credittype.credittype 
                    FROM   studentcreditdetail 
                           INNER JOIN studentcredit 
                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                AND studentcredit.status = 1   /* Successful  */ 
                                /* PERIOD 2 */ 
                                AND studentcredit.date < Now() - INTERVAL 1 MONTH 
                                /* PERIOD 2 */ 
                                AND studentcredit.date > Now() - INTERVAL 2 MONTH 
                           LEFT JOIN credittype 
                             USING(credittypeid)) AS scd2 
           ON sd.studentdebitid = scd2.studentdebitid 
         LEFT JOIN (SELECT studentcreditdetail.studentdebitid, 
                           studentcreditdetail.amount, 
                           studentcredit.date, 
                           credittype.credittype 
                    FROM   studentcreditdetail 
                           INNER JOIN studentcredit 
                             ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                AND studentcredit.status = 1   /* Successful  */ 
                                /* PERIOD 3 */ 
                                AND studentcredit.date < Now() - INTERVAL 2 MONTH 
                                /* PERIOD 3 */ 
                                AND studentcredit.date > Now() - INTERVAL 3 MONTH 
                           LEFT JOIN credittype 
                             USING(credittypeid)) AS scd3 
           ON sd.studentdebitid = scd3.studentdebitid 
WHERE    sd.obsolete = 0   /* Not Deleted */ 
         AND sd.status = 0 /* Normal      */ 
         /* Exclude Voided Invoices */ 
         AND sd.adjustsdebitid IS NULL 
         AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
                                       FROM   studentdebit 
                                       WHERE  adjustsdebitid IS NOT NULL) 
         /* FULL PERIOD */ 
         AND sd.DATE < Now() 
         /* FULL PERIOD */ 
         AND sd.DATE > Now() - INTERVAL 3 MONTH 
GROUP BY sd.qbclass 
/* Formatting only */ 
UNION ALL 
SELECT '---', 
       '---', 
       '---', 
       '---', 
       '---' 
/* Payment Types Summary */ 
UNION ALL 
SELECT   credittype, 
         invoiceamount, 
         Sum(paymentsperiod1), 
         Sum(paymentsperiod2), 
         Sum(paymentsperiod3) 
FROM     (SELECT   scd.credittype, 
                   ''              AS invoiceamount, 
                   Sum(scd.amount) AS paymentsperiod1, 
                   ''              AS paymentsperiod2, 
                   ''              AS paymentsperiod3 
          FROM     studentdebit AS sd 
                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
                                      studentcreditdetail.amount, 
                                      studentcredit.date, 
                                      credittype.credittype 
                               FROM   studentcreditdetail 
                                      INNER JOIN studentcredit 
                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                           AND studentcredit.status = 1   /* Successful  */ 
                                           /* PERIOD 1 */ 
                                           AND studentcredit.date < Now() 
                                           /* PERIOD 1 */ 
                                           AND studentcredit.date > Now() - INTERVAL 1 MONTH 
                                      LEFT JOIN credittype 
                                        USING(credittypeid)) AS scd 
                     ON sd.studentdebitid = scd.studentdebitid 
          WHERE    sd.obsolete = 0   /* Not Deleted */ 
                   AND sd.status = 0 /* Normal      */ 
                   /* Exclude Voided Invoices */ 
                   AND sd.adjustsdebitid IS NULL 
                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
                                                 FROM   studentdebit 
                                                 WHERE  adjustsdebitid IS NOT NULL) 
                   AND sd.DATE < Now() 
                   AND sd.DATE > Now() - INTERVAL 3 MONTH 
          GROUP BY scd.credittype 
          UNION ALL 
          SELECT   scd.credittype, 
                   ''              AS invoiceamount, 
                   ''              AS paymentsperiod1, 
                   Sum(scd.amount) AS paymentsperiod2, 
                   ''              AS paymentsperiod3 
          FROM     studentdebit AS sd 
                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
                                      studentcreditdetail.amount, 
                                      studentcredit.date, 
                                      credittype.credittype 
                               FROM   studentcreditdetail 
                                      INNER JOIN studentcredit 
                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                           AND studentcredit.status = 1   /* Successful  */ 
                                           /* PERIOD 2 */ 
                                           AND studentcredit.date < Now() - INTERVAL 1 MONTH 
                                           /* PERIOD 2 */ 
                                           AND studentcredit.date > Now() - INTERVAL 2 MONTH 
                                      LEFT JOIN credittype 
                                        USING(credittypeid)) AS scd 
                     ON sd.studentdebitid = scd.studentdebitid 
          WHERE    sd.obsolete = 0   /* Not Deleted */ 
                   AND sd.status = 0 /* Normal      */ 
                   /* Exclude Voided Invoices */ 
                   AND sd.adjustsdebitid IS NULL 
                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
                                                 FROM   studentdebit 
                                                 WHERE  adjustsdebitid IS NOT NULL) 
                   AND sd.DATE < Now() 
                   AND sd.DATE > Now() - INTERVAL 3 MONTH 
          GROUP BY scd.credittype 
          UNION ALL 
          SELECT   scd.credittype, 
                   ''              AS invoiceamount, 
                   ''              AS paymentsperiod1, 
                   ''              AS paymentsperiod2, 
                   Sum(scd.amount) AS paymentsperiod3 
          FROM     studentdebit AS sd 
                   INNER JOIN (SELECT studentcreditdetail.studentdebitid, 
                                      studentcreditdetail.amount, 
                                      studentcredit.date, 
                                      credittype.credittype 
                               FROM   studentcreditdetail 
                                      INNER JOIN studentcredit 
                                        ON studentcreditdetail.studentcreditid = studentcredit.studentcreditid 
                                           AND studentcredit.obsolete = 0 /* Not Deleted */ 
                                           AND studentcredit.status = 1   /* Successful  */ 
                                           /* PERIOD 3 */ 
                                           AND studentcredit.date < Now() - INTERVAL 2 MONTH 
                                           /* PERIOD 3 */ 
                                           AND studentcredit.date > Now() - INTERVAL 3 MONTH 
                                      LEFT JOIN credittype 
                                        USING(credittypeid)) AS scd 
                     ON sd.studentdebitid = scd.studentdebitid 
          WHERE    sd.obsolete = 0   /* Not Deleted */ 
                   AND sd.status = 0 /* Normal      */ 
                   /* Exclude Voided Invoices */ 
                   AND sd.adjustsdebitid IS NULL 
                   AND sd.studentdebitid NOT IN (SELECT adjustsdebitid 
                                                 FROM   studentdebit 
                                                 WHERE  adjustsdebitid IS NOT NULL) 
                   AND sd.date < Now() 
                   AND sd.date > Now() - INTERVAL 3 MONTH 
          GROUP BY scd.credittype) AS ct 
GROUP BY ct.credittype
10.2 KB in 8 ms with coderay