How to check the dates are missing in squence

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

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 <= endDate
'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.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
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 <= endDate
        '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.
0