Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Automatic date archive

Posted on 2006-12-01
3
175 Views
Last Modified: 2011-10-03
Hi there,

I am building a system that will monitor mortgage policies indefintiely from the date they are added to the system.  I therefore want to be able to see on a monthly, quarterly, yearly and bi-yearly basis how many mortgages were sold and by which advisers within the company.

When the policies are added to the database, they are added with the ( day its added) date along with the month number and year.

ie:

policy_added = 20061201
policy_added_mon = 12
policy_added_yr = 2006

Now i know how to manually add a select statement to see all of these policies by looking at thsi older question = http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21596727.html?query=archive&topics=103&showAll=true

But as the months and years go on i want the archive to build itself. Starting by a page where you select the timeframe you want to see

Monthly
Quarterly
Yearly
Bi-Yearly

You then select the year and then the month
OR
year and then quarter 1,2,3 or 4
OR
year
OR
years

But as a new month and in turn quarter and years go by i need these to automagically add to the archive links.

Any advice would be greatly appreciated!! Cheers :)
0
Comment
Question by:drews1f
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
jk2001 earned 250 total points
ID: 18058334
This is a pretty general question.

If you have a relatively small number of policies (like a few hundred per year), and not many people view them too often, you can just write up the report queries in SQL, and do the queries dynamically.

First, come up with the report query:

SELECT policy, seller FROM sales GROUP BY seller ORDER BY seller

Then, based on the interval, you add an WHERE clause:

$start = "1/1/2006";
$end = "1/4/2006";
$sql = "SELECT policy, seller FROM sales WHERE saleDate >= $start AND saleDate < $end  GROUP BY seller ORDER BY seller";

The $start and $end should be set based on the type of query:
<?php
    function reportQuery( $queryType = 'annual', $year = Null, $month = Null )
    {
        // $year and $month should be the user-selected year and month
        // if they are not set, they default to the current year and month
        // $query type is set to the type of query you want
        if (!isset($queryType))
            $queryType = 'annual';
        if (!isset($year))
            $year = date('Y');
        if (!isset($month))
            $month = date('M');
        switch ($queryType) {
            case 'm':
                   if ($month==12) {
                       $start = "$month/1/$year"; $end = "1/1/".($year+1);
                   } else {
                       $start = "$month/1/$year"; $end = ($month+1)."/1/$year";
                   }
            break;
            case 'q1':
                   $start = "1/1/$year"; $end = "1/4/$year";
            break;
            case 'q2':
                   $start = "1/4/$year"; $end = "1/7/$year";
            break;
            case 'q3':
                   $start = "1/7/$year"; $end = "1/10/$year";
            break;
            case 'q4':
                   $start = "1/10/$year"; $end = "1/12/$year";
            break;
            case 'annual':
                   $start = "1/1/$year"; $end = "1/1/".($year+1);
            break;
            case 'biannual':
                   $start = "1/1/$year"; $end = "1/1/".($year+2);
            break;
        }
        $sql = "SELECT policy, seller FROM sales WHERE saleDate >= $start AND saleDate < $end  GROUP BY seller ORDER BY seller";
        return $sql;
    }
?>
0
 

Author Comment

by:drews1f
ID: 18059370
Can i use an 'UNTIL' statement to work backwards how many years there have been? and in turn how many months etc?

Ie if we were in 2008 and the first policies were added on august 2005

the loop will get the current year
then display it (2008)
then unless the current year is '2005' it does '-1 year'
then displays it (2007)
then unless the current year is '2005' it does '-1 year'
then displays it (2006)
then unless the current year is '2005' it does '-1 year'
then displays it (2005)

I can then do the same for months using the year logic :o
0
 
LVL 8

Expert Comment

by:jk2001
ID: 18092399
I'm not understanding the second question.

If you want to show multiple years, you can just specify a new interval like "5years" and then set up the dates accordingly.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question