Solved

I need to account for time outside of date based on 1st result return

Posted on 2013-12-18
4
456 Views
Last Modified: 2013-12-19
Example scenario:
1. User selects Dec 1 2013 to Dec 31 2013 as date range for the report
2. The first record in this date range is Dec 5 2013 @ 9:00 for a DISABLED activity
3. The last record in the date range is Dec 28 2013 @ 18:00 for an ENABLED activity

In the above scenario, the report should assume that from Dec 1 2013 @ 00:00 until Dec 5 2013 @ 9:00 that the mode was ENABLED. In this case, it would get the previous record (even though it does not fall in the selected date range) and use it to partially calculate the KW PRe Free Cooling and KW Stabilized Free Cooling values.

For number 3 above, the report should assume that from Dec 28 2013 @ 18:00 up until Dec 31 2013 @ 11:59 that the mode was disabled and only calculate this time towards the total hours within the date range.


Current working stored procedure
CREATE PROCEDURE reportFreeCoolingTrackerTestDateTest (
        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 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 //

Open in new window

0
Comment
Question by:portlight
  • 2
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
This does not make sense to me
For number 3 above, the report should assume that from Dec 28 2013 @ 18:00 up until Dec 31 2013 @ 11:59 that the mode was disabled and only calculate this time towards the total hours within the date range.
Surley if the last event was ENABLED - then the state should be ENABLED going forward so the system should assume ENABLED from 28 Dec 18:00 to 31 Dec 11:59?

If I understand the data correctly the records denote change in state - so the DISABLED record on 5 December implies pre this record the system was ENABLED - so that is correct - just think the logic on the last record is wrong.
0
 

Author Comment

by:portlight
Comment Utility
julian
You are correct so what i would need to take in account is that there would NOT be a 'ending time for the last row - I would need to use endDate as the value to set the hours for that last row

The think I am having problems wrapping my head around is how to do the if - set statement  
I like this site because those who respond often help me brainstorm - I have been up all night working on this project so my thoughts are a little fuzzy right now!

Thank you for catching that.
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
Comment Utility
Ok - maybe spell out the problem a bit more - you have posted or SP which is good but it is quite involved and to get one's head around it without the table structure and data is a bit of schlepp.

For starts I am not clear on what you want to output - in terms of accounting for time outside of the first and last record within the date range - what needs to be done in terms of this?
0
 

Author Comment

by:portlight
Comment Utility
This code calculates the extra hours HOWEVER I only needed them added to the very first row. I have tried to put a min variable in there but then instead of selecting all my rows it only selects one.

CREATE PROCEDURE reportFreeCoolingTrackerDateTest (
        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 
    CASE
        when l1.completed > startDate AND a.name LIKE '%Disable%' AND  ROW_COUNT() = 1 
            THEN (time_to_sec(timediff(l1.completed,(CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) ))) / 3600)
       END AS newTime,l1.recordId,
    a.activityId, 
    DATABASE() AS databaseName, ((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,l1.completed, 
     (CONVERT_TZ( (FROM_UNIXTIME(fromDate/1000)),'UTC', timeZone) )AS StartingDate, 
     (CONVERT_TZ( (FROM_UNIXTIME(toDate/1000)),'UTC', timeZone) ) AS EndingDate,   
      timeZone  AS timeZonePassed

    
  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.started
        BETWEEN startDate
            AND endDate 
     ORDER BY l1.recordId,l1.started;
 
    END //

DELIMITER ;

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

14 Experts available now in Live!

Get 1:1 Help Now