Forum Discussion
Sandra Tibbs
Nov 21, 2025Copper Contributor
Determine a Value from a customized field in a select statement.
I am new. I am looking to determine the value of a customized field based on the value of a previous customized field. Here is what I have so far: select c.*, CASE WHEN c.[Case End Date] is nul...
Viorel
Nov 21, 2025Copper Contributor
Try several possibilities:
select c.*, t.[Days Open], case when [DAYS OPEN] > 60 then 1 else 0 end as [60DAYSORMORE]
from d_case as c
cross apply (values (case when c.[Case End Date] is null then DATEDIFF(day, c.[Case Start Date], getdate()-1) else DATEDIFF(day, c.[Case Start Date], c.[Case End Date]) end)) as t([Days Open])
---------
select d.*, case when d.[DAYS OPEN] > 60 then 1 else 0 end as [60DAYSORMORE]
from
(
select c.*,
case when c.[Case End Date] is null then DATEDIFF(day, c.[Case Start Date], getdate()-1)
else DATEDIFF(day, c.[Case Start Date], c.[Case End Date]) end as [Days Open]
from d_case as c
) as d
---------
; with Q as
(
select *,
case when [Case End Date] is null then DATEDIFF(day, [Case Start Date], getdate()-1)
else DATEDIFF(day, [Case Start Date], [Case End Date]) end as [Days Open]
from d_case
)
select *, case when [DAYS OPEN] > 60 then 1 else 0 end as [60DAYSORMORE]
from Q