December 29, 2011 at 5:06 pm
Hi.
I have the following column in a table:
startdate
from that column I calculate another column (this works) using the CASE statement. This gives me the following columns
STARTDATE DUEDATE
1/1/2010 1/1/2010 + 30 days (or whatever)
What I tried to do today was create a third calculated column using the case statement based on the DUEDATE.
STARTDATE DUEDATE OVERDUESTATUS
1/1/2010 2/1/2010 Overdue by 120 or more days
Is it even possible to do this? When I try it I get an error message stating that the first calcualted column "DUEDATE" is not valid. When I remove my CASE statements for OVERDUESTATUS everything works fine.
Thanks in advance.
December 29, 2011 at 6:36 pm
You can't reference a calculated column in another calculated column. However, you could encapsulate the logic of the first computed column in the definition for the second.
ALTER TABLE TableA ADD DueDate AS DATEADD(d,30,StartDate); -- First computed column.
ALTER TABLE TableA ADD OverDueStatus AS
CASE
WHEN GETDATE() > DATEADD(d,30,StartDate) THEN 'OVERDUE'
ELSE 'NOT OVERDUE'
END; -- Includes date logic for DueDate in second computed column
December 29, 2011 at 9:17 pm
Well gosh...whenever I ask a question on SSC I never get the answer I thought I needed....I always get something better.
I have been doing the calculation for DUEDATE in a query that makes a view. Now you've opened my eyes to the possibility of a calculated column in the source table. Would this be faster than doing the computation as part of the query that makes up the view?
Thank you.
December 30, 2011 at 1:19 am
ckoster (12/29/2011)
Well gosh...whenever I ask a question on SSC I never get the answer I thought I needed....I always get something better.I have been doing the calculation for DUEDATE in a query that makes a view. Now you've opened my eyes to the possibility of a calculated column in the source table. Would this be faster than doing the computation as part of the query that makes up the view?
Thank you.
Oh, I didn't realize you were doing this in a view...you were talking about "calculating a column" and I just assumed you were talking about adding a calculated column to the source table. 😀 If you're doing this as part of a view, though, you can take the same approach...include the DUEDATE calculations again as part of the OVERDUESTATUS. However, doing this in a view, you have a few other options as well. For example, you can base your OVERDUESTATUS on a derived table:
SELECT ID, StartDate, DueDate, CASE WHEN GETDATE() > DUEDATE THEN 'OVERDUE' ELSE 'ON TIME' END AS OverDueStatus
FROM
(
SELECT ID, StartDate, DATEADD(d,30,StartDate) AS DueDate
FROM TableA
) AS drv
...or a CTE:
;WITH DueDateCTE
AS
(
SELECT ID, StartDate, DATEADD(d,30,StartDate) AS DueDate
FROM TableA
)
SELECT ID, StartDate, DueDate, CASE WHEN GETDATE() > DUEDATE THEN 'OVERDUE' ELSE 'ON TIME' END AS OverDueStatus
FROM DueDateCTE
As far as performance goes, with doing the calculations in a view versus a computed column...I'm not sure, and suspect the answer is "it depends." I haven't tested, but all other things being equal, I would expect SELECT performance to be the same between both approaches (but always test!). Now, if you mark your computed column as PERSISTED, you should get better SELECT performance because the value will be calculated and stored physically when the row is UPDATEd or INSERTed. Of course, this means you are shifting the performance hit to your UPDATEs and INSERTs, which may be bad for your scenario. Or perfectly acceptable. Again, you'll need to do some testing for your specific situation. 🙂
December 30, 2011 at 2:41 am
Just to add what Jon said, persisted computed columns actually consume disk space. If your query has search predicates on computed columns then it would be a good idea (with index on them), else no. Also, it requires Table Definition changes as well as View Definition changes (ultimately you will pull that column in view).
The computation is simple. If your base table doesn’t have huge data & if you are not using computed columns as search predicates, I don’t see any harm in computing them directly in views.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply