Select records between 2 date

[joesoaper]

As per usual thank you in advance for any help. I am stuck on what I thought would be a simple piece of code.. I was wrong 

 

I want to get a report for records between 2 dates. This sounds simple, but I need the records between the 27th of a month and the 26th of the following month. Example records from 27 January 2107 - 26 February 2017. This must change automatically as the new month begins.

 

I have this so far:

 select * from invoice where staff = '$search_term' AND MONTH(CURDATE())=MONTH(inv_date) AND YEAR(inv_date) = YEAR(CURDATE())                    

This works well giving me the records within the stipulated month. However getting it to show records from 27th - 26th of following months automatically has been a bit more problematic for me.

 

If I need to post more information please let me know.

 

 

[ginerjm]

Why not build the date args outside of the query statement and then just place those dates into the query? Something like

 

where inv_date >= '$start_dt' and inv_date <= '$end_dt'

 

where you create the two vars using php's date functions into Ymd format. Of course this assumes that your table value 'inv_date' is in fact a date or datetime value.

[Barand]

or something along these lines

SELECT * 
FROM invoice 
WHERE 
    staff = '$search_term' 
    AND inv_date BETWEEN
	CONCAT(EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL 1 MONTH),'27')
	AND
	CONCAT(EXTRACT(YEAR_MONTH FROM CURDATE()),'26');

Just to demonstrate what those two expressions generate:

mysql> select
    ->   CONCAT(EXTRACT(YEAR_MONTH FROM curdate() - interval 1 month),'27') as this
    -> , CONCAT(EXTRACT(YEAR_MONTH FROM curdate()),'26') as next;
+----------+----------+
| this     | next     |
+----------+----------+
| 20170227 | 20170326 |
+----------+----------+

[Psycho]

I want to get a report for records between 2 dates. This sounds simple, but I need the records between the 27th of a month and the 26th of the following month. Example records from 27 January 2107 - 26 February 2017. This must change automatically as the new month begins.

 

I think you need to be more explicit as to your requirements.

 

The first part in bold doesn't make sense. You are saying you want the period to start on the 26th of one month and end on the 27th of the next month. I.e. that would be a month + two days and the periods would overlap. I think the problem is how you tried to word it by giving the end date (27th) first and then stating the begin date (26th). I assume you mean that the period should run from the 26th of the previous month to the 27th of the current month.

 

Second, when exactly are you expecting the dates to roll over to the next period. You state "as the new month begins" - that would seem to mean the 1st of every month. So, on March 31st the period would run from Feb. 27th to March 26th. Then on April 1st it would run from March 27th to April 26th. Is that really what you mean or by "new month" or did you mean the beginning of the next period? For example, on March 26th, the period would be February 27th to March 26th, then on March 27th the period would be March 27th to April 26th.

[joesoaper]

Fisrtly thank you for the replies I am now trying out the new coding and will report back.

 

Secondly sorry for anything that was not explained clearly. Each report must show the records form 27th of first month to 26th of second month, and it must do this automatically.

 

So if we take January, February  March April 2017 there should be a new report each month showing records from:

 

February 2017  report dates are from 27th January to 26th February

March 2017 report dates are from 27th February to 26 March

April 2017 report dates are from 27th March to 26th April

 

and so on.

 

I hope this explains it more clearly

[ginerjm]

Here's my quick and dirty fix.

Create a table with a single record that has a "rpt_month" and a "rpt_year" column.

Each time you generate your reports, check the current date. If it is the 27th, re-set the table record's "rpt_month" and "rpt_year"" values to that date's month number and year value based on today's (ie, current) date.

 

Now retrieve the report period values from this table record. It may have just been updated, or not. Use the month/year values to construct your begin date and then do your simple calc to create the end date from that.

[Psycho]

So if we take January, February  March April 2017 there should be a new report each month showing records from:

 

February 2017  report dates are from 27th January to 26th February

March 2017 report dates are from 27th February to 26 March

April 2017 report dates are from 27th March to 26th April

 

No, that isn't exactly 100% clear. You need to state on which day of the month the report should adjust to the new period. If I read your statement explicitly, the behavior would be as follows:

 

On Jan 31st: Report should show records from Dec. 27th to Jan 26th.

On Feb 1: Report  should show records from Jan. 27th to Feb 26th.

