Datatabe Query

[PeteN]

Data Table example

Dim _weeklyprocTable As DataTable = New DataTable

_weeklyprocTable.Columns.Add("dbfmlivedataid", GetType(System.String))
_weeklyprocTable.Columns.Add("item", GetType(System.Int32))
_weeklyprocTable.Columns.Add("itemvalue", GetType(System.Decimal))

_weeklyprocTable.Rows.Add(“eac69a0a-7c41-e811-8421-00155d01e101”, 9, 1.00)
_weeklyprocTable.Rows.Add(“eac69a0a-7c41-e811-8421-00155d01e101”, 1, 10.00)
_weeklyprocTable.Rows.Add(“eac69a0a-7c41-e811-8421-00155d01e101”, 3, -2.00)
_weeklyprocTable.Rows.Add(“62109810-7c41-e811-8421-00155d01e101”, 9, 10.50)
_weeklyprocTable.Rows.Add(“fdc79a0a-7c41-e811-8421-00155d01e101”, 9, 0.00)
_weeklyprocTable.Rows.Add(“fdc79a0a-7c41-e811-8421-00155d01e101”, 1, 25.00)
_weeklyprocTable.Rows.Add(“fdc79a0a-7c41-e811-8421-00155d01e101”, 2, 72.00)
_weeklyprocTable.Rows.Add(“3cc69a0a-7c41-e811-8421-00155d01e101”, 9, 8.00)
_weeklyprocTable.Rows.Add(“3cc69a0a-7c41-e811-8421-00155d01e101”, 1, 19.00)
_weeklyprocTable.Rows.Add(“3cc69a0a-7c41-e811-8421-00155d01e101”, 8, -20.00)
_weeklyprocTable.Rows.Add(“36d79016-7c41-e811-8421-00155d01e101”, 9, 5.00)
_weeklyprocTable.Rows.Add(“36d79016-7c41-e811-8421-00155d01e101”, 1, 5.00)
_weeklyprocTable.Rows.Add(“36d79016-7c41-e811-8421-00155d01e101”, 8, -5.00)

this is going to be hard to explain!
I'm only looking to get rows for all items that
the sum of itemvalue > 0 where dbfmlivedataid is the same and item is not 9

In my example I would be looking to get returned ;

“eac69a0a-7c41-e811-8421-00155d01e101”, 9, 1.00
“eac69a0a-7c41-e811-8421-00155d01e101”, 1, 10.00
“eac69a0a-7c41-e811-8421-00155d01e101”, 3, -2.00

(itemvalue 10.00 + -2.00) > 0

"fdc79a0a-7c41-e811-8421-00155d01e101”, 9, 0.00
“fdc79a0a-7c41-e811-8421-00155d01e101”, 1, 25.00
“fdc79a0a-7c41-e811-8421-00155d01e101”, 2, 72.00

(item value 25.00 + 72.00) > 0


No record to be returned for;

“62109810-7c41-e811-8421-00155d01e101”, 9, 10.50 as there is no other records with the same dbfmlivedataid

“3cc69a0a-7c41-e811-8421-00155d01e101”, 9, 8.00
“3cc69a0a-7c41-e811-8421-00155d01e101”, 1, 19.00
“3cc69a0a-7c41-e811-8421-00155d01e101”, 8, -20.00

would also not be returned as itemvalue 19.00 + -20.00 ) = -1 which is < 0

“36d79016-7c41-e811-8421-00155d01e101”, 9, 5.00
“36d79016-7c41-e811-8421-00155d01e101”, 1, 5.00
“36d79016-7c41-e811-8421-00155d01e101”, 8, -5.00
would also not be returned as itemvalue 5.00 + -5.00 ) = 0 which is 0



any help on this nightmare would be most useful

 

[jmcilhinney]

Where is this data coming from in the first place? Is this a query that you really ought to be doing in SQL against a database or is there a specific reason not to do that (e.g. there is no database)?

 

[PeteN]

Where is this data coming from in the first place? Is this a query that you really ought to be doing in SQL against a database or is there a specific reason not to do that (e.g. there is no database)?

Hi,

Unfortunately this data is not coming from a SQL database. It is generated from different XML files from different sources.
At a push, if this cannot be done with a datatable, I will have to look at loading all the data into a SQL server

 

[jmcilhinney]

It can certainly be done but you'd do it using LINQ against a DataTable rather than the SQL you'd use against a database. I should have a chance to give it a go some time soon.

 

[JohnH]

One possible solution is to do one query to get ids based on group sums, and another query to get the rows based on join:

Dim ids = From row As DataRow In _weeklyprocTable.Rows
          Select dbfmlivedataid = CStr(row("dbfmlivedataid")), item = CInt(row("item")), itemvalue = CDec(row("itemvalue"))
          Where item <> 9
          Group By dbfmlivedataid Into Group
          Select dbfmlivedataid, sum = Aggregate grouprow In Group Into Sum(grouprow.itemvalue)
          Where sum > 0

Dim rows = From selection In ids
           Join row As DataRow In _weeklyprocTable.Rows
               On selection.dbfmlivedataid Equals CStr(row("dbfmlivedataid"))
           Select row

For Each row In rows
    Debug.WriteLine(String.Join(", ", row.ItemArray))
Next

shows these rows:

eac69a0a-7c41-e811-8421-00155d01e101, 9, 1
eac69a0a-7c41-e811-8421-00155d01e101, 1, 10
eac69a0a-7c41-e811-8421-00155d01e101, 3, -2
fdc79a0a-7c41-e811-8421-00155d01e101, 9, 0
fdc79a0a-7c41-e811-8421-00155d01e101, 1, 25
fdc79a0a-7c41-e811-8421-00155d01e101, 2, 72

Here are also alternatives using only one query, depending on how the results should be used further. One returns the rows as anonymous type, the other as DataRow.

        Dim rows2 = From row As DataRow In _weeklyprocTable.Rows
                    Select dbfmlivedataid = CStr(row("dbfmlivedataid")), item = CInt(row("item")), itemvalue = CDec(row("itemvalue"))
                    Group By dbfmlivedataid Into Group
                    Select Group, sum = Aggregate grouprow In Group Where grouprow.item <> 9 Into Sum(grouprow.itemvalue)
                    Where sum > 0
                    From g In Group Select g.dbfmlivedataid, g.item, g.itemvalue 

        Dim rows3 = From row As DataRow In _weeklyprocTable.Rows
                    Select dbfmlivedataid = CStr(row("dbfmlivedataid")), item = CInt(row("item")), itemvalue = CDec(row("itemvalue")), r = row
                    Group By dbfmlivedataid Into Group
                    Select Group, sum = Aggregate grouprow In Group Where grouprow.item <> 9 Into Sum(grouprow.itemvalue)
                    Where sum > 0
                    From g In Group Select g.r