Im trying to calculate the number of days a DMR is open
we have 2 UD fields that populate the date a DMR is created and the Date the DMR is closed.
The calculation works fine for Closed DMRs but i want to calculate for Open DMRs the open date against Today . I have tried using GetDate() and Constant.Today both return a blank value.
One thing that I would check is the formatting of those fields. Are they Date, DateTime, something else. You might need to convert the Constants.Today to match what your fields are.
the UD fields are Date fields. I tried this and still nothing.
Just discovered that conversion is working but the case statement is what is broken.
So if the expression was either of:
Datediff(dd, DMHRHead.CreatedOn_c, DMRHead.ClosedOn_c)
or
Datediff(dd, DMHRHead.CreatedOn_c, convert(date, Constants.Today))
The Query runs without error (ignoring that it doesn’t do what you want)?
What about this as the expression:
(case when DMRHead.OpenDMR = 0 then 10 else -20 end)
Does it run fine giving your either 10 or -20 as you’d expect based on the DMR being open?
That calc field is of they type nvarchar. shouldn’t it be a number (integer or decimal)
got it to work. the DMRHead.OpenDMR was causing the issue. No idea why
Changed the criteria of the first case and it works.
