Re: A Query for Report

Addressed to: Manuel <manuel@buyee.com.sg> php-db@lists.php.net ** Reply to note from Manuel <manuel@buyee.com.sg> Mon, 11 Sep 2000 10:33:45 +0800 > > I am using MySQL 3.22.32, Linux 2.2.16, PHP4.0.1pl2 > > Let's us one example: my sales table:- salesmancode salesdate > > my sales table data: > A 2000-08-20 > A 2000-08-02 > B 2000-08-20 > B 2000-08-02 > A 2000-07-10 > B 2000-07-10 > B 2000-07-05 > B 2000-07-01 > > My Results should be > Salesmancode Jul Aug Total > A 1 2 3 > B 3 2 5 > Total 4 4 8 > This should eliminate most of the PHP work... SELECT salesmancode, SUM( IF( MONTH( salesdate ) = 1, 1, 0 )) AS Jan, SUM( IF( MONTH( salesdate ) = 2, 1, 0 )) AS Feb, SUM( IF( MONTH( salesdate ) = 3, 1, 0 )) AS Mar, SUM( IF( MONTH( salesdate ) = 4, 1, 0 )) AS Apr, SUM( IF( MONTH( salesdate ) = 5, 1, 0 )) AS May, SUM( IF( MONTH( salesdate ) = 6, 1, 0 )) AS Jun, SUM( IF( MONTH( salesdate ) = 7, 1, 0 )) AS Jul, SUM( IF( MONTH( salesdate ) = 8, 1, 0 )) AS Aug, SUM( IF( MONTH( salesdate ) = 9, 1, 0 )) AS Sep, SUM( IF( MONTH( salesdate ) = 10, 1, 0 )) AS Oct, SUM( IF( MONTH( salesdate ) = 11, 1, 0 )) AS Nov, SUM( IF( MONTH( salesdate ) = 12, 1, 0 )) AS Dec, SUM( 1 ) As SalesmanTotal FROM sales_table WHERE salesdate <= NOW() AND TO_DAYS( salesdate ) > TO_DAYS( ADDDATE( SUBDATE( NOW(), INTERVAL 1 YEAR ), INTERVAL 1 MONTH )) - DAYOFMONTH( NOW()) ORDER BY salesmancode GROUP BY salesmancode That should give you the report by salesmancode down the page, with months across, for the last year. Only sales in the current month, on or before today are shown. The date math in the second part of the where clause should select the first day of the next month, last year - the beginning date of your report. (Currently 1999-10-01) It actually returns the last day of this month, last year (1999-09-30) but since I use > that date is not included in the report. You could replace _both_ NOW() functions with a MYSQL formatted date to select the date to operate on, or even FROM_UNIXTIME( $UnixTimestamp_of_date_for_report ) if you want to base the report on a date other than today. You can read more about date math in MySQL here: http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_functions You will have to sum $Jan .. $Dec in PHP to obtain the totals line, and figure out how to rotate the months for display, say to make the current month the last month listed. (sounds like a job for arrays and % to me...) What the heck... $Months = array( ''. 'Jan', 'Feb', ... ); $Month = 9; #Current month, shown last 9 = Sep. $Data = mysql_fetch_row( $Result ); echo '<TR><TD>', $Data[ 0 ], '</TD>'; # Salesmancode for( $I = 1, $I < 13, $I ++ ) { echo '<TD>', $Data[ (( $I + $Month ) % 12 ) + 1 ], '</TD>'; $Totals[ $I ] += $Data[ $I ]; } echo '</TR>'; This takes the data out of the query result, and displays it. For example this month the data would be... Salesman Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Next month it becomes... Salesman Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct If you change $Data to $Months in the for loop, it will paint the col headings correctly. I haven't actually tested this, but it should be pretty close. You may have to add or subtract 1 on some of the boundaries, but the overall logic should be there... Rick Widmer Internet Marketing Specialists www.developersdesk.com