benanamen Posted October 28, 2015 Share Posted October 28, 2015 (edited) I have the following query used for a google line chart. The query works and does four different monthly counts for the past 12 months to current date. I need to modify it so it does the same exact thing but weekly for the past 6 month to current date. Week starts Sunday, ends Saturday. No brain food left after this one. Getting weak... * Any improvements to current query are also encouraged. Output Image attached. <?php if (isset($_REQUEST['floor']) || !isset($_REQUEST['specialty'])) { $contract_type_id = 3; $active_column = 'active'; } if (isset($_REQUEST['specialty'])) { $contract_type_id = 2; $active_column = 'active_specialty'; } $sql="SELECT C.month, sum(slab) as slab, sum(dried_in) as dried_in, sum(drywall) as drywall, sum(frame) as frame, C.year_month_number FROM ( (SELECT CASE MONTH (l.frame_date) WHEN 1 THEN CONCAT('Jan','-',Year (l.frame_date)) WHEN 2 THEN CONCAT('Feb', '-', Year (l.frame_date)) WHEN 3 THEN CONCAT('Mar','-',Year (l.frame_date)) WHEN 4 THEN CONCAT('Apr', '-', Year (l.frame_date)) WHEN 5 THEN CONCAT('May', '-', Year (l.frame_date)) WHEN 6 THEN CONCAT('Jun', '-', Year (l.frame_date)) WHEN 7 THEN CONCAT('Jul', '-', Year (l.frame_date)) WHEN 8 THEN CONCAT('Aug', '-', Year (l.frame_date)) WHEN 9 THEN CONCAT('Sep', '-', Year (l.frame_date)) WHEN 10 THEN CONCAT('Oct', '-', Year (l.frame_date)) WHEN 11 THEN CONCAT('Nov', '-', Year (l.frame_date)) WHEN 12 THEN CONCAT('Dec', '-', Year (l.frame_date)) ELSE 'Unknown' END as 'month', null as slab, null as drywall, COUNT(IF(l.frame_date is not null, 1, 0)) AS frame, null AS dried_in, CONCAT(Year (l.frame_date),LPAD(MONTH (l.frame_date),2,0)) year_month_number FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = $contract_type_id ) AND l.$active_column=1 AND l.lot_type_id <> 1 and l.frame_date is not null and (frame_date <= CURDATE() and frame_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by MONTH (l.frame_date) ORDER BY MONTH (l.frame_date) ASC) UNION ALL (SELECT CASE MONTH (l.drywall_date) WHEN 1 THEN CONCAT('Jan','-',Year (l.drywall_date)) WHEN 2 THEN CONCAT('Feb', '-', Year (l.drywall_date)) WHEN 3 THEN CONCAT('Mar','-',Year (l.drywall_date)) WHEN 4 THEN CONCAT('Apr', '-', Year (l.drywall_date)) WHEN 5 THEN CONCAT('May', '-', Year (l.drywall_date)) WHEN 6 THEN CONCAT('Jun', '-', Year (l.drywall_date)) WHEN 7 THEN CONCAT('Jul', '-', Year (l.drywall_date)) WHEN 8 THEN CONCAT('Aug', '-', Year (l.drywall_date)) WHEN 9 THEN CONCAT('Sep', '-', Year (l.drywall_date)) WHEN 10 THEN CONCAT('Oct', '-', Year (l.drywall_date)) WHEN 11 THEN CONCAT('Nov', '-', Year (l.drywall_date)) WHEN 12 THEN CONCAT('Dec', '-', Year (l.drywall_date)) ELSE 'Unknown' END as 'month', null as slab, COUNT(IF(l.drywall_date is not null, 1, 0)) AS drywall, null as frame, null AS dried_in, CONCAT(Year (l.drywall_date),LPAD(MONTH (l.drywall_date),2,0)) year_month_number FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = $contract_type_id ) AND l.$active_column=1 AND l.lot_type_id <> 1 and l.drywall_date is not null and (drywall_date <= CURDATE() and drywall_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by MONTH (l.drywall_date) ORDER BY MONTH (l.drywall_date) ASC) UNION ALL (SELECT CASE MONTH (l.slab_date) WHEN 1 THEN CONCAT('Jan','-',Year (l.slab_date)) WHEN 2 THEN CONCAT('Feb', '-', Year (l.slab_date)) WHEN 3 THEN CONCAT('Mar','-',Year (l.slab_date)) WHEN 4 THEN CONCAT('Apr', '-', Year (l.slab_date)) WHEN 5 THEN CONCAT('May', '-', Year (l.slab_date)) WHEN 6 THEN CONCAT('Jun', '-', Year (l.slab_date)) WHEN 7 THEN CONCAT('Jul', '-', Year (l.slab_date)) WHEN 8 THEN CONCAT('Aug', '-', Year (l.slab_date)) WHEN 9 THEN CONCAT('Sep', '-', Year (l.slab_date)) WHEN 10 THEN CONCAT('Oct', '-', Year (l.slab_date)) WHEN 11 THEN CONCAT('Nov', '-', Year (l.slab_date)) WHEN 12 THEN CONCAT('Dec', '-', Year (l.slab_date)) ELSE 'Unknown' END as 'month', COUNT(IF(l.slab_date is not null, 1, 0)) AS slab, null as drywall, null as frame, null AS dried_in, CONCAT(Year (l.slab_date),LPAD(MONTH (l.slab_date),2,0)) year_month_number FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = $contract_type_id ) AND l.$active_column=1 AND l.lot_type_id <> 1 and l.slab_date is not null and (slab_date <= CURDATE() and slab_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by MONTH (l.slab_date) ORDER BY MONTH (l.slab_date) ASC) UNION ALL (SELECT CASE MONTH (l.dried_in_date) WHEN 1 THEN CONCAT('Jan', '-', Year (l.dried_in_date)) WHEN 2 THEN CONCAT('Feb', '-', Year (l.dried_in_date)) WHEN 3 THEN CONCAT('Mar', '-', Year (l.dried_in_date)) WHEN 4 THEN CONCAT('Apr', '-', Year (l.dried_in_date)) WHEN 5 THEN CONCAT('May', '-', Year (l.dried_in_date)) WHEN 6 THEN CONCAT('Jun', '-', Year (l.dried_in_date)) WHEN 7 THEN CONCAT('Jul', '-', Year (l.dried_in_date)) WHEN 8 THEN CONCAT('Aug', '-', Year (l.dried_in_date)) WHEN 9 THEN CONCAT('Sep', '-', Year (l.dried_in_date)) WHEN 10 THEN CONCAT('Oct', '-', Year (l.dried_in_date)) WHEN 11 THEN CONCAT('Nov', '-', Year (l.dried_in_date)) WHEN 12 THEN CONCAT('Dec', '-', Year (l.dried_in_date)) ELSE 'Unknown' END as 'month', null AS slab, null as drywall, null as frame, Count(IF(l.dried_in_date is not null, 1 , 0)) AS dried_in, CONCAT(Year (l.dried_in_date), LPAD(MONTH (l.dried_in_date),2,0)) year_month_number FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = $contract_type_id ) AND l.$active_column=1 AND l.lot_type_id <> 1 and dried_in_date is not null and (dried_in_date <= CURDATE() and dried_in_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by MONTH (l.dried_in_date) ORDER BY MONTH (l.dried_in_date) asc) ) as C GROUP BY C.month ORDER BY C.year_month_number asc"; echo $sql; $stmt = $pdo->prepare($sql); $stmt->execute(); $result = $stmt->fetchAll(); $data[] = array( 'month', 'slab', 'dried_in', 'frame', 'drywall' ); foreach ($result as $row) { $data[] = array( $row['month'], (int) $row['slab'], (int) $row['dried_in'], (int) $row['frame'], (int) $row['drywall'] ); } $encoded_data= json_encode($data); ?> <!-- Load Google JSAPI --> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load("visualization", "1", { packages: ["corechart"] }); google.setOnLoadCallback(drawChart); // draw chart fucntion function drawChart() { /* var jsonData = $.ajax({ url: "./includes/chart_12month_comparison_data.php", dataType: "json", async: false }).responseText; var obj = jQuery.parseJSON(jsonData); */ var data = google.visualization.arrayToDataTable(<?php echo $encoded_data;?>); var currentYear = new Date(); var options = { //title: 'Demo Google LineChart : ' + (new Date(currentYear.setDate(currentYear.getDate() - 365))).yyyymmdd() + ' - ' + (new Date()).yyyymmdd() title: '12 Month Comparison ' + (new Date(currentYear.getFullYear()-1,currentYear.getMonth()+1)).yyyymmdd() + ' - ' + (new Date()).yyyymmdd(), //legend: 'none', hAxis: { minValue: 0, maxValue: 9 }, pointSize: 10, pointShape: 'square' }; var chart = new google.visualization.LineChart( document.getElementById('chart_div')); chart.draw(data, options); } // format date function Date.prototype.yyyymmdd = function() { var yyyy = this.getFullYear().toString(); var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based var dd = this.getDate().toString(); return yyyy + "/" + (mm[1]?mm:"0"+mm[0]) + "/" + (dd[1]?dd:"0"+dd[0]); // padding }; </script> <?php $floor = isset($_GET['floor']) || !isset($_GET['specialty']) ? "style=\"color:#FF0000\"" : ''; $specialty = isset($_GET['specialty']) ? "style=\"color:#FF0000\"" : ''; ?> FILTER: <a href="<?php echo $_SERVER['SCRIPT_NAME'];?>?p=<?php echo $_GET['p'];?>&floor" title="Flooring" <?php echo $floor;?>>Flooring</a> | <a href="<?php echo $_SERVER['SCRIPT_NAME'];?>?p=<?php echo $_GET['p'];?>&specialty" title="Specialty" <?php echo $specialty;?>>Specialty</a><br><br> <div id="chart_div" style="width: 900px; height: 500px;"> </div> Edited October 28, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 28, 2015 Share Posted October 28, 2015 You will get better quicker answers if you post an SQL dump of your DB. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 28, 2015 Author Share Posted October 28, 2015 (edited) LOL @Barand, so true.! I am working on it. It is a very big and complex DB with keys to everything. Difficult to break it apart for just what is needed for this query. Edit* SQL Attached data.txt Edited October 28, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 28, 2015 Share Posted October 28, 2015 Does this help? SELECT DATE_FORMAT(frame_date, '%X-%V') as yr_wk WHERE frame_date > CURDATE() - INTERVAL 6 MONTH will give you the YYYY-WW year and week number (weeks beginning Sunday) for the last six months. Much easier than all those case statements. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 28, 2015 Author Share Posted October 28, 2015 (edited) As you wrote it, no, as I put the missing FROM, no. SQL dump attached to previous message. Edited October 28, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2015 Share Posted October 29, 2015 "lot_type" table? Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 29, 2015 Author Share Posted October 29, 2015 SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for lot_type -- ---------------------------- DROP TABLE IF EXISTS `lot_type`; CREATE TABLE `lot_type` ( `lot_type_id` int(11) NOT NULL AUTO_INCREMENT, `lot_type_description` varchar(10) DEFAULT NULL, PRIMARY KEY (`lot_type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of lot_type -- ---------------------------- INSERT INTO `lot_type` VALUES ('1', 'Lot'); INSERT INTO `lot_type` VALUES ('2', 'Slab'); INSERT INTO `lot_type` VALUES ('3', 'Dried In'); INSERT INTO `lot_type` VALUES ('4', 'Frame'); INSERT INTO `lot_type` VALUES ('5', 'Drywall'); Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2015 Share Posted October 29, 2015 $active_column = 'active_specialty'; BTW, there is no "active_specialty" column Does this do it? SELECT C.yrwk , sum(slab) as slab , sum(dried_in) as dried_in , sum(drywall) as drywall , sum(frame) as frame FROM ( (SELECT DATE_FORMAT(l.frame_date, '%X-%V') as yrwk, null as slab, null as drywall, COUNT(IF(l.frame_date is not null, 1, 0)) AS frame, null AS dried_in FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = 2 ) AND l.active=1 AND l.lot_type_id <> 1 and l.frame_date is not null and (frame_date <= CURDATE() and frame_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by yrwk ) UNION ALL (SELECT DATE_FORMAT(l.drywall_date, '%X-%V') as yrwk, null as slab, COUNT(IF(l.drywall_date is not null, 1, 0)) AS drywall, null as frame, null AS dried_in FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = 2 ) AND l.active=1 AND l.lot_type_id <> 1 and l.drywall_date is not null and (drywall_date <= CURDATE() and drywall_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by yrwk ) UNION ALL (SELECT DATE_FORMAT(l.slab_date, '%X-%V') as yrwk, COUNT(IF(l.slab_date is not null, 1, 0)) AS slab, null as drywall, null as frame, null AS dried_in FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = 2 ) AND l.active=1 AND l.lot_type_id <> 1 and l.slab_date is not null and (slab_date <= CURDATE() and slab_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by yrwk ) UNION ALL (SELECT DATE_FORMAT(l.dried_in_date, '%X-%V') as yrwk, null AS slab, null as drywall, null as frame, Count(IF(l.dried_in_date is not null, 1 , 0)) AS dried_in FROM lot as l INNER JOIN lot_type AS lt ON l.lot_type_id = lt.lot_type_id INNER JOIN block as b ON b.block_id=l.block_id INNER JOIN community as c ON c.community_id=b.community_id WHERE ( c.contract_type_id = 1 OR c.contract_type_id = 2 ) AND l.active=1 AND l.lot_type_id <> 1 and dried_in_date is not null and (dried_in_date <= CURDATE() and dried_in_date >= DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -11 month), interval -(day(DATE_ADD(CURDATE(),INTERVAL -11 month)) - 1) day)) GROUP by yrwk ) ) as C GROUP BY C.yrwk; Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 29, 2015 Author Share Posted October 29, 2015 (edited) BTW, there is no "active_specialty" column Oops, I gave you a dump of the live server with real data. active_specialty is on the Dev version. Same thing as active, just ones or zeros. I will test the query. Need to make sure it works with the chart as well. Thanks for your efforts. Edited October 29, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.