Can you please elaborate further when you say it is not getting the right results?
I just did the same thing at my end and the results seem to be OK.
Here is the SQL it generated.
`HPD:Help Desk`.`Incident Number`,
`HPD:Help Desk`.`Submit Date`,
DATEDIFF('day', `HPD:Help Desk`.`Submit Date`, '2019-03-19')
FROM `AR System Schema`.`HPD:Help Desk`
Thanks. However that note give me the days of how long the ticket was open for. for e.g i have submitted a ticket today and only resolved it on Monday, the days should be 5 Days, maybe with hours. I have tried you solution but it doesn't give me what we looking for.
Maybe there is something wrong with the version the client is on (9.1.02.003). I will test with 9.1.04 and check if it does the same behavior
You need to understand the solution first, When I suggested the solution it was based on open tickets and It will calculate from the submit date to now ( current system time). You need to calculate the age based on open and closed/resolved status. open will use the suggested solution and closed/resolved will use the predefined formula which you used and instead of current date, you will use last resolved date.
Unfortunately there is no straight forward solution.
I hope this helps.