5 Replies Latest reply: Mar 31, 2015 2:04 AM by anbu cheliyan RSS

    Row with missing timestamp is still selected

    Nemanja Momic

      Hello all,

       

      I have a problem with logs that distribution service makes.

       

      I want to get the name of tasks that have timestamp within some range, and I get the all of them, even it the value is null.

       

      Here is the sample script:

       

      TaskExecutionHistoryItemExtra:

      load

          *,

          date#(Left(TaskExecutionHistoryItem.StartTimeStamp,10)) as TaskExecutionHistoryItem.Date,

          interval(Sum(TaskExecutionHistoryItem.Duration), 'hh:mm:ss') as TaskExecutionHistoryItem.TotalExecTime

      Resident TaskExecutionHistoryItem_tmp

      Where TaskExecutionHistoryItem.StartTimeStamp >= Date(Date#(Today(), 'DD.MM.YYYY')-8,'') & ' ' & '00:00:00'

        and TaskExecutionHistoryItem.StartTimeStamp <= Date(Date#(Today(), 'DD.MM.YYYY')-1,'') & ' ' & '23:59:59'

        and not Isnull(TaskExecutionHistoryItem.Duration) and Len(trim(TaskExecutionHistoryItem.Duration)) > 0

      Group by

          %TaskId,

          TaskExecutionHistoryItem.ID,

          TaskExecutionHistoryItem.StartTimeStamp,

          TaskExecutionHistoryItem.EndTimeStamp,

          TaskExecutionHistoryItem.Duration,

          TaskExecutionHistoryItem.TaskStatus,

          Month;

       

       

      Any of these fields can contain null value, and I want tasks that have all info

          TaskExecutionHistoryItem.StartTimeStamp,

          TaskExecutionHistoryItem.EndTimeStamp,

          TaskExecutionHistoryItem.Duration,