dgj dgj - 7 months ago 13
SQL Question

MySQL Multiple Joins to a Calendar Table For Payments Data

I have an order table that looks like the below

order_id pre_pay_time pre_pay_amount pre_pay_type final_payment_time final_payment_amount final_payment_type
==============================================================================================================================
1 1234123413 10 1 1234123913 25 2
2 1234123414 25 1 0 100 0
3 1234123417 75 2 1234125416 155 1
4 0 0 0 1234126418 60 2


Here the customer can either make a pre payment on the order and then pay the remainder at the end, or they can just pay the full amount at the end.

The pre_pay_time and final_payment_time columns are UNIX timestamps.

What I'm trying to do is produce an output table that has the sum amounts for each calendar day. To do this I am joining with a calendar table.

Currently I am able to successfully output the data only for the sum of the final payment, as well as sums for cash and card payment (based on final_payment_type column) for each day of the month.

SELECT calendar.datefield AS DATE, IFNULL( SUM( orders.final_payment_amount ) , 0 ) AS total_sales,
IFNULL(sum(if(final_payment_type=1,orders.final_payment_amount,0)),0)AS total_cash,
IFNULL(sum(if(final_payment_type=2,orders.final_payment_amount,0)),0)AS total_card,
count(orders.id) AS order_counter
FROM orders
RIGHT JOIN calendar ON ( DATE( FROM_UNIXTIME( cast(orders.final_payment_time as signed) ) ) = calendar.datefield )
WHERE calendar.datefield >= '2016-4-1' AND calendar.datefield <= '2016-4-31'
GROUP BY DATE


What I'm hoping to do is expand the query so that I also get sum values for each day for the pre_pay_amount based on the pre_pay_time. This will allow me to calculate total revenue for the day as a combination of final_payment_amount and pre_pay_amount.

Since the pre payment may be made on a different day to the final payment I believe that I will have to do another JOIN to the same calendar table using the pre_pay_time column.

Is this possible to do with one query?

Answer

You could do something like this....You alias your pre and final payment queries and then join them by datefield.

 SELECT *
  FROM (  SELECT calendar.datefield AS FinalDate,
                 IFNULL (SUM (orders.final_payment_amount), 0)
                    AS total_final_sales,
                 IFNULL (
                    SUM (
                       if (final_payment_type = 1,
                           orders.final_payment_amount,
                           0)),
                    0)
                    AS total_final_cash,
                 IFNULL (
                    SUM (
                       if (final_payment_type = 2,
                           orders.final_payment_amount,
                           0)),
                    0)
                    AS total_final_card,
                 COUNT (orders.id) AS order_final_counter
            FROM orders
                 RIGHT JOIN calendar
                    ON (DATE (
                           FROM_UNIXTIME (
                              CAST(orders.final_payment_time AS signed))) =
                           calendar.datefield)
           WHERE     calendar.datefield >= '2016-4-1'
                 AND calendar.datefield <= '2016-4-31'
        GROUP BY FinalDate) finalPay,
       (  SELECT calendar.datefield AS PreDate,
                 IFNULL (SUM (orders.pre_payment_amount), 0) AS total_pre_sales,
                 IFNULL (
                    SUM (
                       if (pre_payment_type = 1, orders.pre_payment_amount, 0)),
                    0)
                    AS total_pre_cash,
                 IFNULL (
                    SUM (
                       if (pre_payment_type = 2, orders.pre_payment_amount, 0)),
                    0)
                    AS total_pre_card,
                 COUNT (orders.id) AS order_pre_counter
            FROM orders
                 RIGHT JOIN calendar
                    ON (DATE (
                           FROM_UNIXTIME (
                              CAST(orders.pre_payment_time AS signed))) =
                           calendar.datefield)
           WHERE     calendar.datefield >= '2016-4-1'
                 AND calendar.datefield <= '2016-4-31'
        GROUP BY PreDate) prePay
 WHERE prePay.PreDate = finalPay.FinalDate
Comments