On Feb 28: Report  should show records from Jan. 27th to Feb 26th.

On March 1st: report should show records from Feb. 27th to Mar 26th.

 

Using that logic, from the 27th to the end of the month you will be displaying the data from the prior period. Is that what you want?

[ginerjm]

Reading Psycho's last post, I can see how my simple logic has a simple error in it. Easily remedied tho.

 

When the current date is the 1st of the month, on THAT day update the table record's values for the 27th of the previous month and so on. My assumption was to do the update on the 27th, which may be a bit early. However the OP wants to do this, he can make a simple function that can handle the updating of the record's values as well as to return the proper start and end dates, based on a given input argument of today's date. If he needs to alter the 'when' factor he can easily alter the function.

[Barand]

The best way is to parameterize the query. So if you want this year's February report (Jan 27 - Feb 26) then you pass 201702 to the query. This method has the advantage of also being able to produce past reports and not fixed to the current month.

 

Example

SELECT * 
FROM invoices 
WHERE 
    inv_date BETWEEN
	CONCAT('201702','27') - INTERVAL 1 MONTH
	AND
	CONCAT('201702','26');

[Psycho]

Reading Psycho's last post, I can see how my simple logic has a simple error in it. Easily remedied tho.

 

My point was that I don't think that is what he really intends. I would assume the logic should be that the report period should change as the actual date transitions from one report period to another.

 

So, on Feb. 27th I would expect the report period to run from Feb 27th to March 26th. Barand has the best solution. Making the report period change on the 1st of the month (which creates several days delay at the end of each month) would be easy - just use the current Year/Month. But, if the report period needs to change on the actual date that the periods change it is a little more complicated.

[ginerjm]

The op was pretty clear on the window. And he hasn't picked up on any of our suggested changes as to the 'start' date. Maybe he knows what he wants.

[joesoaper]

Sorry been a bit busy trying all the suggestions. I will try explain  things  a bit more as to what I am trying to do.

 

Anytime between the 27th of Month 1 and the 26th of Month 2 I must get a report showing records between these dates.

 

Once the day in Month 2 changes to the 27th then a new report must be available form date 27 of Month 2 to 26th of Month 3. and so on and so on.

 

Apologies if I am not explaining this clearly,

[ginerjm]

You have once again stated what you said earlier. While it is very weird to do "monthly" reports that don't rely on the 1st of the month, it is however your requirement. I stand by the suggestion I gave you earlier.

[Psycho]

You have once again stated what you said earlier.

 

Actually, no. He provided the clarification that I have been asking for. It is a very nuanced detail, but important nonetheless.

 

@joesoaper,

 

Ginerjm and Barand have both provided solutions. One thing to think about is that you want the report to 'automatically' show the data for the current period, but I have to assume that there does need to be the ability to look at the report for a previous period. having said that I'll provide my input:

 

Ginerjm's solution is to create a table and pre-populate with the applicable report start/end dates as needed. You can then have the query automatically 'pick' the current period using the current date and finding the first report period record with a start date >= the current date (ordering them in ascending order). Plus, you can optionally add the ability for the user to select other report periods. The downside is that if you need to change the report periods or create other periods (e.g. weekly) you either have to update that entire table or create a more complicated structure. But, I think this would be the easiest solution.

 

Barand's solution is to create a query with an input value - in this case the "yearmonth". You could probably make some logic in the query itself to determine the correct year month based on the current day of the month, but that would probably be more complicated than it is worth. I believe he is suggesting that you determine the correct "yearmonth" in the PHP code and use that when crafting the query. The downside of this is that the query is basically hard-coded for the specific reporting periods you need (right now). That means creating reports for other periods would require a different process and/or code change. The benefit is you are not dependent upon a new table.

 

I will suggest a combination of the two approaches. I would first create a process that takes two parameters - a normal start and end date - and run the query using those. By doing this you can execute the report for any arbitrary period you want now or in the future. THEN, create a separate process that determines the dates for your standardized monthly period based on the current date. This secondary process can be done just in PHP or utilizing a secondary table. I would just do it in the PHP code.

 

You will now have a process that will (by default) provide the data for the current period. But you can, very simply, repurpose the same code for other periods. You can create separate processes to return other standardized periods if you want (Quarterly, Year to date, etc.) and marry them to this process.

 

