Solved

Recursive function problem

Posted on 2006-11-14
4
211 Views
Last Modified: 2008-02-01
I have a datatable with the following columns:

Heading, Subheading, Number1, Number2, Number3, Number4

I need to sum the number columns for each Heading and Subheading. How do I do this using a recursive function? I vaguely remember recursion when I was studying, is it the best approach? The select statement that fills this datatable is ordered by Heading, Subheading.

In the future I may need to add deeper levels below the sub heading. I just need some pointers on how to get started writing the algorithm, the general idea of how to solve this.

Cheers




0
Comment
Question by:craigdev
  • 2
  • 2
4 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 17936789
Hi craigdev,

Generally it is easier to let SQL do this for you if you already have the data there:

Select Heading, SubHeading, Sum(Number1) As Number1, Sum(Number2) As Number2, Sum(Number3) As Number3, Sum(Number4) As Number4 from MyTable Group By Heading,SubHeading Order By Heading,SubHeading

Tim Cottee
0
 
LVL 1

Author Comment

by:craigdev
ID: 17936837
true, thanks but then I have to write multiple select statements and try and combine all the results into a big table. What Im trying to do is make a versatile function that I can use to sum up any columns using a single select. The table that I mentioned may actually have several Heading and subheading columns and number columns. If I can get it to work in the case above then I can tweak the algorithm a bit for any case.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 17937069
craigdev,

Ok, that makes it a slightly more interesting problem.

The following function returns a datatable when supplied with the source datatable, number of groupings and number of columns to sum.

The requirements are that the datatable has the grouping columns first, followed by the columns to total and that the ordering of the source table is heading,subheading,subsubheading etc.

    Private Function SumTable(ByRef Source As DataTable, ByVal Groups As Int16, ByVal Sums As Int16) As DataTable
        Dim Result As DataTable = New DataTable
        Dim aryGroups() As String
        ReDim aryGroups(Groups)
        Dim intGroup As Int16
        For intGroup = 0 To Groups - 1
            Result.Columns.Add(Source.Columns(intGroup).ColumnName, Type.GetType("System.String"))
        Next
        For intGroup = 0 To Sums - 1
            Result.Columns.Add(Source.Columns(intGroup + Groups).ColumnName, Type.GetType("System.Double"))
        Next
        Dim intRow As Integer
        Dim TargetRow As DataRow
        For intRow = 0 To Source.Rows.Count - 1
            Dim SourceRow As DataRow = Source.Rows(intRow)
            Dim NewRow As Boolean = False
            For intGroup = 0 To Groups - 1
                If CType(SourceRow.Item(intGroup), String) <> aryGroups(intGroup) Then
                    NewRow = True
                End If
            Next
            If NewRow Then
                TargetRow = Result.NewRow
                For intGroup = 0 To Groups - 1
                    aryGroups(intGroup) = CType(SourceRow.Item(intGroup), String)
                    TargetRow.Item(intGroup) = CType(SourceRow.Item(intGroup), String)
                Next
                For intGroup = 0 To Sums - 1
                    TargetRow.Item(Groups + intGroup) = 0
                Next
                Result.Rows.Add(TargetRow)
            End If
            For intGroup = 0 To Sums - 1
                Result.Rows(Result.Rows.Count - 1).Item(Groups + intGroup) = CType(CType(Result.Rows(Result.Rows.Count - 1).Item(Groups + intGroup), Double) + CType(Source.Rows(intRow).Item(Groups + intGroup), Double), String)
            Next
        Next
        Return Result
    End Function

This then will take any datatable in the appropriate format and return it summed on the various headings.

Tim
0
 
LVL 1

Author Comment

by:craigdev
ID: 17937160
thanks heaps that worked perfectly!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question