Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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

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
portlight
Asked:
portlight
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
portlightAuthor Commented:
That actually gave me an error  on all date ranges.
0
 
portlightAuthor Commented:
Also the date range of dec 31, 13 to jan 1, 14 give me an error with the original code.
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
portlightAuthor Commented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now