SQLTeam.com | Weblogs | Forums

Duration (max number of days betwwen 2 different closing days)


#1

I am working in Logi to create a formula for a field. Essentially I want the formulas to calculate the Maximum number of days it takes to close out an incident. I have the following values:
RM Close Date: Incidents.CustomDate37
PR Close Date: Incidents.CustomDate35
Report Date: Incidents.ReportDate

I want to find out the Maximum days it takes to close out an incident. the following formula does not give me what I need:
CASE
WHEN Incidents.CustomDate37 > Incidents. CustomDate35
THEN Incidents.CustomDate37- Incidents.ReportDate
ELSE Incidents. CustomDate35- Incidents.ReportDate
END

I also tried a Max datediff, but that did not work either. I am new to writing in SQL, but the above is essantaillly what I need in English, its just not giving my the number of days that I need,:

MAX (DATEDIFF (day, (Incidents. CustomDate35- Incidents.ReportDate),
(Incidents.CustomDate37-
Incidents.ReportDate))


#2

Something like this perhaps:

select max(datediff(day
                   ,incedents.reportdate
                   ,case
                       when incedents.customdate37>indecents.customdate35
                       then incedents.customdate37
                       else incedents.customdate35
                    end
                   )
          )
  from yourtable