CODEWITHSUNDEEP
sqlsql-server
Starjammer
Starjammer

Reputation: 81

Updating a table based on date ranges of another

I have 2 table that look like below.

TABLE_A

START_DT     END_DT     VALUE

4/1/1993       4/29/1993       48
4/29/1993      5/12/1993      48
5/13/1993      5/27/1993      44
5/27/1993      8/18/1993      44

TABLE_B

START_DT END_DT VALUE

4/1/1993      5/12/1993      NULL
5/13/1993      8/18/1993      NULL

As you can see, the TABLE A's first 2 rows date ranges fall in TABLE B's first row date range. TABLE A's rows 3 and 4 date ranges fall in TABLE B's row 2.

Based on date ranges, I want to take A.VALUE and update B.VALUE such that TABLE B will appear as:

TABLE_B

START_DT END_DT VALUE

4/1/1993      5/12/1993      48

5/13/1993      8/18/1993      44

I started to try and do this by using a cursor, and going through table A and trying to see if A.START_DT and A.END_DT fall within a range of table B, and update accordingly.

Basically,

DECLARE @StartDate datetime, @EndDate datetime, @Value smallint; DECLARE MyCursor cursor for select * from TABLE_A

OPEN MyCursor FETCH NEXT FROM MyCursor INTO @StartDate,@EndDate, @Value //Then I want to do something with:

//  SELECT START_DT, END_DT
//      FROM TABLE_B
//      WHERE START_DT <= A.@EndDate
//      AND END_DT>= A.@StartDate
//
//

//From here, I'm getting confused on how to best update the VALUE column in TABLE B.

UPDATE TABLE B
    SET VALUE = @Value

WHERE B.START_DT,B.END_DT in (
    SELECT start_dt,end_dt
        FROM TABLE_B
        WHERE start_dt <= @EndDate
        AND end_dt >= @StartDate)

The above is NOT correct syntax but you get the point. Does anyone have a quick and dirty way to do/fix this? I'm having a brain freeze on how to write the sql to pick the row I want to work with based on the date range, and then update the single VALUE column.

Surely there's an easier way to do this without involving cursors?

Thanks much in advance.

Upvotes: 2

Views: 5261

Answers (1)

rs.
rs.

Reputation: 27467

Try following code to update value, You can avoid cursor totally and use this 

UPDATE B
SET B.VALUE = V.VALUE
FROM TABLE_B B 
CROSS APPLY (SELECT DISTINCT VALUE FROM TABLE_A A 
              WHERE B.start_dt <= A.End_dt AND B.end_dt >= A.Star_Dt) V

Upvotes: 2

Related Questions