locked
DateDiff returning a blank column RRS feed

  • Question

  • I have two columns and I'm trying to use a DateDiff to get the difference, but every time I test it in ASA it returns a blank column. Here is my query:

    SELECT
        System.Timestamp AS Time,
        SUBSTRING ( service_unit.#text,1,3) AS Dock,
        service_unit.#text AS Service_Unit,
        CONCAT (longitude, ', ', latitude) AS location,
        datetimefromparts (
            year(system.timestamp),
            month(system.timestamp),
            day( system.timestamp),
            cast(substring ( endingtimeframe,1,2) AS bigint)+case when patindex('%PM', endingtimeframe) = 0 THEN 0 ELSE 12 end,
            cast(substring ( endingtimeframe,4,2) AS bigint),00,00) as endwindow,
        datetimefromparts (
            year(system.timestamp),
            month(system.timestamp),
            day( system.timestamp),
                    case when concat(plannedarrivaltime,'a') ='a' 
            then 0 else cast(substring(plannedarrivaltime,patindex('%:%',plannedarrivaltime)-2,2)
            as bigint) end,
            case when concat(plannedarrivaltime,'a') ='a' 
            then 0 else cast(substring(plannedarrivaltime,patindex('%:%',plannedarrivaltime)+1,2)
            as bigint) end,00,00) as ETA,
        datediff(minute, eta, endwindow)
    INTO
        [realtimeDT-ASA]
    FROM
        [realtime-alias]

    Wednesday, July 29, 2015 6:45 PM

Answers

  • DATEDIFF will only return NULL if one of the arguments is NULL. It looks like you are trying to apply DATEDIFF to the columns computed in the same SELECT statement - this is not supported. 'eta' and 'endwindow' are interpreted as column names from the original [realtime-alias] stream (anf hence NULL values)

    You can split your query into 2 steps and compute DATEDIFF in the second step. Something like this:

    WITH step1 AS
    (
     SELECT
       System.Timestamp AS Time,
       SUBSTRING ( service_unit.#text,1,3) AS Dock,
       service_unit.#text AS Service_Unit,
       CONCAT (longitude, ', ', latitude) AS location,
       datetimefromparts (
        year(system.timestamp),
        month(system.timestamp),
        day( system.timestamp),
        cast(substring ( endingtimeframe,1,2) AS bigint)+case when patindex('%PM', endingtimeframe) = 0 THEN 0 ELSE 12 end,
        cast(substring ( endingtimeframe,4,2) AS bigint),00,00) as endwindow,
       datetimefromparts (
        year(system.timestamp),
        month(system.timestamp),
        day( system.timestamp),
          case when concat(plannedarrivaltime,'a') ='a' 
        then 0 else cast(substring(plannedarrivaltime,patindex('%:%',plannedarrivaltime)-2,2)
        as bigint) end,
        case when concat(plannedarrivaltime,'a') ='a' 
        then 0 else cast(substring(plannedarrivaltime,patindex('%:%',plannedarrivaltime)+1,2)
        as bigint) end,00,00) as ETA,
       datediff(minute, eta, endwindow)
      FROM
       [realtime-alias]  
    )

    SELECT
     [Time],
     [Dock],
     [Service_Unit],
     [location],
     [endwindow],
     [eta],
     datediff(minute, eta, endwindow) 
    INTO
       [realtimeDT-ASA]
    FROM step1

    Saturday, August 1, 2015 9:31 PM