Quarter Reports Php and Mysql

TomCatEL
TomCatEL used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

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

Author

Commented:
A year back or a option two choose more
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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;
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)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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)
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Correction, the start of period should be in the query a total of 3 times:

select date_add($periodstart, 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
Most Valuable Expert 2011
Top Expert 2016

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial