Comparing date fields in a single row
-
I have a table where each row contains five date fields. I need to update one of those date fields with the most recent of the other four date values. In addition, I need to update another field with a value describing which date field I used. If the date currently in the field being updated is more recent than any of the other dates, I need to leave it as is.
How can I write a T-SQL statement that will compare the four dates and tell me which date it used, and its value?
Thanks,
Mattie
-
Here is an easy solution. You may want to think through this and develop a UDF if you end up doing this quit a bit or more fields become involved.
CREATE TABLE #DATES( IdentityField integer,
ChangeStatus char(5), -- D1, D2, D3, D4
Date1 datetime,
Date2 datetime,
Date3 datetime,
Date4 datetime)
INSERT INTO #DATES
VALUES( 1, NULL, '01/01/2005', '01/02/2005', '01/03/2005', '01/04/2005')
INSERT INTO #DATES
VALUES( 2, NULL, '01/10/2005', '01/02/2005', '01/03/2005', '01/04/2005')
INSERT INTO #DATES
VALUES( 3, NULL, '01/01/2005', '01/12/2005', '01/03/2005', '01/04/2005')
INSERT INTO #DATES
VALUES( 4, NULL, '01/01/2005', '01/02/2005', '01/13/2005', '01/04/2005')
INSERT INTO #DATES
VALUES( 5, NULL, '01/01/2005', '01/01/2005', '01/01/2005', '01/01/2005')
SELECT IdentityField, ChangeStatus,
CONVERT( varchar, Date1, 101) AS Date1,
CONVERT( varchar, Date2, 101) AS Date2,
CONVERT( varchar, Date3, 101) AS Date3,
CONVERT( varchar, Date4, 101) AS Date4
FROM #DATES
UPDATE #DATES SET
Date1 = CASE
WHEN Date2 > Date1 AND Date2 > Date3 AND Date2 > Date4
THEN Date2
WHEN Date3 > Date1 AND Date3 > Date2 AND Date3 > Date4
THEN Date3
WHEN Date4 > Date1 AND Date4 > Date2 AND Date4 > Date3
THEN Date4
ELSE Date1
END,
ChangeStatus = CASE
WHEN Date2 > Date1 AND Date2 > Date3 AND Date2 > Date4
THEN 'D2'
WHEN Date3 > Date1 AND Date3 > Date2 AND Date3 > Date4
THEN 'D3'
WHEN Date4 > Date1 AND Date4 > Date2 AND Date4 > Date3
THEN 'D4'
ELSE 'D1'
END
SELECT IdentityField, ChangeStatus,
CONVERT( varchar, Date1, 101) AS Date1,
CONVERT( varchar, Date2, 101) AS Date2,
CONVERT( varchar, Date3, 101) AS Date3,
CONVERT( varchar, Date4, 101) AS Date4
FROM #DATES
DROP TABLE #DATES
I wasn't born stupid - I had to study.
-
Farrell,
Thank you so much, although 'easy' is not exactly how I'd characterize the solution. My first thought was a UDF, but I'm trying to break out of the habit of what are apparently cursor-based solutions. The only change I had to make to the logic, in case anyone else wants to use it, was to put an IsNull around the date tests. Otherwise, because at least one of the dates was null, it fell through to the 'else' clause.
You saved me tons of time. Thanks again.
Mattie
-
I'm bad about remembering error coding when I try and answer a question quickly.
Glad it helped.
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply