Then you create the connection
to the database.
Next is the SQL query that pulls the report from the database:
SELECT WEEK(p1.date) as week_number,
YEAR(p1.date) as year,
COALESCE((SELECT SUM(ABS(p2.gross))
FROM paypal_transactions as p2
WHERE (WEEK(p2.date) = WEEK(p1.date)
AND YEAR(p2.date) = YEAR(p1.date)
AND WEEKDAY(p2.date) IN (5,6)
AND p2.gross<0
AND p2.status='Completed')
),0) as weekend_amount,
COALESCE((SELECT SUM(abs(p3.gross))
FROM paypal_transactions as p3
WHERE (WEEK(p3.date) = WEEK(p1.date)
AND YEAR(p3.date) = YEAR(p1.date)
AND WEEKDAY(p3.date) NOT IN (5,6)
AND p3.gross<0
AND p3.status='Completed')
),0) as weekday_amount
CHAPTER 7 n TRACKING EXPENDITURES WITH PAYPAL 149
FROM paypal_transactions as p1
GROUP BY YEAR(date) ASC, WEEK(date) ASC;
This query groups the transactions by year and by date. This means that you??™ll get
one row for every week/year combination. From there, the query uses two correlated
subqueries to locate the total amount of money that was spent during the week and during
the weekend.
Pages:
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218