SQL Server - how to use a derived table on a calculated field?
I am trying to use a calculated field value in a WHERE clause in my query and after some research I know that I need to create a derived table. However, I am not sure about the syntax as my calculated field uses a CASE statement like:
CASE T.IsReassigned
WHEN 1 THEN DATEDIFF(MINUTE,
(SELECT top 1 SXAVWFTaskHistory.CreatedWhenUTC from SXAVWFTaskHistory where TaskID = T.TaskID and StatusID = 7 order by TaskHistoryID desc),
SYSDATETIMEOFFSET())
ELSE
CASE Stat.StatusID
WHEN 1 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
WHEN 2 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
ELSE DATEDIFF(MINUTE,TSK.CreatedWhenUTC, TH.CreatedWhenUTC)
END
END as TaskItemAge,
Then I want to use the TaskItemAge value in the where clause something like:
WHERE TaskItemAge > @TaskAgeStart AND TaskItemAge < @TaskAgeEnd
How do I put this CASE statement in a subselection?
************** EDIT ************ Sorry, I'm still a little confused how this would work. Here is a large snippet of my request:
SELECT TSK.TaskID, --0
TH.IsLatest,
TH.CreatedWhenUTC as TaskHistoryCreationDate, --10
TS.Name AS STATE,
CASE T.IsReassigned
WHEN 1 THEN DATEDIFF(MINUTE,
(SELECT top 1 SXAVWFTaskHistory.CreatedWhenUTC from SXAVWFTaskHistory where TaskID = T.TaskID and StatusID = 7 order by TaskHistoryID desc),
SYSDATETIMEOFFSET())
ELSE
CASE Stat.StatusID
WHEN 1 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
WHEN 2 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
ELSE DATEDIFF(MINUTE,TSK.CreatedWhenUTC, TH.CreatedWhenUTC)
END
END as TaskItemAge,
ctx.ContextTypeName,
ctx.ContextDescription, --15
TH.TouchedWhenUTC as TaskHistoryModifiedDate
INTO #ii
FROM SXAVWFTask TSK
INNER JOIN SXAVWFTaskHistory TH ON TSK.TaskID = TH.TaskID
INNER JOIN @PagedTemp T on TH.TaskHistoryID = T.TaskHistoryID
Where TaskItemAge > @TaskAgeStart AND TaskItemAge < @TaskAgeEnd
+3
source to share
3 answers
Andomar is the correct form (+1), here is your specific syntax:
SELECT *
FROM (
SELECT *,
CASE T.IsReassigned
WHEN 1 THEN DATEDIFF(MINUTE,
(SELECT top 1 SXAVWFTaskHistory.CreatedWhenUTC from SXAVWFTaskHistory where TaskID = T.TaskID and StatusID = 7 order by TaskHistoryID desc),
SYSDATETIMEOFFSET())
ELSE
CASE Stat.StatusID
WHEN 1 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
WHEN 2 THEN DATEDIFF(MINUTE,TSK.CreatedWhenUTC, SYSDATETIMEOFFSET()) -- time duration between when task is created(use SXAVWFTask.CreatedWhenUTC) and now
ELSE DATEDIFF(MINUTE,TSK.CreatedWhenUTC, TH.CreatedWhenUTC)
END
END as TaskItemAge
FROM BaseTable
) as DerivedTable
WHERE TaskItemAge > @TaskAgeStart AND TaskItemAge < @TaskAgeEnd
+1
source to share