Daniel Vaskovic Daniel Vaskovic - 2 months ago 6
SQL Question

Update ID column depending on dates

I have a table where i need to update the ID field depending on a date column.

Table A

H_MEP_ID|_LoadDate |_EndDate |ID
12312 |2015-04-19 12:14:52.733 |2016-06-16 17:19:56.613 |72
12312 |2016-06-16 17:19:53.403 |NULL |83


Table B

H_MEP_ID|MeasureDate|MeasureDateTime |ID
12312 |2015-10-02 |2015-10-02 23:00 |NULL
12312 |2015-11-01 |2015-11-01 23:00 |NULL
12312 |2015-11-02 |2015-11-02 23:00 |NULL
12312 |2015-11-03 |2015-16-16 23:00 |NULL


How Table B should look like after the update script.

H_MEP_ID|MeasureDate|MeasureDateTime |ID
12312 |2015-10-02 |2015-10-02 23:00 |72
12312 |2015-11-01 |2015-11-01 23:00 |72
12312 |2015-11-02 |2015-11-02 23:00 |72
12312 |2015-11-03 |2015-16-16 23:00 |83


Do someone have any advice how to proceed?

Answer
   update b
    set 
    b.id=a.id
    from
    tablea a
    join
    tableb b
    on
    b.H_MEP_ID=a.H_MEP_ID
    and b.meaure_datetime between a.loaddate and isnull(a.enddate,getdate())
Comments