Solved

Set startDate to row above's time based on case statement

Posted on 2013-12-22
5
334 Views
Last Modified: 2013-12-26
I am trying to check to see if the value for column 'complete' of is less than the startDate and if it is then I need to set the startDate to that value so the row is included in the return set....
]

In looking at the data set here:

Table and code testing

I have several things not working as I would like

1. row 12 isError should = 1 as row 12 and row 13 are both actiivtyID 1257

2. if row 10 is the FIRST row returned in a select statement (using a where date between start and end) I need to set the start date to row 10's completed value
0
Comment
Question by:portlight
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39736451
1. row 12 (actual recordid12) activity is 1291, row 13 is 1257
2. I don't understand

for complete being less than startdate and setting to startdate just use a case statement for that column:
(case when complete < startdate then startdate else complete end) as complete
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 39736797
The LEAST() function is simpler than writing a CASE statement.

Look at the RECORDID and NEXTRECORDID for row 7 - it seems it should be between rows 12 and 13. Either a record id or a timestamp is in the wrong place.
0
 

Author Comment

by:portlight
ID: 39736817
you are correct that they are out of order - and for this project - that is okay!
(wrong as it may be!) they are able to go back and edit record times which put them out of wack -
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 39736930
Ok, but that explains why there's no value in isError - going by record ids, rather than rowNums, there are not two consecutive rows with the same activity id.
0
 

Author Comment

by:portlight
ID: 39741174
I actually fixed this by creating a temp table that picked up row Numbers based on times and not the actual recordId

 DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);

  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET mylogID   = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
  SET myItemID = (SELECT i.itemId FROM items i  WHERE i.name LIKE '%KW PRE%');
  SET myItemID2 = (SELECT i.itemId FROM items i  WHERE  i.name LIKE '%KW STA%');

  SET @sql = NULL;
  SET @sql = CONCAT(
 'CREATE TEMPORARY TABLE tempTable3 AS (SELECT
    @row := @row + 1 AS rownum,
    a.logId,
    L1.recordId,
    L2.recordId AS next_recordId,
    L1.completed,
    L2.completed AS next_completed,
    L1.activityId AS activityJoin,
    L2.activityId AS next_activityId,
    IF(L1.activityId = L2.activityId, 1, NULL) AS isError,
     CASE
        when a.name LIKE ''%Enable%'' THEN time_to_sec(timediff(L2.completed, L1.completed)) / 3600
        ELSE NULL
        END AS coolingHours,
     TO_SECONDS(L2.completed) - TO_SECONDS(L1.completed) AS timeInSeconds,
    ((SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS charge,
    time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
    CONVERT_TZ(''', startDate, ''', ''UTC'', ''US/Pacific'') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''US/Pacific'') AS endingDate,
    ((L1.item', myItemID, ' - L1.item', myItemID2, ') * 
        (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)))) / 3600 AS kwDifference,
    ((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS cost,
    ((((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details,'':'') + 1)))
    * (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600))) AS costT,
    DATABASE() AS databaseName, i.itemId ,l.details
FROM
    (SELECT @row:=0)R,
    (SELECT T1.completed,
       (SELECT MIN(completed)
         FROM log',mylogID, '
         WHERE completed > T1.completed) AS next_completed
      FROM log',mylogID, ' T1
      ORDER BY T1.completed
     )TimeOrder
        LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
        LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
        LEFT JOIN activities a ON L1.activityId = a.activityId
        LEFT JOIN logs l ON a.logId = l.logId
        inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
    )ORDER BY L1.completed');  

PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END //

Open in new window


Then called this temp table in stored procedure that returned rows I needed

BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);

  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET mylogID   = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
  SET myItemID = (SELECT i.itemId FROM items i  WHERE i.name LIKE '%KW PRE%');
  SET myItemID2 = (SELECT i.itemId FROM items i  WHERE  i.name LIKE '%KW STA%');

  CALL reportCreateFreeCoolingTempTable(fromDate,toDate,'UTC');
     
  SET @sql = NULL;
  SET @sql = CONCAT(
  'SELECT 
    rowNum,l.logId,l.completed,l.next_completed,isError,charge,totalTimeRange,startingDate,endingDate,coolingHours,
    databaseName,i.name,l.itemId, kwDifference, cost, costT,l.details,timeInSeconds

  FROM tempTable3 l
   inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%'' 
   WHERE l.itemId = ''',myItemId,'''
        AND completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
  ORDER BY completed');   

PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DROP TEMPORARY TABLE tempTable3;
 DEALLOCATE PREPARE stmt;

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
myqsl update statement on phpMyAdmin 8 37
category table 2 30
UPDATE query not working in mysqli php 8 61
Creating Functions in phpMyAdmin 8 26
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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