Solved

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to use Check Constraints in MySQL 2 36
Please help me with my database modeling 7 37
PHP connection to remote AWS MySQL RDS 4 64
join tables 4 50
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Creating and Managing Databases with phpMyAdmin in cPanel.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

14 Experts available now in Live!

Get 1:1 Help Now