Use UPDATE to determine the earliest date in a field and old date in another field

  • 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

  • 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

  • 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?

  • 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

  • 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.

  • 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

  • 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

  • Flo,

    This is what I need.

    Thank You,

    Tim

  • 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.

  • 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

  • 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