asked on

SQL quiry assistance needed

Hello,

I am trying to create a small program that will calculate commission using php mysql.

I have a user import a data to the mysql database and I need to display acutall commission as a result.
I have a table commission.
Table has 1 column: Company Name, Profit for the month, Month.
Calculation as follows.
(Current Month Profit - Last Month profit) * 0.2

Sample Data Bellow
account_id      company_name      minutes      profit      revenue      cost      time_frame
983348      Company 1      1731.5      14.43      150.4      135.97      10
983349      Company 2      108744.92      701.65      7304.63      6602.98      10
983350      Company 3      168625.77      762.69      6851.39      6088.7      10
983358      Company 1      1783.02      14.53      153.02      138.49      11
983359      Company 2      277170.65      1375.36      7944      6568.65      11
983360      Company 3      286652.4      1428.12      24341.75      22913.63      11

Result Need to see:
Month November
Company Name Commision
Company 1 0.02
Company 2 134.742
Company 3 133.086

Thanks in advance,

Roman

nplib

is time_frame the month aka 1-12?

dizel247

ASKER

yes but i not sure if its right. I need to have month and a year so we can distinguish between them.

Thanks in advance,

Roman

nplib

Here is the code.

function getCommission($currentMonth) {
	$months = array($currentMonth-1, $currentMonth); // this array contians previous month and current month
	switch ($months['1']) {
		case 1: $month = "January"; break;
		case 2: $month = "February"; break;
		case 3: $month = "March"; break;
		case 4: $month = "April"; break;
		case 5: $month = "May"; break;
		case 6: $month = "June"; break;
		case 7: $month = "July"; break;
		case 8: $month = "August"; break;
		case 9: $month = "September"; break;
		case 10: $month = "October"; break;
		case 11: $month = "November"; break;
		case 12: $month = "December"; break;
	}
 
	$results = array("Company" => array(), "Current" => array(), "Previous" => array());
	foreach ($months as $key => $m) {
		$sql = "SELECT company_name, profit FROM sample1 where time_frame = $m ORDER BY company_name;";
		$rs = mysql_query($sql) or die(mysql_error());
		$company = array();
		$profits = array();
		while ($row = mysql_fetch_array($rs)) {
			//$results[$month][] = $row['profit'];
			//$results[$month][] = $row['company_name'];
			$profits[] = $row['profit'];
			$company[] = $row['company_name'];
		}
		if ($key == 0) {
			$results['Previous'] = $profits;
		} else {
			$results['Current'] = $profits;
		}
	}
	$results['Company'] = $company;
	$html = "<table>";
	$html .= "<tr><td colspan='2'>Month $month</td></tr>";
	foreach ($results['Company'] as $key => $value) {
		$commission = number_format((($results['Current'][$key] - $results['Previous'][$key])*0.2), 2);
		$html .= "<tr>";
		$html .= "<td>$value</td><td>$commission</td>";
		$html .= "</tr>";
	}
	$html .= "</table>";
	return $html;
}
echo getCommission(11);

Open in new window

nplib

place this after your connection statment to your db.

dizel247

ASKER

hello,

Thanks for a wonderful description. For some reason I am not getting any result. All I see month November.

1. Can you verify how time_frame - what format I need to have my values 1-12 or ex November, December etc.

2. I am already running a quire that shows me result for other columns. I would like to be able to plug your code for the Commission column and see the result. Attaching code bellow. I am using dremweaver to generate some of my code.

<?php require_once('Connections/connTT.php'); ?>
//This is sample quiry I am running. I will modify it later to get request from previous page to show result based on user imput. For now I am trying to have generic result.
mysql_select_db($database_connTT, $connTT);
$query_rsCommision = "SELECT tb_accounts.minutes, tb_accounts.time_frame  	tb_accounts.profit,  	tb_accounts.revenue,  	tb_accounts.cost,  	carriers.carrier_name,  	tb_account_report.report_name,  	carriers.manag_customers,  	carriers.manag_venodrs,  	carriers.house_account,  	carriers.carrier_status FROM tb_accounts INNER JOIN carriers ON tb_accounts.carrier_code = carriers.carrier_code 	 INNER JOIN tb_account_report ON tb_accounts.report_id = tb_account_report.report_id";
$rsCommision = mysql_query($query_rsCommision, $connTT) or die(mysql_error());
$row_rsCommision = mysql_fetch_assoc($rsCommision);
$totalRows_rsCommision = mysql_num_rows($rsCommision);
//////////////
 
<table width="90%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#000000">
            <tr>
              <td colspan="7"><div align="center" class="topheader"><?php echo $row_rsManagers['user_firstname']; ?>'s Commision for <?php echo $row_rsReportName['report_name']; ?></div></td>
              </tr>
            <tr>
              <td bgcolor="#999933"><div align="center">Carrier Name</div></td>
              <td bgcolor="#999933"><div align="center">Minutes</div></td>
              <td bgcolor="#999933"><div align="center">Profit</div></td>
              <td bgcolor="#999933"><div align="center">Revenue</div></td>
              <td bgcolor="#999933"><div align="center">Cost</div></td>
              <td bgcolor="#999933"><div align="center">Margin</div></td>
              <td bgcolor="#999933"><div align="center">Commision</div></td>
            </tr>
            <?php do { ?>
              <tr>
                <td><?php echo $row_rsCommision['carrier_name']; ?></td>
                <td><div align="right"><?php echo $row_rsCommision['minutes']; ?></div></td>
                <td><div align="right">$<?php echo $row_rsCommision['profit']; ?></div></td>
                <td><div align="right">$<?php echo $row_rsCommision['revenue']; ?></div></td>
                <td><div align="right">$<?php echo $row_rsCommision['cost']; ?></div></td>
                <td><div align="right">                <?php 
				$cost = $row_rsCommision['cost'];
				$revenue = $row_rsCommision['revenue'];
				$margin = 100 - ( $cost * 100 / $revenue );
				echo round($margin,2) . "%"; ?></div></td>
                <td><div align="right">
                <?php 
				/////////// CODE NEEDED HERE *****************************
				?>
                
                </div></td>
              </tr>
              <?php } while ($row_rsCommision = mysql_fetch_assoc($rsCommision)); ?>
            <tr>
              <td colspan="2">Total:</td>
              <td><div align="center">$<?php echo $row_rsTotatlProfit['Total_profit']; ?> </div></td>
              <td><div align="center">$<?php echo $row_rsTotalRevenue['Total_revenue']; ?></div></td>
              <td><div align="center">$<?php echo $row_rsTotal_Cost['Total_cost']; ?></div></td>
              <td colspan="2"><div align="center">Total Comision Due: $327.11</div></td>
              </tr>
          </table>

Open in new window

imitchie

you really need another column, year to be able to go from 2007/12 to 2008/1. I'll assume that you will add that to your table

select (thismonth.profit - lastmonth.profit) * 0.2
from comm thismonth
inner join comm lastmonth on thismonth.company_name = lastmonth.company_name and 
 (thismonth.year*12+thismonth.time_frame) = (lastmonth.year*12+lastmonth.time_frame+1)

Open in new window

nplib

I did it with the columns you provided, I will edit your code to comply.

ASKER CERTIFIED SOLUTION

nplib

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial