DataTable Dilemma

[VBStarterKid]

Hello there,
I have a vb.net algorithm that should calculate the daily and monthly sum of productive hours of the employees from the sales table in an SQL Server database.

What my program already does is that it has extracted the relevant employees' records from the sales table based on the selected month and year from the user interface screen, with all the records going into a DataSet. At this point, my dataset already contains the records necessary for calculation. Since I want to calculate the daily productive hour of each employee, I need to select all the records for that particular employee from the dataset on that particular day.

I have devised a For Loop that loops each day, starting form 1st of the month to the 31st, depending on the month selected.

Now, my problem...the earlier algorithm OUTER LOOPS each datarow in the dataset, and INNER LOOPS each day in the month. basically it means for each record in my dataset, the program loops roughly 31 times just to check whether the particular employee was productive on the selected day. If the dataset contains 100 records, the looping will run 100 * 31 times, and what if only 1 record satisfies the condition that I set. That is wasting time and resources.

I found that I can use DataTable.Select method instead to filter out the datarows based on the condition that is sent to the method as a parameter, but I can't use wildcards in the middle of the string. I wanted to make the day value in my date-based condition as a dynamic value, changing as necessary following the FOR LOOP that I mentioned earlier. For example, in my FOR loop, the condition is that the records I want to select for calculation must have a sales date of #mm/1/yyyy# with the value 1 there dynamicaly changing as the loop progresses. The next loop, I would want the records with a sales date of #mm/2/yyyy#. And so on for the next loops.

How can I do that?

Thanks for the invaluable advice.

The following is my original code snippet THAT works, but not efficient:

            ' Loop each record in the dataset, additionally, for each record loop, 
            ' inner loop the number of days in the selected month

            For Each dr In ds.Tables(0).Rows
                For i = 1 To nDayInMonth

                    ' Advance the date of loop by i count
                    dtLoopDate = dtLoopDate.AddDays(-dtLoopDate.Day + i)
                    ' RecordDate is the sales date time that the current datarow contains
                    dtRecordDate = CType(dr("StartTime"), Date)

                    ' If the record date is within 24 hours of the date in the current loop then do the statements
                    If dtRecordDate >= dtLoopDate And dtRecordDate < dtLoopDate.AddDays(1) Then

                       .....
                       ...
                    End If
               Next
           Next

My plan now is to modify the algorithm to use the following method:

            Dim rows() As DataRow
            Dim expression As String
            Dim dtLoopDate As Date

            For i = 1 To nDayInMonth

                  ' Advance the date of the month by i count
                  dtLoopDate = dtLoopDate.AddDays(-dtLoopDate.Day + i)

                  expression = "dtLoopDate = CType(dr("StartTime"), Date)"
                  rows = ds.Tables(0).Select(expression)
            Next

Is my logic correct?

 

[alander]

maybe u can post codes and we can help u better remember to use the code snippet

 

[cjard]

I do this kind of stuff in the database itself.. SQLServer 2005 is a bit crappy when it comes to directed grouping, but I'm given to understand that SQLS2008 catches up with Oracle9 in offering GROUPING SETS. As it stands, if youre on 2005 you can use ROLLUP

http://www.google.co.uk/search?hl=e...=result&cd=1&q=SQL+Server+with+rollup&spell=1


The theory would go soemthing like this:

SELECT
  employee_name,
  MONTH(event_date) as month_of_work,
  MONTH(event_date) + DAY(event_date) as day_of_work,
  SUM(finish_time - start_time) as hours_worked
WHERE
  YEAR(event_date) = 2007
GROUP BY 
  employee_name ,
  MONTH(event_date) as month_of_work,
  MONTH(event_date) + DAY(event_date)

You'd get 4 columns, employee name, month, day and the hours. Not only would you get the hours worked each day, but there would be a row where the day was null and that would be all the hours for that employee that month, a row where day and month were null and that would be all hours for that employee that year, and a row where everything was null and that would be all the hours worked by everyone in the company

Databases are designed for this, take a look