Solved

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

Posted on 2013-12-22
5
327 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 38

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

863 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

27 Experts available now in Live!

Get 1:1 Help Now