Solved

Need to set all rows value based on the top row results

Posted on 2013-12-19
3
309 Views
Last Modified: 2013-12-19
I need to select the top row AND l1.activityId = t2.activityId  Then  I need to be able to set ALL rows of a.scheduleType to be equal to 'error' - NOT just the rows where the above is true.

CREATE PROCEDURE reportFreeCoolingTrackerNoErrors (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            
SELECT 
   l1.item31985,l1.item31987,a.scheduleType,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE 0
    END AS errorCheck  

  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started 
        BETWEEN startDate
            AND endDate
     ORDER BY l1.recordId,l1.started;
       
    END //

DELIMITER ;

Open in new window

0
Comment
Question by:portlight
  • 3
3 Comments
 

Author Comment

by:portlight
Comment Utility
This code achieves exactly what I want it to except it sends two sets of query data. The data is being passed to a report and I want ONLY the results of the IF to return in the result set

DELIMITER //
/*  Author: Jessie Brown     
    Date:   8/17/13 
    Notes:  Template Creation

    Comments: Comments created in the header area will NOT be stored in the database write
              Comments created AFTer the begin statement will be retained within the database write
*/

-- checks to see if stored procedure exists
-- if exists the procedure is dropped and recreated

DROP PROCEDURE IF EXISTS `reportFreeCoolingTrackerCalls`//

-- Creates new procedure

CREATE PROCEDURE reportFreeCoolingTrackerCalls (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
            DECLARE ActivityID1 Int;
            DECLARE ActivityID2 Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
            

SELECT @ActivityID1 := l1.activityId, @ActivityID2 := t2.activityId, CASE
        when l1.activityId = t2.activityId THEN 0
        ELSE 1
    END AS errorCheck 
  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate 
      ORDER BY errorCheck ,l1.recordId,l1.started LIMIT 1;

     IF (@ActivityID1 = @ActivityID2) 

      THEN SELECT g.groupId,
    g.name AS groupName,
    g1.parentId AS parentId1, 
    g1.name AS group1Name,
    g2.parentId AS parentId2, 
    g2.name AS group2Name,
    g3.parentId AS parentId3,
    g3.name AS group3Name,
    l.logId,l.name AS logName,
    l.ordering AS logOrder,
    a.activityId AS activityId,
    a.ordering AS activityOrder,
    a.name AS activityName,
    l1.recordId,
    l1.started, l1.completed,
    l1.userId,l1.note,
    u.name,
    TO_SECONDS(t2.completed) - TO_SECONDS(l1.completed) AS timeInSeconds,
    substr(l.details, instr(l.details , ':' ) +1)AS charge,l.details,
    i.itemId,
    i.name AS itemName,
    i.itemType,
    i.details,l1.item31985,l1.item31987,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE 0
    END AS errorCheck, t2.completed AS errorDay,a.scheduleType  

  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257 AND l1.activityId = t2.activityId 
      AND l1.started
        BETWEEN startDate
            AND endDate
     ORDER BY l1.recordId,l1.started;

    ELSEIF  (@ActivityID1 != @ActivityID2)
       THEN 
      SELECT g.groupId,
    g.name AS groupName,
    g1.parentId AS parentId1, 
    g1.name AS group1Name,
    g2.parentId AS parentId2, 
    g2.name AS group2Name,
    g3.parentId AS parentId3,
    g3.name AS group3Name,
    l.logId,l.name AS logName,
    l.ordering AS logOrder,
    a.activityId AS activityId,
    a.ordering AS activityOrder,
    a.name AS activityName,
    l1.recordId,
    l1.started, l1.completed,
    l1.userId,l1.note,
    u.name,
    TO_SECONDS(t2.completed) - TO_SECONDS(l1.completed) AS timeInSeconds,
    substr(l.details, instr(l.details , ':' ) +1)AS charge,l.details,
    i.itemId,
    i.name AS itemName,
    i.itemType,
    i.details,l1.item31985,l1.item31987,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE 0
    END AS errorCheck, t2.completed AS errorDay,a.scheduleType  

  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257 AND l1.activityId != t2.activityId 
      AND l1.started
        BETWEEN startDate
            AND endDate
     ORDER BY l1.recordId,l1.started;
            END IF;

  
    END //

DELIMITER ;
                                            

Open in new window

0
 

Accepted Solution

by:
portlight earned 0 total points
Comment Utility
I wound up addressing this in the report. I set error to null if the two fields did not match. I then grouped inside of the report and printed based on count.  Worked like a charm.

CREATE PROCEDURE reportFreeCoolingTracker (
        IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;
           

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);
             

SELECT g.groupId,
    g.name AS groupName,
    g1.parentId AS parentId1, 
    g1.name AS group1Name,
    g2.parentId AS parentId2, 
    g2.name AS group2Name,
    g3.parentId AS parentId3,
    g3.name AS group3Name,
    l.logId,l.name AS logName,
    l.ordering AS logOrder,
    a.activityId AS activityId,
    a.ordering AS activityOrder,
    a.name AS activityName,
    l1.recordId,
    l1.started, l1.completed,
    l1.userId,l1.note,
    u.name,
    TO_SECONDS(t2.completed) - TO_SECONDS(l1.completed) AS timeInSeconds,
    substr(l.details, instr(l.details , ':' ) +1)AS charge,l.details,
    i.itemId,
    i.name AS itemName,
    i.itemType,
    i.details,l1.item31985,l1.item31987,
    ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
    ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1))) AS cost,
    (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , ':' ) +1)))
    *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
      time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
      time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange,
    (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS startingDate, 
    (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS endingDate,DATABASE() AS databaseName,
     CASE
        when l1.activityId = t2.activityId THEN 1
        ELSE NULL
    END AS errorCheck, t2.completed AS errorDay,a.scheduleType  

  FROM logs l 
      INNER JOIN groups g ON g.groupId = l.groupId
      LEFT JOIN groups g1 ON g.parentId = g1.groupId
      LEFT JOIN groups g2 ON g1.parentId = g2.groupId
      LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
      INNER JOIN activities a ON l.logId = a.logId 
      INNER JOIN log1644 l1 ON a.activityId = l1.activityId 
      INNER JOIN log1644 t2 ON t2.recordId = l1.recordid + 1
      INNER JOIN items i ON l.logId = i.logId AND i.name LIKE '%KW%'
      INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE '%KW%'
     WHERE i.itemID = "31985"  AND  l1.activityId = 1257
      AND l1.started
        BETWEEN startDate
            AND endDate
     ORDER BY l1.recordId,l1.started;
 
    END //

DELIMITER ;

Open in new window

0
 

Author Closing Comment

by:portlight
Comment Utility
Never received a response from anyone and solved it myself
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now