CODEWITHSUNDEEP
vb.netdata-bindingdatatable
Lady A
Lady A

Reputation: 147

Get Sum of Required Values in DataTable VB.Net

I have a datatable with the following fields:

The data is as follows:

Day Date    Room No.    Room Rate   No. of Person       Amount
1   4/9/2018    101         900.00      2           1, 800.00
2   4/10/2018   101         900.00      2           1, 800.00
3   4/10/2018   101         900.00      2           1, 800.00
1   4/9/2018    102         1000.00     3           3, 000.00
2   4/10/2018   102         1000.00     3           3, 000.00
3   4/10/2018   102         1000.00     3           3, 000.00

I would like to get the total amount by getting the sum of Amount. But, the last day for each Room should not be included. With the above example, the total amount would be 9, 600.00 since Room 101 and Room 102 of day 3 is not included.

I tried to use the datatable compute function, but this will not be effective:

Convert.ToInt32(DataSet.Tables("dt_Lodging").Compute("SUM(Amount)", "Day = 3")

Day will not be limited to 3. If we have days 1 to 5, day 5 is the one which will not be included in Total.

Upvotes: 1

Views: 2579

Answers (1)

PavlinII
PavlinII

Reputation: 1083

Try his line

Dim Amount As Decimal = T.Rows.OfType(Of DataRow).GroupBy(Function(X) CStr(X("RoomNo"))).Sum(Function(Room) Room.Take(Room.Count - 1).Sum(Function(X) pDec(X("Amount"))))

But your question is not clear abount name of RoomNo column. And the query has some presumptions according to your question. It will not work when the room number repeats in different periods. Or when the rows are not sorted by date.

This solution is not optimized in any way. It just calculates the value.

Does room rate vary by date? Room rate*Number of nights would be better solution. You should work with nights of stay instead of days anyway.

EDIT: Full code version

Public Sub Test()
    Dim R As DataRow, i As Integer
    Using T As New DataTable
        T.Columns.Add("RoomNo", GetType(String))
        T.Columns.Add("Amount", GetType(Decimal))
        For i = 1 To 3
            R = T.NewRow
            R("RoomNo") = "101"
            R("Amount") = 1800
            T.Rows.Add(R)
            R = T.NewRow
            R("RoomNo") = "102"
            R("Amount") = 3000
            T.Rows.Add(R)
        Next
        Dim Amount As Decimal = T.Rows.OfType(Of DataRow).GroupBy(Function(X) CStr(X("RoomNo"))).Sum(Function(Room) Room.Take(Room.Count - 1).Sum(Function(X) CDec(X("Amount"))))
        Debugger.Break()
    End Using
End Sub

Upvotes: 1

Related Questions