Solved

getting a null error when using today's dates and yesterday's dates

Posted on 2014-01-02
5
232 Views
Last Modified: 2014-01-14
I have run into a scenario that is giving me a null error and do not know how to fix it - can't even figure out where the null error is throwing.

CREATE PROCEDURE reportFreeCooling(
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE startDateOriginal VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);
  DECLARE xHours varchar (50);
  DECLARE endHoursNull varchar(50);
  DECLARE endHoursNotNull varchar (50);
  DECLARE firstRow INT;
  DECLARE firstRowDate varchar(50);
  DECLARE minRowDate varchar (50);
  DECLARE minRow int;

  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET startDateOriginal = FROM_UNIXTIME(fromDate/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%');

  DROP TABLE IF EXISTS tempTable3 CASCADE;



  SET @sql = NULL;
  SET @sql = CONCAT(
 'CREATE TEMPORARY TABLE tempTable3 AS (SELECT
    @row := @row + 1 AS rownum,
    a.logId,a.name AS Type,
    L1.recordId,
    L2.recordId AS next_recordId,
    L1.completed,
    L2.completed AS next_completed,
    L3.completed AS first_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'', ''', timeZone, ''') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''', timeZone, ''') AS endingDate,
    (L1.item', myItemID, ' - L1.item', myItemID2, ') AS itemDifference,
    ((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(T2.completed) 
            FROM log',mylogID, ' T2
            WHERE T2.completed > T1.completed) AS next_completed
         , (SELECT MAX(T2.completed) 
            FROM log',mylogID, ' T2
            WHERE T2.completed < T1.completed) AS frst_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 log', mylogID, ' L3 ON (L3.completed = TimeOrder.frst_completed)
        LEFT JOIN activities a ON L1.activityId = a.activityId
        LEFT JOIN logs l ON a.logId = l.logId
        LEFT 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; 

    /* sets next row hours when date is after startdate and first is null */
    SET @startDate = (SELECT MIN(completed) FROM tempTable3 WHERE coolingHours is NULL AND 
        completed BETWEEN startDate AND endDate);
    SET @startDate2 = (SELECT MAX(completed) FROM tempTable3 WHERE completed < @startDate);
    SET startDate = @startDate2;

   /* sets end hours when last row is enabled and next_completed is greater than end date */
   SET @endHoursNotNull = (SELECT MAX(completed) AS maxCompletedDateAndCoolingHoursNotNull FROM tempTable3 WHERE coolingHours is NOT NULL AND 
        completed BETWEEN startDate AND endDate);
   SET endHoursNotNull= @endHoursNotNull;
   SET @endHoursNull = (SELECT MAX(completed) AS maxCompletedAndCoolingHoursNull FROM tempTable3 WHERE coolingHours is NULL AND 
        completed BETWEEN startDate AND endDate);
   SET endHoursNull = @endHoursNull;

      /* sets first row hours when date is after startdate and first enabled */
    SET firstRowDate = (SELECT MIN(completed) FROM tempTable3);
    IF firstRowDate > startDateOriginal THEN SET startDateOriginal = firstRowDate; END IF;
   SET @newcoolingHours = (SELECT (time_to_sec(timediff(next_completed, startDateOriginal)) / 3600) AS newCoolingHours 
        FROM tempTable3 WHERE completed >= startDateOriginal 
                          AND completed BETWEEN startDate AND endDate LIMIT 1);
   SET xHours = @newCoolingHours;

   SET minRowDate = (SELECT MIN(completed) FROM tempTable3 WHERE completed BETWEEN startDate AND endDate LIMIT 1 );
   SET minRow = (SELECT MIN(rowNum)FROM tempTable3 WHERE completed BETWEEN startDate AND endDate LIMIT 1 );
   /* set first row number and hours for when first row in log(itemId) is greater then startdate */

  SET @sqlTemp = NULL;
  SET @sqlTemp = 
   CONCAT(
  'SELECT   rowNum,l.completed,l.next_completed,l.first_completed,startingDate,endingDate,Type,itemDifference,
        ''',startDateOriginal,''' AS oStartDate, ''',startDate,'''  as startDate,
   (time_to_sec(timediff(next_completed, completed)) / 3600)AS cHours, 
   CASE
        WHEN (''',startDateOriginal,''' > ''',startDate,''' AND Type LIKE ''%Enable%'' AND rowNum = ''',minRow,''')
            THEN (time_to_sec(timediff(next_completed, ''',startDateOriginal,''')) / 3600)
        WHEN (DAYOFYEAR(completed) = DAYOFYEAR(''',startDate,'''))
            THEN ''',xHours,'''
        WHEN (''',endHoursNotNull,''' > ''',endHoursNull,''' AND next_completed > ''',endDate,''' )
            THEN ((time_to_sec(timediff(''',endDate,''', completed))) / 3600)
        ELSE coolingHours
    END AS coolingHours,
    isError,charge,totalTimeRange,
    l.logId,databaseName,i.name,l.itemId,
    CASE 
        WHEN (''',startDateOriginal,''' > ''',startDate,''' AND Type LIKE ''%Enable%'' AND rowNum = ''',minRow,''')
            THEN (itemDifference*(time_to_sec(timediff(next_completed, ''',startDateOriginal,''')) / 3600))
        WHEN (DAYOFYEAR(completed) = DAYOFYEAR(''',startDate,'''))
            THEN (''',xHours,'''* itemDifference)
        WHEN (''',endHoursNotNull,''' > ''',endHoursNull,''' AND next_completed > ''',endDate,''' )
            THEN (itemDifference*((time_to_sec(timediff(''',endDate,''', completed))) / 3600))
        ELSE kwDifference
    END AS kwDifference,cost, 
   CASE
        WHEN (''',startDateOriginal,''' > ''',startDate,''' AND Type LIKE ''%Enable%'' AND rowNum = ''',minRow,''')
            THEN ((time_to_sec(timediff(next_completed, ''',startDateOriginal,''')) / 3600)* cost)
        WHEN  (DAYOFYEAR(completed) = DAYOFYEAR(''',startDate,'''))
            THEN (''',xHours,''' * cost)
        WHEN  (''',endHoursNotNull,''' > ''',endHoursNull,''' AND next_completed > ''',endDate,''' )
            THEN (((time_to_sec(timediff(''',endDate,''', completed))) / 3600) * cost)
        ELSE costT
    END AS 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, '''

 '); 



PREPARE stmtTemp FROM @sqlTemp;
 EXECUTE stmtTemp;
 DEALLOCATE PREPARE stmtTemp;

 DROP TEMPORARY TABLE tempTable3;

END //


DELIMITER ;

Open in new window

I do know that is has to do with the last row of data and the next_completed being null

last rows of data

row Num completed   next_completed  first_completed starting Date   ending Date
28  12/28/2013 6:30:35 PM   12/29/2013 12:07:19 AM  12/27/2013 11:22:54 PM  12/31/1969 10:00:00 PM  12/31/2013 10:00:00 PM
29  12/29/2013 12:07:19 AM  12/29/2013 8:56:58 AM   12/28/2013 6:30:35 PM   12/31/1969 10:00:00 PM  12/31/2013 10:00:00 PM
30  12/29/2013 8:56:58 AM   12/31/2013 11:27:40 PM  12/29/2013 12:07:19 AM  12/31/1969 10:00:00 PM  12/31/2013 10:00:00 PM
31  12/31/2013 11:27:40 PM  (null)  12/29/2013 8:56:58 AM   12/31/1969 10:00:00 PM

Open in new window

0
Comment
Question by:portlight
  • 3
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39751528
I have not had a chance to look through the entire code block, but I suspect your guess is correct as your data samples contains records for neither yesterday (1 JAN) nor today (2 JAN); therefore, if your start and end dates are both after the max row in your data, you will get NULL from this line:

SET @startDate = (SELECT MIN(completed) FROM tempTable3 WHERE coolingHours is NULL AND completed BETWEEN startDate AND endDate);

Consequently, this affects the next line:
SET @startDate2 = (SELECT MAX(completed) FROM tempTable3 WHERE completed < @startDate);

Since @startDate is NULL from the line above, this query results in NULL because you cannot do a direct comparison with NULL.

I think this may work better:
SET @startDate = (SELECT MAX(completed) FROM tempTable3 WHERE coolingHours is NULL AND completed <= @endDate);

Let me know if that is not the solution, and I will take a closer look.
0
 

Author Comment

by:portlight
ID: 39751641
That actually gave me an error  on all date ranges.
0
 

Author Comment

by:portlight
ID: 39751648
Also the date range of dec 31, 13 to jan 1, 14 give me an error with the original code.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39751680
AH, I see you are passing the dates as VARCHAR (50) then using FROM_UNIXTIME( {date} / 1000) to convert.  Therefore, the trouble may be in the conversion (input).  In addition, I realize my understanding was backwards.  You are finding the MIN date in the range, then finding the MAX date in the table less than that date, so...

SET @startDate = (SELECT MAX(completed) FROM tempTable3 WHERE coolingHours is NULL AND completed <= @startDate);

However, the problem of data type still exists:

DECLARE startDate VARCHAR (50);
DECLARE endDate   VARCHAR (50);

What is the data type in the table?  

Note that BETWEEN and other other comparison against strings will not function correctly for dates unless you are using numeric representation that properly sorts chronologically as an alphanumeric (e.g., 2013-12-01, 2014-01-01, et cetera).

P.S. the use of VARCHAR(50) makes the FROM_UNIXTIME(...) even more suspicious because it suggests the input should be a number representing a Unix-style time stamp.
0
 

Author Comment

by:portlight
ID: 39751805
The varchar is do to the way the application send the data to the application. I have gone line by line toile sure the calculations are working acccurately. I think what I need to do is put some error checking in for the last row next completed is null. At first I thought it was an issue with just 24 hour time frame but other times between work fine
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 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