[Solved] Need to show costs for last 6 months on complicated sub queries

by


Right I have an answer which works, but it is not the best solution by any means. I really wanted to answer my question with one query, but the only solution I have come up with is to use php and loop the mysql. Yes I know this is not ideal, but I needed to find a solution quickly and even though it isn’t the best way, it works. I will carry on and see if I can work out a way of doing this with JUST mysql, but at the moment this will have to do.

Basically my query is doing one month at a time. So I have to increment the month in php and re-run the query for all 6 months.

Looking for a better solution now.

$plArray = array();
for ($i = 0; $i <= 5; $i++):
    $month  = date("m", strtotime( date( 'Y-m-01' )." -$i months"));
    $query="SELECT IFNULL(`totalSales`,0)-IFNULL(`totalPurchase`,0)-(IFNULL(`totalNetpay`,0)+IFNULL(`totalP32`,0)+IFNULL(`totalPension`,0)) as `totalPL`";
    $query.="
    FROM (SELECT SUM((`Unit_Cost`*`ExchangeRate`)*`Quantity`) AS `totalSales` FROM `salesinvoice_products` AS `prod`
    LEFT JOIN `salesinvoice` AS `inv` on `prod`.`SalesInvoice_id`=`inv`.`SalesInvoice_id`
    WHERE MONTH(`InvoiceDate`)=?
    ) as `t1`,

    (SELECT SUM((`InvoiceValue`*`ExchangeRate`)-`VATValue`) as `totalPurchase` FROM `purchaseinvoice`
    WHERE MONTH(`InvoiceDate`)=?
    ) as `t2`,

    (SELECT SUM(`NetAmountPaid`) as `totalNetpay` FROM `payroll_netpay`
    WHERE MONTH(`Date`)=?
    ) as `t3`,

    (SELECT SUM(`totalAmountDue`) as `totalPension` FROM `payroll_pensionrecords`
    WHERE MONTH(`datePaid`)=?
    ) as `t4`,

    (SELECT SUM(`totalAmountDue`) as `totalP32` FROM `payroll_p32records`
    WHERE MONTH(`datePaid`)=?
    ) as `t5`";
    $stmt = $this->logon->db->prepare($query);
    $stmt->bind_param('iiiii',$month,$month,$month,$month,$month);
    $stmt->execute();
    $result = $this->functions->get_result($stmt);
    $plArray[] = array(
    'date' => date("Y-m", strtotime( date( 'Y-m-01' )." -$i months")),
    'total' => $result[0]['totalPL']
    );
endfor;

solved Need to show costs for last 6 months on complicated sub queries