orion3

This is because the personnel ID is supposed to be a constant regardless of how many different requests or submissions. So the personnel id is what ties the personnel entry to lets say multiple submissions. In the example above you can have 1 personnel id that is connected to several submission ids, and the submission ids are connected to the request ids, which the request ids is what has the dates and customers in it. Ideally they way the entire thing should work is grabs a personnel id, then looks up all the different submission and request ids tied to it, calculates the number of days and returns # of days and customer, then moves on to the next personnel id. Unfortunately as Jessica stated ive reverted to php functions and query loops to make up for not knowing how to do this in sql

Sure, below is the function that performs that date range calculations. Basically this is taking start dates and end dates from a requests table, identifying from a personnel submission link table if the requests = the same submission. Then it takes all the matching requests and submissions and is adding up the number of days across all requests. Also keep in mind the customer data is also stored in the requests table. I'll try and illustrate this below, the tables have alot of data and other fields, I can provide a dump if needed though. requests submissions personnel_submission_link request_id submissions_rid personnel_id request_start submissions_id submissions_id request_end request_customer So an example of the result for 1 set would be submissions id personnel id request_start request_end request_id 139698 700010 2011-10-10 2012-05-28 20005 132561 700010 2011-06-28 2011-10-09 16457 124788 700010 2010-12-27 2011-04-29 13405 106768 700010 2009-01-05 2010-12-26 4230 10766 700010 2005-05-02 2009-01-04 1134 function start_check_gap($pid, $tgap) { if ($pid != False) { $sql = " SELECT submissions_id, pl_personnel_id, request_start, request_end, request_id FROM personnel_submission_link, submissions, requests WHERE pl_sid = submissions_id AND request_id = submissions_rid AND pl_personnel_id = '$pid' ORDER BY request_end DESC "; $result_id = mysql_query($sql) or die(mysql_error()); $anymatches = mysql_num_rows($result_id); if ($anymatches == 0) { RETURN "n/a"; } else { $gaps = ""; $start = FALSE; while ($row = mysql_fetch_array($result_id)) { if ($start != FALSE) { $gap = dateDiff($start, $row['request_end']); $gaps = $gaps . "**" . $start . "," . $row['request_end'] . "," . $gap; if ($gap < -$tgap) { break; } } $start = $row['request_start']; } RETURN $start; } } }

HI Jessica, Thanks for the idea. I'll have to play with the arrays to try and change how its organized. Unfortunately my SQL knowledge is a little limited, which is why I had to create the function "start_check_gap" to figure out the what the total count in days should be across multiple date ranges. Ideally i would like for all this to be done with SQL though. @akphidelt: yes this definately helps. Let me try this out.

Hi All, I am having an issue trying to figure out how to turn the below array into a table by grouping it by customer name. I hope this makes sense. Unfortunately I cant do this with SQL because I have to run calculations on date ranges with the SQL results. Array ( [2year] => Array ([internal] => 16) [1year] => Array ([internal] => 16) [less1year] => Array ([CDR] => 4 [internal] => 171 [Other] => 11 [TPI] => 1) ) Into a table that looks like this: CUSTOMER <1 YR 1 YR 2 YR Internal 171 16 16 CDR 4 Other 11 TPI 1 So far here is the code that I have, im thinking im close, but I cant figure out how to get the same customer names to not repeat on a new <TR> but instead not repeat the customer name but show the value for the correct column. Any help is greatly appreciated. $sql = " SELECT per_id as 'Personnel #', request_customer as 'Customer' FROM submissions, requests, personnels, ( SELECT max(end) as maxdate, personnel_id FROM personnel_submission_link, submissions, requests WHERE sid = submissions_id AND request_id = submissions_rid GROUP BY pl_personnel_id ) as end WHERE request_end = end.maxdate AND end.pl_personnel_id = per_id AND per_id = cv_personnel_submission_link.pl_personnel_id AND pl_sid = submissions_id AND request_id = submissions_rid ORDER BY request_customer "; $result_id = mysql_query ($sql) or die(mysql_error()); $pernumc1 = 0; $pernumc2 = 0; $pernumc3 = 0; while($row = mysql_fetch_array($result_id)) { $customers = $row[1]; $customernames[] = $customers; $calctoday = date("Y-m-d"); $sd = start_check_gap($row[0],45); $dateDiff = strtotime($calctoday) - strtotime($sd); $totaldays = floor($dateDiff/(60*60*24)); $data = $dateDiff / 86400; $data = number_format($data, 0, '.', ''); // returns a number of days after calculations against other orders are found if ($data > 548) { $pernum3 = $pernumc3; $pernumc3++; $pernumname[] = "2 YR"; $custnames3[] = $row[1]; } elseif ($data > 365) { $pernum2 = $pernumc2; $pernumc2++; $pernumname[] = "1 YR"; $custnames2[] = $row[1]; } elseif ($data < 365) { $pernum1 = $pernumc1; $pernumc1++; $pernumname[] = "< 1 YR"; $custnames1[] = $row[1]; } } $pernum3[] = $pernum3; $pernum2[] = $pernum2; $pernum1[] = $pernum1; $ppernum1 = $pernum1; $NewArray3 = array( "2year"=>array_count_values($custnames3), "1year"=>array_count_values($custnames2), "less1year"=>array_count_values($custnames1) ); print_r($NewArray3); $NewArray = array( "More2"=>array_count_values($customernames) ); $newArray2 = array_count_values($pernumname); print("<TABLE WIDTH=100% BORDER=1 BORDERCOLOR=#6c9fc6 CELLSPACING=0>"); print ("<TR BGCOLOR=#c2d6e7>"); print("<TH>CUSTOMER</TH>"); foreach ($newArray2 as $key => $value) { $pernumname = $key; $pernumnum = $value; print("<TH>$pernumname</TH>"); } print("</TR>"); foreach ($NewArray3 as $key => $narray) { foreach ($narray as $key => $value) { $customer = $key; print("<TD VALIGN=TOP>$customer</TD>"); $numpersonnel = $value; print("<TD VALIGN=TOP>$numpersonnel</TD>"); print("</TR>"); } } print("</tr>