Forum Discussion

Sandra Tibbs's avatar
Sandra Tibbs
Copper Contributor
Nov 21, 2025

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 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],
CASE 
WHEN [DAYS OPEN] > 60
THEN 1
ELSE 0
END AS [60DAYSORMORE]
 
From d_case as c
 
The issue I have is that the system will not recognize my [Days Open] as a field.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'DAYS OPEN'.
 
How can I make this work in Microsoft SQL Server Management Studio?
 

2 Replies

  • Viorel's avatar
    Viorel
    Copper Contributor
    Nov 21, 2025

    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

     

Resources