Simple Query

benanamen · October 28, 2015

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... :tease-03:

* 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

Barand · October 28, 2015

You will get better quicker answers if you post an SQL dump of your DB.

benanamen · October 28, 2015

LOL @Barand, so true.! :happy-04:

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

Barand · 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.

benanamen · October 28, 2015

As you wrote it, no, as I put the missing FROM, no.

SQL dump attached to previous message.

Edited October 28, 2015 by benanamen

Barand · October 29, 2015

"lot_type" table?

benanamen · 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');

Barand · 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;

benanamen · October 29, 2015

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

Sign In

Simple Query

Recommended Posts

benanamen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

benanamen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

benanamen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

benanamen

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

benanamen

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information