asked on

Quarter Reports Php and Mysql

Hi All

I have a table that has the following colums date,sales and purchases .

I need mysql or php code that will give me quarter report's depending on the start of the company's
financial year so it can be in a config file say $financial_start = 2010-05-01 ;

It then needs to work out the quarter sales , and purchases and give it to me in a table format .

All help will be appreciated .

Thanks
Thomas

cyberkiwi

Do you need just the current Quarter, or how far back does the report need to go?

TomCatEL

ASKER

A year back or a option two choose more

cyberkiwi

select
floor(period_diff(date_format(`date`, '%Y%m'), date_format($periodstart, '%Y%m')) / 3) as quarter,
count(sale) as countsales, count(purchases) as countpurchases
from tbl
group by floor(period_diff(date_format(`date`, '%Y%m'), date_format($periodstart, '%Y%m')) / 3)
order by 1;

ollyatstithians

The mysql should be something like:

select extract(year from date) as year, extract(quarter from date) as quarter, sum(sales), sum(purchases) from mytable group by year, quarter having year={$year} and quarter={$quarter}

(Not tested)

cyberkiwi

select date_add('2010-06-01', INTERVAL quarter*3 MONTH) as StartOfQuarter, `countsales`, countpurchases
from
(
select
floor(period_diff(date_format(`date`, '%Y%m'), date_format($periodstart, '%Y%m')) / 3) as quarter,
count(sale) as countsales, count(purchases) as countpurchases
from tbl
group by floor(period_diff(date_format(`date`, '%Y%m'), date_format($periodstart, '%Y%m')) / 3)
) sq
order by 1

If you need any filters for dates, put it here, e.g.

from tbl
where `date` between x and y
group by floor(period_diff(date_format(`date`, '%Y%m'), date_format($periodstart, '%Y%m')) / 3)

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

I might try setting up the date ranges in PHP, then using the values from this return array as BETWEEN date strings in the query.

So the SELECT for the second quarter of the year might have this...

... WHERE transaction_date BETWEEN '{$qtrs["q2_alpha"]}' AND '{$qtrs["q2_omega"]}' ...

And the SELECT for the second half of the year might have this...

... WHERE transaction_date BETWEEN '{$qtrs["q3_alpha"]}' AND '{$qtrs["q4_omega"]}' ...

Best regards, ~Ray

<?php //RAY_quarterly_dates.php
error_reporting(E_ALL);
echo "<pre>" . PHP_EOL;

function quarter_dates($financial_start, $date_format='Y-m-d')
{
    // THE FINANCIAL START ARGUMENT DEFINES THE FIRST DAY OF THE FIRST QUARTER
    $quarters['q1a'] = date($date_format, strtotime($financial_start));
    
    // ADD THE STARTING DAYS OF SUCCEEDING QUARTERS
    $quarters['q2a'] = date($date_format, strtotime($quarters['q1a'] . ' +3 months'));
    $quarters['q3a'] = date($date_format, strtotime($quarters['q2a'] . ' +3 months'));
    $quarters['q4a'] = date($date_format, strtotime($quarters['q3a'] . ' +3 months'));
    $quarters['q5a'] = date($date_format, strtotime($quarters['q4a'] . ' +3 months'));
    
    // ADD THE ENDING DAYS OF THE QUARTERS
    $quarters['q1z'] = date($date_format, strtotime($quarters['q2a'] . ' - 1 day'));
    $quarters['q2z'] = date($date_format, strtotime($quarters['q3a'] . ' - 1 day'));
    $quarters['q3z'] = date($date_format, strtotime($quarters['q4a'] . ' - 1 day'));
    $quarters['q4z'] = date($date_format, strtotime($quarters['q5a'] . ' - 1 day'));
    
    // DISCARD THE WORKING VARIABLE FROM THE RETURNED DATA
    unset($quarters['q5a']);
    
    // NOT STRICTLY NEEDED, BUT ksort() MAKES EASY-TO-UNDERSTAND OUTPUT
    ksort($quarters);
    return $quarters;
}

// TEST DATA
$financial_start = '2010-05-01';
$qtrs = quarter_dates($financial_start);
var_dump($qtrs);

// TEST DATA - US GOVERNMENT
$financial_start = '2008-10-01';
$qtrs = quarter_dates($financial_start);
var_dump($qtrs);

// TEST DATA - MY BIRTHDAY
$financial_start = '1979-09-15';
$qtrs = quarter_dates($financial_start);
var_dump($qtrs);

Open in new window

TomCatEL

ASKER

Hi all i have had connection problems will try examples and come back to you