CODEWITHSUNDEEP
sqlsql-server-2005sql-update
JoSav
JoSav

Reputation: 247

Get rows with consecutive Date(Day)

I'm searching a way to update all rows which have an other occurence the day before.

Example:

ID     Config Number     Date          Status
1      238               10/9/2013     1
2      351               1/9/2013      2
3      351               2/9/2013      0
4      238               11/9/2013     0
5      124               18/9/2013     3
6      238               20/9/2013     0
7      238               12/9/2013     0
8      124               20/9/2013     0
9      238               13/9/2013     0

And after the update i would like to have something like:

ID     Config Number     Date          Status
1      238               10/9/2013     1
2      351               1/9/2013      2
3      351               2/9/2013      2
4      238               11/9/2013     1
5      124               18/9/2013     3
6      238               20/9/2013     0
7      238               12/9/2013     1 
8      124               20/9/2013     0
9      238               13/9/2013     1

What i have for now is:

Update sc
set sc.Status = (Select Status From sc1 inner Join sc On sc.Id = sc1.ID)
From sc

And I know it didn't work, but I can't figure out how should my query should look like.

Upvotes: 1

Views: 96

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

We can make use of a "self join" by joining the table to itself, but with the stipulation of consecutive days. Turning this into an update is as follows:

update a
set a.[Status] = b.[Status]
from sc as a
    join sc as b on a.[Config Number] = b.[ConfigNumber]
        and a.[Date] = dateadd(dd, 1, b.[Date])

This will update the consecutive record that matches on Config Number. I'm assuming that your "after update" demo data has an errant duplicate record at the end.

Also, as general points:

  • Try to use ISO 8601 date formatting to eliminate ambiguity in your dates (e.g. "2013-09-20").
  • Don't use spaces in your column names.
  • Try to avoid keywords as your column names.

Upvotes: 1

roman
roman

Reputation: 117380

update T1 set
    Status = T2.[Status]
from sc as T1
    inner join sc as T2 on T2.[Date] = dateadd(dd, -1, T1.Date)

sql fiddle demo

of, if you have to search prev date just inside Config Number

update T1 set
    Status = T2.[Status]
from sc as T1
    inner join sc as T2 on T2.[Date] = dateadd(dd, -1, T1.Date) and T2.[Config Number] = T1.[Config Number]

Upvotes: 1

Related Questions