Scenario
It was required to take a full extract of Service Requests from database. But there was a calculated field “Days Open” as follows:
IIf ([Status] = LookupValue(“SR_STATUS”, “Open”), Timestamp() – [Created],[Updated] – [Created])
Now the target was to design an Oracle SQL for calculating number of days a Service Request was open.
Solution
select s.row_id
, s.SR_STAT_ID
, To_char(s.created, ‘MM/DD/YYYY HH24:MI:SS’) “Created”
, To_char(s.last_upd, ‘MM/DD/YYYY HH24:MI:SS’) “Last_Updated”
, To_char(sysdate, ‘MM/DD/YYYY HH24:MI:SS’) “Today”
, Case
When s.SR_STAT_ID = ‘Open’ Then to_char (sysdate – s.created, ‘999999D99’)
Else to_char (s.last_upd – s.created, ‘999999D99’)
End “Days Open”
from SIEBEL.S_SRV_REQ s;
Download SQL – Date Difference
Please note that I have utilized To_Char function with an explicit format specification.
Cracking Siebel 