CODEWITHSUNDEEP
sqlsql-servert-sql
pauloutboy
pauloutboy

Reputation: 51

Updating records depending on the intersection of dates

I have a table that looks like this:

ID  |    Start   |    End     | Flag1 | Flag2
--------------------------------------------    
1   | 1900-01-01 | 1900-01-01 |   0   |   1
1   | 1900-01-01 | 2000-01-01 |   1   |   0
2   | 2010-01-01 | 2020-01-01 |   0   |   0

For any date range of an ID, flags 1 and 2 could be set to 1 or 0. For the first record, he's only flag2. For the second record, he's only flag1.

Is there a way to update the records in a way where the flags are both 1 in the intersection of the start and end dates? Essentially:

ID  |    Start   |    End     | Flag1 | Flag2
--------------------------------------------    
1   | 1900-01-01 | 1900-01-01 |   1   |   1
1   | 1900-01-02 | 2000-01-01 |   1   |   0
2   | 2010-01-01 | 2020-01-01 |   0   |   0

I have tried using a group by but it doesn't return the result set as expected. Any help would be appreciated. Thank you very much!

Upvotes: 1

Views: 51

Answers (2)

Shruti
Shruti

Reputation: 190

Why not this?

UPDATE [mytable] SET [flag1] = 1, [flag2] = 1 WHERE [startdate] = [enddate]

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

You need to use self join to achieve the desired result.

UPDATE t1
SET t1.Flag1 = 1, t1.Flag2 = 1
FROM
mytable t1 join mytable t2 on t1.ID = t2.ID
WHERE t1.Start_Date = t2.End_Date

Upvotes: 1

Related Questions