Here's some sample code. Although I would probably add some validation to ensure any passed dates are valid. Also, you should not use 'SELECT *'. List out the fields you want.

<?php
function getReportData($startDate=false, $endDate=false)
{
    //Check if start date and end date are both false
    if(!$startDate && !$endDate) {
        //Call function to return start/end date for current period
        $paramValues = getCurrentMonthPeriod();
    } elseif(!$endDate) {
        //Start date set, but not end date. Set period from start date to today
        $paramValues = array('start_date'=>$startDate, 'end_date'=>date('Y-m-d'));
    } else {
        //Both start and end dates are set - use them
        $paramValues = array('start_date'=>$startDate, 'end_date'=>$endDate);
    }
 
    //Create & prepare the query
    $query = "SELECT * FROM invoice
              WHERE staff = '$search_term'
                AND inv_date BETWEEN :start_date AND :end_date";
    $stmt = $pdo->prepare($query);
    //Execute query with the input values
    $stmt->execute(['start_date' => $startDate, 'end_date' => $endDate]);
    //Fetch and return results
    $results = $stmt->fetchAll();
    return $results;
}
 
 
function getCurrentMonthPeriod()
{
    //Get current day of month
    $day = date('d');
    //Check the value and define start/end dates
    if($day<27) {
        $startDate = date('Y-m-27', strtotime('-1 month'));
$endDate = date('Y-m-26');
    } else {
        $startDate = date('Y-m-27');
        $endDate = date('Y-m-26', strtotime(date('Y-m-1') . " +1 month"));     
    }
    //return the dates
    return array('start_date'=>$startDate, 'end_date'=>$endDate);
}

//Usage:

//Get data for current period
$data = getReportData();

//Get data for start date till today
$data = getReportData('2017-01-01'); //E.g. Year to date report

//Get data for the defined start/end dates
$data = getReportData('2017-01-01', '2017-03-31'); //E.g. Quarterly
?>

[ginerjm]

Actually my idea was to have the function that retrieves the data actually maintain the tabled dates by examining them before doing the query and making the necessary change when it detects that 'today' is > than the 27th of a month. If so, update the table for the 'current' period. Yes - if one needs to reports a past period, parameters to provide the specific period good be supplied. In no way does my table have to be manually pre-set, except for the first time.

[Psycho]

Actually my idea was to have the function that retrieves the data actually maintain the tabled dates by examining them before doing the query and making the necessary change when it detects that 'today' is > than the 27th of a month. If so, update the table for the 'current' period. Yes - if one needs to reports a past period, parameters to provide the specific period good be supplied. In no way does my table have to be manually pre-set, except for the first time.

 

I see. But, if you already have a table of the start/end periods there is no reason to have any logic in PHP to retrieve the correct period. Just use something like:

SELECT start_date, end_date FROM periods WHERE start_date >= today ORDER BY start_date LIMIT 1

Regardles of how you retrieve the period, I understood the approach and have used something similar for non-standard reporting periods. For example, my company uses an agile development process where we work in three week iterations. So, when building a reporting interface to look at iteration metrics I needed a way to determine the start/end dates. Plus, we have had times where there were non-standard iterations (e.g. due to holidays/release dates we might extend an iteration to 4 weeks). So, the iteration periods were in-deterministic and the only solution (I could think of ) was to build a table with all the iteration periods. I pre-populated everything, but would have to make changes if we end up having any non-standard iterations.

 

My suggestion above comes from experience. Even though all the teams in the organization use the same iteration periods, there are some executives that will request data on monthly or weekly periods because it "makes sense" to them or we are wanting to track the data for trends. A period from the 27th to 26th seems odd. So, while the OP needs those periods today, there is a very real possibility that he will need other periods some time in the future. So, I suggest separating the process of determining the periods from the actual query execution. You can then have the option to run a report for ANY period. If there are some standard periods needed, you can build in the supporting functions to handle those seamlessly or even allow for a completely custom period.

[ginerjm]

I was being narrowly focused. Yes - it makes sense to separate the period calc from the data retrieval logic. Of course. So the function now boils down to "get the dates" only. Same process though. For THIS purpose.

[joesoaper]

Thank you all once again for the very useful help and advice. I will try again and will let you know when I am successful.