How to check the dates are missing in squence

Question

Hi, I have table AAA, Td_No, Td_Date Td_No Td_Date 1 01/01/02018 2 02/01/2018 3 03/01/2018 4 05/01/2018 what i want is if the dates are contine more than two days then pass some flag. con.Open() cmd6 = New SqlClient.SqlCommand("Select * from AAA order by Td_Date", con) dr6 = cmd6.ExecuteReader() If dr6.HasRows Then While dr6.Read ''here need to check and some flag End While End If thanks Basit

Suraj Kumar · Answer

You need to declare two variables start date and end date. You can also say these dates as minimum and maximum date. DIM startDate as DateTime DIM endDate as DateTime To get the start date write your query as shown below. cmd6 = New SqlClient.SqlCommand( "Select top 1 Td_Date from AAA order by Td_Date" , con) To get the end date write your query as shown below. cmd6 = New SqlClient.SqlCommand( "Select top 1 Td_Date from AAA order by Td_Date desc" , con) Now create an another function in code and pass each date from start to end date. You need to check number of record in table of that date. If record exist then you need to write it exists otherwise that date is missing. To run the loop through each date write your code as follows. Private Sub LoopByEachDate() Dim date As DateTime = startDate Dim isRecordExist As Boolean While date 'You need to pass the date to function here, for example isRecordExist = CheckDateInTable(date) 'In the function CheckDateInTable you have to write query as select * from your table where Td_Date = date. Then check number of record if the number of record is greater than or equal to one then record exists otherwise not. 'Now check that boolean value If (isRecordExist) Then 'Your logic here End If date += TimeSpan.FromDays(1) End While End Sub Not this has been indicated as steps. You need to modify this as per your need. Hope it will help you.