Solved

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

Posted on 2014-01-02
5
231 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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