Jump to content

Simple Query


benanamen

Recommended Posts

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>

post-179806-0-19814600-1446073493_thumb.jpg

post-179806-0-02655400-1446073794_thumb.jpg

Edited by benanamen
Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

$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;
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.