Loop to Get Last Working Day Data

Question

lulu79 0 Junior Poster in Training

12 Years Ago

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

programming-construct vb.net

2 Contributors
9 Replies
217 Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by lulu79

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

Does it really matter whether or not today is Monday? Just do the loop as

set start date to today

do
    subtract one from start day
    get the records for start day
loop while record count = 0

If today is (for example) Thursday then it will kick out after one iteration (Wednesday). If it is Monday then it will kick out when it finds a day with records which should be the previous working day.

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

Is there still a question pending?

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

Look at my code

SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
         "   FROM VAC_JSINFO " &
         "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
         "    AND (COLLECTDATE < '" & fendTime & "') " &
         "    AND (KYUERR1 >= 0) " &
         "  ORDER BY RM DESC"

and look at your code

Dim SQLstr As String = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
SQLstr &= " FROM VAC_JSINFO "
SQLstr &= " WHERE (COLLECTDATE > '" & fstrtTime & "') AND (COLLECTDATE < '" & fendTime & "') AND (KYUERR1 >= 0) "
SQLstr &= " ORDER BY RM DESC "

see the difference? Try this

Public Function SumYC1() As String

    Dim SQLstr As String
    Dim fstrtTime As String
    Dim fendTime As String
    Dim res1 As String

    Dim DBConn As New SqlConnection("server=xxxx;uid=xxx;pwd=xxx;database=xxx")
    DBConn.Open()    

    Dim DBCmd As New SqlCommand
    DBCmd.Connection = DBConn

    Dim i As Integer = 0

    Do
        i -= 1
        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 = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
                 "   FROM VAC_JSINFO " &
                 "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
                 "    AND (COLLECTDATE < '" & fendTime & "') " &
                 "    AND (KYUERR1 >= 0) " &
                 "  ORDER BY RM DESC"

        DBCmd.CommandText = SQLstr
        res1 = DBCmd.ExecuteScalar().ToString()

    Loop While res1 = ""

    Return res1

End Function

I removed the declaration of things you don't use in this Sub (like da and ds). By the way, depending on your SQL implementation you may be able to simplify the query by using BETWEEN as

SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
         "   FROM VAC_JSINFO " &
         "  WHERE COLLECTDATE BETWEEN '" & fstrtTime & "' AND '" & fendTime & "'" &
         "    AND (KYUERR1 >= 0) " &
         "  ORDER BY RM DESC"

Edited 12 Years Ago by Reverend Jim

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

lulu79 0 Junior Poster in Training · Answer 1 · 2012-10-30T00:13:35+00:00

Yeah you're right! I forgot about the fact that Monday is not always our 1st day of working day.. Thank you (^_^)

lulu79 0 Junior Poster in Training · Answer 2 · 2012-10-29T23:56:58+00:00

I tried the above solution. it works if the working day is yesterday but it is not work if the last working day is last 2 or 3 days.

When I debug it, it said that it has error at WHERE statement and when I debug the query statement after second loop, the query become like this:

SELECT     SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM
FROM         VAC_JSINFO
WHERE     (COLLECTDATE > '2012/10/28 06:15:00') AND (COLLECTDATE < '2012/10/28 18:15:00') AND (KYUERR1 >= 0)
ORDER BY RM DESC
WHERE     (COLLECTDATE > '2012/10/27 06:15:00') AND (COLLECTDATE < '2012/10/27 18:15:00') AND (KYUERR1 >= 0)
ORDER BY RM DESC

How can I loop it to make it only 1 WHERE statement like this although it is at second or third time loop:

 SELECT     SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM
    FROM         VAC_JSINFO
    WHERE     (COLLECTDATE > '2012/10/27 06:15:00') AND (COLLECTDATE < '2012/10/27 18:15:00') AND (KYUERR1 >= 0)
    ORDER BY RM DESC

Here is my loop:

 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 fstrtTime As String
        Dim fendTime As String
        Dim res1 As String

        DBConn.Open()

        Dim SQLstr As String = " SELECT  SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
        SQLstr &= " FROM         VAC_JSINFO "


        Dim i As Integer
        i = 0

        Do
            i = i + 1
            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()

        Loop While res1 = ""

        Return res1


    End Function

lulu79 0 Junior Poster in Training · Answer 3 · 2012-10-30T03:25:07+00:00

lulu79 0 Junior Poster in Training

12 Years Ago

yes. It is still pending..

score 0 · Answer 4 · 2012-10-30T07:30:00+00:00

Build the entire query inside the loop as

SQLstr = " SELECT SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM " &
         "   FROM VAC_JSINFO " &
         "  WHERE (COLLECTDATE > '" & fstrtTime & "') " &
         "    AND (COLLECTDATE < '" & fendTime & "') " &
         "    AND (KYUERR1 >= 0) " &
         "  ORDER BY RM DESC"

lulu79 0 Junior Poster in Training · Answer 5 · 2012-10-30T08:04:14+00:00

I still got the same problem. Have I miss something inside my code?

Public Function SumYC1() As String

        Dim DBConn As New SqlConnection("server=xxxx;uid=xxx;pwd=xxx;database=xxx")
        Dim DBCmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet()
        Dim fstrtTime As String
        Dim fendTime As String
        Dim res1 As String

        DBConn.Open()

        Dim i As Integer
        i = 0

        Do
            i = i + 1
            fstrtTime = Date.Now.AddDays(-i).ToString("yyyy/MM/dd") & " " & "06:15:00"
            fendTime = Date.Now.AddDays(-i).ToString("yyyy/MM/dd") & " " & "18:15:00"

            Dim SQLstr As String = " SELECT  SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
            SQLstr &= " FROM         VAC_JSINFO "
            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()

        Loop While res1 = ""

        Return res1


    End Function

lulu79 0 Junior Poster in Training · Answer 6 · 2012-10-31T07:18:33+00:00

Hi Reverend Jim,

Finally it works! First i change my code like what you suggest but it become like this:

Then I change back the query and got the result just like what I want. Here is the code:

    Public Function SumYC1() As String

        Dim SQLstr As String
        Dim fstrtTime As String
        Dim fendTime As String
        Dim res1 As String
        Dim DBConn As New SqlConnection("server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx")

        DBConn.Open()
        Dim DBCmd As New SqlCommand
        DBCmd.Connection = DBConn
        Dim i As Integer = 0
        Do

            i -= 1
            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 = " SELECT  SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
            SQLstr &= " FROM         VAC_JSINFO "
            SQLstr &= " WHERE       (COLLECTDATE > '" & fstrtTime & "') AND (COLLECTDATE < '" & fendTime & "') AND (KYUERR1 >= 0) "
            SQLstr &= " ORDER BY RM DESC "

            DBCmd.CommandText = SQLstr
            res1 = DBCmd.ExecuteScalar().ToString()

        Loop While res1 = ""
        Return res1

    End Function

Thanks a lot Reverend Jim (^__^)