Sqlserver: How to detect consecutive days in a table

Asked

Viewed 91 times

0

I need to insert into a time table only the rows of another table in which the Membershipdate column has 1 day difference dates. For example, in the table image below, only lines 1, 2 and 3 would go to the new table.

inserir a descrição da imagem aqui

I wrote the code below, however I have as return 0 Row(s) affected, IE, the code did not detect any consecutive day.

 BEGIN TRY DROP TABLE #Temporaire2 END TRY BEGIN CATCH END CATCH

 select a.* 
 into
 #Temporaire2  
  from (Select * from #Temporaire ) a
  inner join (Select * from #Temporaire) b
        on a.MembershipID = b.MembershipID
           and DATEDIFF(day, a.MembershipDate, b.MembershipDate) = 1
           order by a.MembershipID, a.MembershipDate

Where my code is wrong?

sql sql-server

  • You want to buy the lines from the table itself? if it’s no use using Datediff, as it will compare the dates of a row in the two tables (2003-06-22 with 1998-06-01)

  • Thank you @Ricardopunctual. And I managed to find the solution.

1 answer

0

I finally managed to solve it the following way:

select * from (Select * from tabMemberships m ) a
              inner join (Select * from tabMembershipS m1) b
              on  a.PersonID = b.PersonID
              and ((a.MembershipDate = DATEADD(day, 1, b.membershipDate) ) 
              OR  
              (a.MembershipDate = DATEADD(day, -1, b.membershipDate) ))

I made a Tablea Internet with herself to make the comparison with all dates that differ from the total of 1 day.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.