Hi,
I want to get last working day data from database(SQL database).
For example, if today is Tuesday, I want to get Monday data.
I can get the data directly using
strtTime = Date.Now.AddDays(-1).ToString("yyyy/MM/dd") & " " & "06:15:00"
But the problem is if today day is Monday.
Since the last working day is not fixed, for example for week32, if today is monday, the last working day before monday is last saturday. but for week33, if today is monday, the last working day is last Friday. Sometimes the last working day can be last Thursday or Wednesday also.
My idea is I want to loop the Date.Now.AddDays(-i) to check whether data for last working day in database is available or not. If available, i can proceed to next step but if the data is not available, it will loop until it found a data. But after a lot of trying, I cant get it right.
I really hope someone can help me.. tanx a lot (^_^)
Here is my code to get the data:
Public Function SumYC1() As String
Dim DBConn As New SqlConnection("server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx")
Dim DBCmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet()
Dim strtTime As String
Dim endTime As String
Dim fstrtTime As String
Dim fendTime As String
Dim res1 As String
strtTime = Date.Now.AddDays(-1).ToString("yyyy/MM/dd") & " " & "06:15:00"
endTime = Date.Now.AddDays(-1).ToString("yyyy/MM/dd") & " " & "18:15:00"
DBConn.Open()
Dim SQLstr As String = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
SQLstr &= " FROM VAC_JSINFO "
If DateTime.Now.DayOfWeek.ToString() = "Monday" Then
For i = 1 To 10
fstrtTime = Date.Now.AddDays(-i).ToString("yyyy/MM/dd") & " " & "06:15:00"
fendTime = Date.Now.AddDays(-i).ToString("yyyy/MM/dd") & " " & "18:15:00"
SQLstr &= " WHERE (COLLECTDATE > '" & fstrtTime & "') AND (COLLECTDATE < '" & fendTime & "') AND (KYUERR1 >= 0) "
SQLstr &= " ORDER BY RM DESC "
Dim myCommand As New SqlCommand(SQLstr, DBConn)
res1 = myCommand.ExecuteScalar().ToString()
If res1 = Nothing Then
Next
Else
Return res1
End If
Else
SQLstr &= " WHERE (COLLECTDATE > '" & strtTime & "') AND (COLLECTDATE < '" & endTime & "') AND (KYUERR1 >= 0) "
SQLstr &= " ORDER BY RM DESC "
Dim myCommand As New SqlCommand(SQLstr, DBConn)
res1 = myCommand.ExecuteScalar().ToString()
Return res1
End If
End Function