Results 1 to 5 of 5

Thread: Datatabe Query

  1. #1
    .NET Framework
    .NET 3.0 (VS 2005/2008)
    Join Date
    Feb 2008
    Posts
    6
    Reputation
    0

    Datatabe Query

    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

  2. #2
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    14,002
    Reputation
    1730
    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)?

  3. #3
    .NET Framework
    .NET 3.0 (VS 2005/2008)
    Join Date
    Feb 2008
    Posts
    6
    Reputation
    0
    Quote Originally Posted by jmcilhinney View Post
    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

  4. #4
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    14,002
    Reputation
    1730
    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.

  5. #5
    .NET Framework
    .NET 4.5
    Join Date
    Dec 2005
    Location
    Norway
    Posts
    15,214
    Reputation
    2861
    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
    [xcode=vb] code here [/xcode] - see bbcode list or use formatting buttons in posting editor.

    Visual Studio Community 2017

Similar Threads

  1. Query
    By boknat in forum Windows Forms
    Replies: 4
    Last Post: 01-23-2012, 8:13 AM
  2. Replies: 5
    Last Post: 12-11-2010, 12:05 PM
  3. Question how to write a Sql query in VB 2008 Query builder
    By kbsudhir in forum ADO.NET
    Replies: 2
    Last Post: 03-08-2010, 6:39 AM
  4. Resolved Access query Not in another query
    By JuggaloBrotha in forum MS Access
    Replies: 4
    Last Post: 09-10-2009, 12:02 PM
  5. query help
    By Steven Low in forum Oracle
    Replies: 7
    Last Post: 04-10-2007, 8:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •