How to calculate daily sales with vb.net and sql-CodePudding

I want to sum the Amount in my Bill Table via dates and report as daily sales

The table columns are Client Name, Amount, BDate This is what I have tried

   Dim query = "SELECT SUM(Amount)as sales FROM BillTbl where [BDate] = ? "
        Dim conkey As New SqlConnection(con)
        Dim cmd = New SqlCommand(query, conkey)
        cmd.Parameters.AddWithValue("@BDate", Now.ToString("MM/dd/yyyy"))

        conkey.Open()
        Dim total As Double = Convert.ToDouble(cmd.ExecuteScalar())
        check.Text = total.ToString
        conkey.Close()

CodePudding user response：

I know that this isn't an answer, but I can't add comments yet :(.

Could you include the DDL of the Sales table, along with some sample data, and include the results that you expect to be returned from running your query against that sample data? That could be a huge help in trying to figure out your issue.

At first glance, your solution SEEMS like it SHOULD work, but I can't make a definitive statement without the DDL and sample data, and desired results.

If you just want Sales BY Date, without regard to ClientName, you could try this:

SELECT [BDate], SUM([Amount]) AS Sales FROM [BillTbl] GROUP BY [BDate]

If this is SQL Server, and your BDate column includes time values as well, you could try this:

SELECT CAST([BDate] AS DATE), SUM([Amount]) AS [Sales] FROM [BillTbl] GROUP BY CAST([BDate] AS DATE)

Or if you have an older version of SQL Server, this should work:

SELECT CONVERT(VARCHAR(10), [BDate], 102), SUM([Amount]) AS [Sales] FROM [BillTbl] GROUP BY CONVERT(VARCHAR(10), [BDate], 102)

(Sorry again please forgive my ignorance, this is only my second attempt at an answer, and like I said, it SHOULD really be a comment instead.)

I hope this is of some value to someone. Note: Indexes would not be of much help with my sample queries, but there are ways around that too, if necessary.

CodePudding user response：

Assuming the type of the BDate is Date or DateTime and NOT varchar (which would be very bad):

Dim query = "SELECT SUM(Amount)as sales FROM BillTbl where [BDate] = cast(current_timestamp as Date)"

Using conkey As New SqlConnection(con), _
      cmd As New SqlCommand(query, con)

    conkey.Open()
    Dim total As Decimal = Convert.ToDecimal(cmd.ExecuteScalar())
End Using

I also wanted to talk about query parameters, but as you can see there is no need for any parameter in the above code at all; the database has its own way to get the current time. So instead I'll add another example to show the right way to use a (now unnecessary) parameter:

Dim query = "SELECT SUM(Amount)as sales FROM BillTbl where [BDate] = @BDate"

Using conkey As New SqlConnection(con), _
      cmd As New SqlCommand(query, con)

    ' Always choose an SqlDbType and Length to match the database column
    cmd.Parameters.Add("@BDate", SqlDbType.Date).Value = DateTime.Today
    conkey.Open()
    Dim total As Decimal = Convert.ToDecimal(cmd.ExecuteScalar())
End Using

Notice the parameter placeholder in the query has a name that matches what we use later on. Also notice we gave the parameter an explicit type. There is a problem with AddWithValue() that can result in major performance issues. The other change is replacing .Close() with a Using block, which is safer. The .Close() code might never run if there is an exception with a query, but the Using block will still close the connection.

Finally, not the total variable in both these examples goes out of scope immediately after the code block is done. You may need to declare the variable earlier in the scope to give it a larger scope, and then merely set the value at this time.