Solved

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

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Insert Query Help 16 87
Limit on number of values in mysql in clause 3 41
generate a dynamic mysql query 2 37
Mysqli Query 5 41
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

21 Experts available now in Live!

Get 1:1 Help Now