Can a computed column in design reference another in sql server?

Question

I have 2 computed columns, [StartTime] and [EndTime]

The [StartTime] and [EndTime] are calculated with a formula using the [Week] and [Year] columns.

Now I need another computed column, [Status] that is calculated using the first two. But it gives me an error in formula when I try to use one of them inside the formula of [Status]

I really need this to work as I have no alternative. But is this even possible?

Here you go Mr -1 :

(case when [IsVOR]=(1) then 'VOR' 
      when [MarkedAsCompleteOn] IS NULL AND [Year]<datepart(year,getdate()) then 'Overdue' 
      when [MarkedAsCompleteOn] IS NULL AND [Year]>datepart(year,getdate()) then 'Not Due' 
      when [MarkedAsCompleteOn] IS NULL AND [Year]=datepart(year,getdate()) AND [Week]<datepart(iso_week,getdate()) then 'Overdue' 
      when [MarkedAsCompleteOn] IS NULL AND [Year]=datepart(year,getdate()) AND [Week]=datepart(iso_week,getdate()) then 'Due' 
      when [MarkedAsCompleteOn] IS NULL AND [Year]=datepart(year,getdate()) AND [Week]>datepart(iso_week,getdate()) then 'Not Due' 
      when [MarkedAsCompleteOn] IS NOT NULL AND [Year]<datepart(year,[MarkedAsCompleteOn]) then 'Late' 
      when [MarkedAsCompleteOn] IS NOT NULL AND [Year]>datepart(year,[MarkedAsCompleteOn]) then 'Early' 
      when [MarkedAsCompleteOn] IS NOT NULL AND [Year]=datepart(year,[MarkedAsCompleteOn]) AND [Week]<datepart(iso_week,[MarkedAsCompleteOn]) then 'Late' 
      when [MarkedAsCompleteOn] IS NOT NULL AND [Year]=datepart(year,[MarkedAsCompleteOn]) AND [Week]=datepart(iso_week,[MarkedAsCompleteOn]) then 'On Time' 
      when [MarkedAsCompleteOn] IS NOT NULL AND [MarkedAsCompleteOn]<[AllocatedTimeStart] then 'Early'  end)

The last part of it causes the error :

[MarkedAsCompleteOn]<[AllocatedTimeStart] then 'Early'

And the error is generic :

- Error validating the formula for column 'Status'.

Maybe not ideal but you can always compute the status based on the week and year columns....copy-pasta...so yes, it is possible... — rene, Jan 02 '13 at 10:31
@rene That's what I am currently doing but its causing issues with accuracy. Like for example 31st of Dec is showing as week 53, it would be -alot- easier if I could just use the dates to limit. — sprocket12, Jan 02 '13 at 10:34
possible duplicate of [SQL Server Reference a Calculated Column](http://stackoverflow.com/questions/413764/sql-server-reference-a-calculated-column) — rene, Jan 02 '13 at 10:52
What week *should* Dec 31 show as? There are after all more than 52 weeks in a year. — AakashM, Jan 02 '13 at 11:05
@AakashM according to ISO, it should be week 1. iso_week, my problem is the thing gets too complex, so I have found how to put it into a function. — sprocket12, Jan 02 '13 at 11:26

score 0 · Answer 1 · answered Jan 02 '13 at 10:57

0

No. One computed column can not be based on a different computed column. There's a specific error for this (1759):

Computed column '%s' in table '%s' is not allowed to be used in another computed-column definition.

You could create a view, based on the table, and perform a second round of computations within the view definition. Whether you then perform all activity against the view (adding triggers if required), or only use it for lookup is a design decision you'd need to make.

Of course, one you add the idea of using a view, you can build up multiple (not just 2) layers of computation by using a Common Table expression.

You do lose the ability to make the computed column persisted, unless the view is eligible for becoming an indexed view - not that this matters in this case, since it seems to be based on date calculations, so probably not persistable anyway.

answered Jan 02 '13 at 10:57

Damien_The_Unbeliever

234,701
27
340
448

Unfortunately I have an app in production that relies on this table with the [Status] working. At the moment it gives incorrect results so I need a fix in-place. I am experimenting with a function, so hopefully that will work out. I was just wondering is it possible to declare variables in the formula for computed fields? – sprocket12 Jan 02 '13 at 11:28
@MuhammadA - you can *rename* the table, then create a view using the original name of the table. Provided the view is updatable (or, if needed, you create the required triggers), the original application should be ignorant of this change behind the scenes. That's what I was vaguely referring to when I said about performing all activity against the view. (It's also one of the reasons I don't like people using hungarian style prefixes with database objects - you shouldn't *know* or *care* whether you're working against a table or a view) – Damien_The_Unbeliever Jan 02 '13 at 11:39
Thanks for your suggestions Damien, but my solution works and at the moment I feel like, "if it isn't broken don't fix it". – sprocket12 Jan 02 '13 at 11:57

score 0 · Accepted Answer · answered Jan 02 '13 at 11:55

I found my own solution in the end, by using a function, this kept my production app going without any changes.

USE [DBNAME]
GO
/****** Object:  UserDefinedFunction [dbo].[GetStatus]    Script Date: 01/02/2013 11:08:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetStatus](@CompletedOn DATETIME, @Year INT, @Week INT)
RETURNS NVARCHAR(MAX)
AS BEGIN
    DECLARE @CurrentDate DATETIME
    DECLARE @StartTime DATETIME
    DECLARE @EndTime DATETIME
    DECLARE @Status NVARCHAR(MAX)

    SET @CurrentDate = GETDATE()
    SET @StartTime = (dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+'-01-01'),(1)))))
    SET @EndTime = (dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+'-01-01'),(8)))))

    SET @Status = '-'
    IF(@CompletedOn IS NULL)
        BEGIN
            IF(@CurrentDate < @StartTime)
                SET @Status = 'Not Due'
            IF(@CurrentDate > @StartTime AND @CurrentDate < @EndTime)
                SET @Status = 'Due'
            IF(@CurrentDate > @EndTime)
                SET @Status = 'Overdue'
        END
    ELSE
        BEGIN
            IF(@CompletedOn < @StartTime)
                SET @Status = 'Early'
            IF(@CompletedOn > @EndTime)
                SET @Status = 'Late'
            ELSE
                SET @Status = 'On Time'
        END

    RETURN @Status
END

And in the status field I put the formula :

([dbo].[GetStatus]([MarkedAsCompleteOn],[Year],[Week]))

This is my first ever function in sql server, so it may not be optimum.

Can a computed column in design reference another in sql server?

2 Answers2