Compute date column if other date columns not null ?
-
Hi, I am a part time amateur programmer, and really need some help. I have 5 date columns: DateComplete, Date1, Date2, Date3, Date4. In SSMS, is there a way to make a computed column defined like this: If Date1 and Date2 and Date3 and Date4 are Not Null, then DateComplete = Date.Today
Then the DateComplete column must not be recomputed every time. Once the date is inserted, it must remain intact.
Is that even possible?
Thanks in advance. I am looking for a way to define that column in ssms column definition. I know WHERE to put it, but not WHAT to put in there. Thanks again. -
CREATE TABLE #t
(
Id int IDENTITY NOT NULL
, date1 date
, date2 date
, date3 date
, date4 date
, date5 AS
CASE
WHEN date1 IS NOT NULL
AND date2 IS NOT NULL
AND date3 IS NOT NULL
AND date4 IS NOT NULL
THEN Cast(GetDate() AS date) ELSE Cast(NULL AS date) END );
INSERT #t (
date1
, date2
, date3
, date4
)
VALUES
(
GetDate()
, GetDate()
, GetDate()
, GetDate()
),
(
GetDate()
, NULL
, GetDate()
, GetDate()
);
SELECT * FROM #t t;
DROP TABLE #t;
-
Thank you for the reply, I really appreciate your time.
I apologize if I wasn't clear enough. Is there a way to put that formula in here so that it runs automatically? or am I on the wrong track altogether - Joe Torre - Thursday, April 12, 2018 3:42 PM
The problem with that is that the date in the calculated column will change over time, which is very visible if you change the calculation to this and do some back to back selects.
date5 AS
CASE
WHEN date1 IS NOT NULL
AND date2 IS NOT NULL
AND date3 IS NOT NULL
AND date4 IS NOT NULL
THEN GetDate() ELSE NULL END;You could do it with a trigger something like,
CREATE TABLE TESTY_DATES
(
Id int IDENTITY NOT NULL
, date1 date
, date2 date
, date3 date
, date4 date
, date5 datetime
)GO
CREATE TRIGGER TESTY_DATE_TRIGGER ON TESTY_DATES
AFTER INSERT, UPDATE
AS BEGINUPDATE TESTY_DATES SET date5 = getdate() WHERE TESTY_DATES.Id IN(SELECT Id FROM inserted WHERE date1 IS NOT NULL AND date2 IS NOT NULL AND date3 IS NOT NULL AND date4 IS NOT NULL AND date5 IS NULL)
END
INSERT INTO TESTY_DATES ( date1, date2, date3, date4)
VALUES
(
GetDate()
, GetDate()
, GetDate()
, GetDate()
),
(
GetDate()
, NULL
, GetDate()
, GetDate()
);
SELECT * FROM TESTY_DATES t;UPDATE TESTY_DATES SET date2 = getdate() WHERE Id = 2
UPDATE TESTY_DATES SET date2 = NULL WHERE Id = 1SELECT * FROM TESTY_DATES t;
DROP TABLE TESTY_DATES;
-
@Ld,
If you create the computed column in SQL or in the GUI the result is the same. To persist the computed column in SQL:CREATE TABLE #t
(
Id int IDENTITY NOT NULL
, date1 date
, date2 date
, date3 date
, date4 date
, date5 AS
CASE
WHEN date1 IS NOT NULL
AND date2 IS NOT NULL
AND date3 IS NOT NULL
AND date4 IS NOT NULL
THEN Cast(GetDate() AS date) ELSE Cast(NULL AS date) END PERSISTED );
The only reason to persist a computed column would be to index it. If you plan to do that, any procedures using it will need the same SET options as an indexed view.SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF - Joe Torre - Thursday, April 12, 2018 6:13 PM
That table won't even create, you'll get a nice error, Computed column 'date5' in table '#t' cannot be persisted because the column is non-deterministic.
And as I noted earlier if you create it without persisted the original value will not be preserved.
- Ld - Thursday, April 12, 2018 3:29 PM
As some other posters responding have indicated, there are two ways to handle this scenario, but only the trigger will comply with "must not be recomputed every time". It was coded to check for a NULL value in date5, whereas the computed column simply can't do that. However, I would be somewhat concerned about a data scenario with that kind of compliance. If a date column is being set back to NULL after having a value, do you really want to say that your datecomplete column is still the original completion date? I'm just thinking about the consistency of your data. This scenarios suggests that maybe any old value for the datecompleted column should be tracked into a history table. What say you, @Ld ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
HI Steve / Everyone that posted
First, I really appreciate everyone's time spent on my question. I thank you for the input, and suggestions. This is my first post on here, even though I read it a lot. I have gained enormous knowledge and practical application tips from this forum.
After spending a lot of time thinking about this overnight, I came to basically the same conclusion as Steve, in that I would be concerned about the integrity of the DateComplete column, in the event one of the other dates changes. For this instance, I think that creating a class in my code and handling that way will be the best solution, rather than in the db itself.
Again, thank you all for your time, I will do this in code behind and be done.
I hope everyone has a great weekend ! Thanks again!
at least I came to a conclusion ... 🙂 , which was the whole point ! - Ld - Friday, April 13, 2018 11:55 AM
Glad you came to a conclusion. Given the options, I would have chosen the trigger because it encapsulates the logic in the database, and makes it easy to ensure that the data remains consistent with the other columns. If I had to keep track of the changes, I could create a history table and let the trigger update that as well. I wouldn't choose to handle it in the application because that's probably a lot more effort in testing, and it doesn't handle someone going into the database and manually updating one of those columns, leaving a bit of a hole where protecting the consistency of your data is concerned.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I would split the dates out into a different related table with a date type column to identify what the is, like completed and a bit column indicating if it's the last date of that type. That way you keep all the history.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply