DateDiff on custom column in Table transformer not working

[Charles Middlebrooks]

I am using Table Transformer to uses the "Create" column to create a new column "MTTR Projection" that adds days to the create column. I then created an additional column to calculate the difference between the new "MTTR Projection" column and the "Resolved Column" called "MTTR Variance". 

I feel like this was working previously but now just loads 0 even when there is a resolved date. I'm wondering if its the date format, or if my SQL isnt written correctly to reference a previously created column.

SELECT *,
FORMATDATE(DATE_SUB(T1.'Created', INTERVAL -126 DAY)) as 'MTTR Target',
DATEDIFF(day,T1.'Resolved', 'MTTR Target')
AS 'MTTR Variance'
FROM T1

[Stiltsoft support · Accepted solution]

Hi @Charles Middlebrooks ,

To refer to the previously created virtual column, you need to use an internal SELECT:

• In the internal SELECT you create the 'MTTR Target' column
• In the external SELECT you refer to the 'MTTR Target' column for further operations

So, your query will be as following:

SELECT *,
DATEDIFF(day, 'Resolved', 'MTTR Target')
AS 'MTTR Variance'
FROM
(SELECT *,
FORMATDATE(DATE_SUB(T1.'Created', INTERVAL -126 DAY)) as 'MTTR Target'
FROM T1)

Please note that in the internal SELECT we can refer to the columns as T1.'Column_name'. But in the external SELECT we use only 'Column_name' syntax as we now refer not to the original T1 table but to a modified one.

Also I noticed that your date format for the 'MTTR Target' differs from the 'Created' and 'Resolved' fields. You may go to the macro settings tab and set the date format as "M d, yy" (or add hours and minutes if required as well).

Hope this helps your case.

[Charles Middlebrooks]

Thanks, thats perfect. Thanks for explaining as well.