May 26, 2009 at 9:14 am
I am trying to use the UPDATE statement to determine the earliest date on the DATEIN field and the lastest date on the DATEOUT field. I am not sure it can be done in an update statement but I was trying to use the example below.
The results I am trying to get:
DATEIN DATEOUT FIELDIDFIELD
2009-05-03 10:36:32.0002009-05-07 11:36:33.000ABCD1D
2009-05-02 09:34:31.0002009-05-09 08:36:35.000ABCD2F
CREATE TABLE #TEMP (DATEIN datetime, DATEOUT datetime, FIELDID varchar(200), FIELD varchar(100))
INSERT INTO #TEMP (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/03/09 10:39:32', '05/07/09 11:34:32', 'ABCD1', 'A')
INSERT INTO #TEMP (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/02/09 9:34:31', '05/09/09 8:34:31', 'ABCD2', 'B')
SELECT * FROM #TEMP
CREATE TABLE #TEMP1 (DATEIN datetime, DATEOUT datetime, FIELDID varchar(200), FIELD varchar(100))
INSERT INTO #TEMP1 (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/03/09 10:38:33', '05/07/09 11:36:33', 'ABCD1', 'D')
INSERT INTO #TEMP1 (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/03/09 10:36:32', '05/07/09 11:32:32', 'ABCD1', 'C')
INSERT INTO #TEMP1 (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/02/09 9:39:34', '05/09/09 8:32:34', 'ABCD2', 'E')
INSERT INTO #TEMP1 (DATEIN, DATEOUT, FIELDID, FIELD)
VALUES ('05/02/09 9:36:35', '05/09/09 8:36:35', 'ABCD2', 'F')
UPDATE A WITH (ROWLOCK) SET A.FIELDID = B.FIELDID,
A.DATEIN = CASE WHEN A.DATEIN < B.DATEIN THEN A.DATEIN ELSE B.DATEIN
END,
A.DATEOUT = CASE WHEN A.DATEOUT > B.DATEOUT THEN B.DATEOUT ELSE A.DATEOUT
END,
A.FIELD = B.FIELD
FROM dbo.#TEMP AS A INNER JOIN
(SELECT DATEIN ,DATEOUT, FIELDID, FIELD
FROM #TEMP1) AS B
ON A.FIELDID = B.FIELDID
SELECT * FROM #TEMP
SELECT * FROM #TEMP1
DROP TABLE #TEMP
DROP TABLE #TEMP1
May 26, 2009 at 12:23 pm
Hi Tim
First: Thanks for the really helpful sample data and the desired result! 🙂
I would use a CTE which returns your MIN/MAX dates. I'm just not sure about the logic how to update the FIELD:
; WITH
min_max (FieldId, Field, MinIn, MaxOut) AS
(
SELECT
FIELDID,
FIELD,
MIN(DATEIN),
MAX(DATEOUT)
FROM #TEMP1
GROUP BY
FIELDID,
FIELD
)
UPDATE A SET
A.DATEIN = mm.MinIn,
A.DATEOUT = mm.MaxOut
FROM #Temp A
JOIN min_max mm ON A.FIELDID = mm.FieldId --AND A.FIELD = mm.Field
SELECT * FROM #TEMP
Flo
May 26, 2009 at 1:13 pm
1. How do you determine the value with which to update FIELD in your #TEMP? Is it supposed to be from the #TEMP1 row that has the min(DATEIN) or the row that has the max(DATEOUT)? Or what?
2. Is the goal to update #TEMP only when a min(#TEMP1.DATEIN) is before the existing #TEMP.DATEIN for its FIELDID, or a max(#TEMP1.DATEOUT) is after the existing #TEMP.DATEOUT for its FIELDID? That is, should values already in #TEMP be considered in finding the min() and max(), or would you want to ignore a possibly earlier value in #TEMP and simply update the row with the min() and max() found in #TEMP1?
May 26, 2009 at 1:32 pm
Flo,
This does not give me what I want. I need to use the values in in the #TEMP also to be used to determine the earliest DATEIN and latest DATEOUT.
Thanks Tim
May 26, 2009 at 1:33 pm
1. I would use the max(DATEOUT) to determine to update FIELD as I want the the value from the oldest record.
2. Yes the values already in the #TEMP should be used to determined.
May 26, 2009 at 1:37 pm
You can either use a CASE statement or a second CTE which combines the #TEMP1 with #TEMP and call the "min_max" on this.
Here the CASE approach:
; WITH
min_max (FieldId, Field, MinIn, MaxOut) AS
(
SELECT
FIELDID,
FIELD,
MIN(DATEIN),
MAX(DATEOUT)
FROM #TEMP1
GROUP BY
FIELDID,
FIELD
)
UPDATE A SET
A.DATEIN = CASE WHEN mm.MinIn A.DATEOUT THEN mm.MaxOut ELSE A.DATEOUT END
FROM #Temp A
JOIN min_max mm ON A.FIELDID = mm.FieldId --AND A.FIELD = mm.Field
SELECT * FROM #TEMP
Flo
May 26, 2009 at 2:09 pm
Tim
I didn't recognize your last post. If you need the FIELD of the row with the MAX(DATEOUT) the solution is a bit different. I would use a CROSS APPLY in combination with a TOP(1):
UPDATE A SET
A.DATEIN = CASE WHEN min_field.DATEIN A.DATEOUT THEN max_field.DATEOUT ELSE A.DATEOUT END,
A.FIELD = CASE WHEN max_field.DATEOUT > A.DATEOUT THEN max_field.FIELD ELSE A.FIELD END
FROM #Temp A
CROSS APPLY
(
SELECT TOP(1)
DATEIN
FROM #TEMP1
WHERE FIELDID = A.FIELDID
ORDER BY DATEIN
) min_field
CROSS APPLY
(
SELECT TOP(1)
DATEOUT,
FIELD
FROM #TEMP1
WHERE FIELDID = A.FIELDID
ORDER BY DATEOUT DESC
) max_field
Flo
May 26, 2009 at 2:19 pm
Flo,
This is what I need.
Thank You,
Tim
May 26, 2009 at 5:11 pm
Flo already gave a great solution with "cross apply", so I won't bother messing about with a CTE that searches out the max() and min() from a UNION of #temp and #temp1. (sounds complicated just saying that much!!!). I am curious, though, how the max(dateout) equates to "the oldest" record.
1. I would use the max(DATEOUT) to determine to update FIELD as I want the the value from the oldest record.
That seems to find the newest. The oldest record overall would be found with a third cross apply. The FIELD value for the older of the two result time-values would seem to be the one in the min(DATEIN) row.
May 26, 2009 at 6:22 pm
john.arnott (5/26/2009)
Flo already gave a great solution with "cross apply", so I won't bother messing about with a CTE that searches out the max() and min() from a UNION of #temp and #temp1. (sounds complicated just saying that much!!!). I am curious, though, how the max(dateout) equates to "the oldest" record.1. I would use the max(DATEOUT) to determine to update FIELD as I want the the value from the oldest record.
That seems to find the newest. The oldest record overall would be found with a third cross apply. The FIELD value for the older of the two result time-values would seem to be the one in the min(DATEIN) row.
Hi John
Providing another solution would never bother in my opinion :-). The best solution of CROSS APPLY and CTEs in this context always depends on many criteria.
I thing (thought) the "oldest" was a typo.
Greets
Flo
May 27, 2009 at 7:32 am
John,
You are right I did mean to say I wanted the most recent record and not the oldest record with the DATEOUT field.
Tim
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply