July 8, 2013 at 2:20 pm
I don't think so simply because the data doesn't exist but I thought I would check.
Here is a sample of what I'm trying to do:
CREATE TABLE [dbo].[x]
(
[PNmCd][varchar](50) NOT NULL,
[StrtLY] [date] NULL,
[StrtTY] [date] NULL
)
ALTER TABLE x
ADD DysBf as (datediff(dd, [StrtTy], getdate()))
ALTER TABLE x
ADD CalcDt AS (dateadd(dd,-[DysBf], [StrtLY]))
I was allowed to create the first computed column ([DysBf]) but I received this when trying to create the next one:
Computed column 'DysBf' in table 'x' is not allowed to be used in another computed-column definition.
Basically, I would like to put a date in StrtLY and StrtTY. On that same row, I need the column DysBf to subtract the number of days before StrtTY. Then, I need to add the result of that calculation to StrtLY to populate the column CalcDt.
So a sample output would look like this (for today, which is 7/8/2013):
PNmCd StrtLY StrtTY DysBf CalcDt
OR 2012-07-10 2013-07-12 4 2013-07-06
I guess the question is if my method isn't possible, what is the best way to accomplish this?
July 8, 2013 at 2:36 pm
The error message says it all. 🙂
However, there is no reason you can't use the same calculation again.
ALTER TABLE x
ADD CalcDt AS (dateadd(dd,-datediff(dd, [StrtTy], getdate()), [StrtLY]))
However, your original for DysBf does not return 4 as you said you need. It returns -4.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2013 at 2:41 pm
That's exactly what I needed. And good catch on the -4. I modified that section. Thanks alot!
July 8, 2013 at 2:54 pm
DataAnalyst011 (7/8/2013)
That's exactly what I needed. And good catch on the -4. I modified that section. Thanks alot!
You are welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply