• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

MySql Select past quarter to date

I have a table that has dates when work was completed,
I need to run quarterly reports, quarter to date, or last quarter.

Doing a google I found
and quarter(fecha) < quarter(current_date) OR YEAR(fecha) < YEAR(NOW())",$dbh);  
But i couldnt get that to work...

500 points because I need it asap !



0
Energy-Expert
Asked:
Energy-Expert
  • 7
  • 3
1 Solution
 
Ray PaseurCommented:
You can determine the quarter in PHP and use that in the query, I think.  Are your quarters the regular calendar business quarters, ending at the end of March, June, etc.?
0
 
Patmac951Commented:
select completedworkdate;
from yourtable;
where compledtedworkdate between beginquarterdate and lastquarterdate
0
 
Ray PaseurCommented:
This seems to cover it.  Then you can use the array elements in your query.  HTH, ~Ray
<?php // RAY_date_quarters.php
error_reporting(E_ALL);
echo "<pre>";

// DETERMINE WHAT QUARTER THE DATE IS IN

function getQuarter($datething)
{
    // IF A BOGUS DATE
    if (!$timestamp = strtotime($datething)) return FALSE;

    // GET THE NUMBER OF THE MONTH
    $month = date('n', $timestamp);
    $year  = date('Y', $timestamp);

    // GET THE QUARTERLY DATA
    switch ($month)
    {
        case 1:
        case 2:
        case 3:
            $quarter = 1;
            $start   = "January 1 $year";
            $end     = "March 31 $year 23:59:59";
            break;

        case 4:
        case 5:
        case 6:
            $quarter = 2;
            $start   = "April 1 $year";
            $end     = "June 30 $year 23:59:59";
            break;

        case 7:
        case 8:
        case 9:
            $quarter = 3;
            $start   = "July 1 $year";
            $end     = "September 30 $year 23:59:59";
            break;

        case 10:
        case 11:
        case 12:
            $quarter = 4;
            $start   = "October 1 $year";
            $end     = "December 31 $year 23:59:59";
            break;
    }

    // PREPARE THE RETURN ARRAY
    $array["Quarter"]   = $quarter;
    $array["FirstDate"] = date('c', strtotime($start));
    $array["LastDate"]  = date('c', strtotime($end));

    return $array;
}

// TEST THE FUNCTION
var_dump(getQuarter('Foo'));
var_dump(getQuarter('July 4, 1976'));
var_dump(getQuarter('Today'));

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Energy-ExpertAuthor Commented:
I was looking for a way to do it within SQL, and not have to qeury the db, process with php, then re-query ...

one link I found ...
select ... FROM table WHERE quarter(mydate) = quarter(DATE_ADD(NOW(), INTERVAL -3 MONTH)) AND YEAR(mydate) = YEAR(NOW()) OR quarter(DATE_ADD(NOW(), INTERVAL -3 MONTH)) = 4 AND YEAR(mydate) = YEAR(NOW()) - 1

but i dont know what mydate is referring to...

0
 
Energy-ExpertAuthor Commented:
Think I found one answer  myself... at least for current quarter but i have to add more rows to my table to test...

AND work.passed_date != "0000-00-00"
AND work.passed_date = quarter(NOW()) < quarter(curdate()) OR YEAR(NOW()) < YEAR(NOW())

Now to find how to choose last quarter...
0
 
Ray PaseurCommented:
I don't think you would have to query, process and requery.  Just throw the date in question at the function above and it will return an array that gives you the ISO8601 DATETIME fields of the boundary dates around the quarter when that date occurred.  Then you can use those fields in the query - so only one call is needed.

$datex = getQuarter('today');
$query = "SELECT thing FROM table WHERE passed_date BETWEEN '{$datex["FirstDate"]}' AND '{$datex["FirstDate"]}' ";

You might consider making this change to add a default date equal to today.  Line 7 of the code snippet above.
function getQuarter($datething='Today')

Open in new window

0
 
Ray PaseurCommented:
Sorry - the king of typos is at work again.  Make that query sequence more like the code snippet...
$query = "SELECT thing FROM table WHERE passed_date BETWEEN '{$datex["FirstDate"]}' AND '{$datex["LastDate"]}' ";

Open in new window

0
 
Ray PaseurCommented:
Just checking back - did you try the code example I posted?  It worked correctly for my queries.  Happy New Year, ~Ray
0
 
Energy-ExpertAuthor Commented:
you guys rule.

But, I was looking to do this in sql :(

If I use php, I will award based on that decision :)

0
 
Ray PaseurCommented:
Thanks for your kind words.  In the original post you wrote,

"I need to run quarterly reports, quarter to date, or last quarter... couldnt get that to work... 500 points because I need it asap !"

So I gave you the solution that would give you the results ASAP.  If it is purely an academic learning exercise, feel free to experiment with SQL syntax - I expect that there is some kind of SQL statement that would have the same effect, but I didn't feel like taking the time to research it when I knew there was a 100% certainty that I could get it right in PHP with little effort.  That was to be responsive to your "asap" needs.

Best, ~Ray
0
 
Ray PaseurCommented:
12/28/09 03:19 PM, ID: 26133770 - this worked in my tests.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now