Btukfyl

In this project I'm working on, I have to create a bunch of pie charts using SQL queries.

There are 2 phases of SQL

First, I want to collect the names of Machines operating during the time frame the user has selected and place it in a DataTable.

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")

    Dim dt1 As DataTable = GetData(query1)

For this example the machine names I collected is

--------------

|    MacId   |

--------------

|    A01     |

|    A02     |

|    C01     |

--------------

For the second phase I want to use the data collected in the first phase and reference it into another SQL query. My idea is to have the content in the table to convert into a string, like so:

'A01', 'A02', 'C01' 

That way I'll only need to run the 2nd SQL query once instead of having to rely on For - Next statement to get the data, which was what I used the last time resulting in long loading times, expecially when there are 30+ machines.

How can I get this result? I've been thinking about it for 2 days now and I have no idea how to start.

Edit:

For some context here is my current code. Please ignore the mess.

Sub draw_chart1() 'All Machines



    Dim check1, check2, fi, cnt

    Dim seperator As String = ", "

    Dim columnindex As Integer = 0



    Dim myConnectionString As String = "Provider=SQLOLEDB;" & SQLDB_pp.ConnectionString

    fi = 0

    cnt = 0



    PlaceHolder1.Dispose()

    PlaceHolder2.Dispose()



    'Get all machines

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")

    Dim dt1 As DataTable = GetData(query1)



    Dim result As String = String.Join(seperator, dt1.AsEnumerable.Select(Function(r) "'" + r(columnindex).ToString() + "'")).TrimEnd(seperator.ToCharArray())



    Dim dt As DataTable

    Dim query As String





    'Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

    '                                        & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _

    '                                        & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

    '                                        & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

    '                                        & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

    '                                        & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _

    '                                        & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")

    'Dim dt As DataTable = GetData(query)



    'For q As Integer = 0 To dt1.Rows.Count - 1



    'check1 = dt1.DataSet.ToString

    'check1 = dt1.Rows(q)(0).ToString() '(0) means column, since query1 only search for 1 column, it uses 0 = 1ST COLUMN



    query = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ({0}) ) Z " _

            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ({0}) group by A.MacID, A.EventName) a " _

            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName", result)



    dt = GetData(query)



    'Next



    'dt = GetData(query)



    For a As Integer = 0 To dt.Rows.Count - 1



        check2 = dt.Rows(a)(0).ToString()



        Dim mychart As Chart = New Chart

        Dim ChartArea1 As ChartArea = New ChartArea

        Dim Legend1 As Legend = New Legend



        'Dim dt As DataTable = GetData(query) '30 sec to process

        Dim x As String() = New String(dt.Rows.Count - 1) {}

        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}

        For i As Integer = 0 To dt.Rows.Count - 1



            x(i) = dt.Rows(i)(1).ToString()

            y(i) = Convert.ToInt32(dt.Rows(i)(2))

        Next



        mychart.Width = 600

        mychart.Height = 400



        mychart.ChartAreas.Clear()

        mychart.ChartAreas.Add("ChartArea2")



        mychart.Series.Clear()

        mychart.Series.Add(0)

        mychart.Series(0).Points.DataBindXY(x, y)



        mychart.Titles.Clear()

        mychart.Titles.Add("[" & a + 1 & "] " & check2.ToString.ToUpper)

        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)

        mychart.Titles(0).BackColor = Color.PaleTurquoise

        mychart.Titles(0).ForeColor = Color.Black



        mychart.Series(0).ChartType = SeriesChartType.Pie

        mychart.Series(0).LegendText = "#VALX"

        mychart.Series(0)("BarLabelStyle") = "Center"

        mychart.Series(0)("pointWidth") = "1"

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).Label = "#PERCENT"

        mychart.Series(0).ShadowColor = Color.Gray

        mychart.Series(0).ShadowOffset = 10



        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk

        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)



        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"

        mychart.Series(0).ToolTip = "#VALX - #PERCENT"



        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True



        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"

        'new

        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"



        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid

        mychart.Palette = ChartColorPalette.None

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).BorderColor = Color.Black



        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.White, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}



        mychart.Legends.Clear()

        mychart.Legends.Add(0)

        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)

        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom



        mychart.DataBind()



        If (a + 1) Mod 2 <> 0 Then



            PlaceHolder1.Controls.Add(mychart)



        End If



        If (a + 1) Mod 2 = 0 Then



            PlaceHolder2.Controls.Add(mychart)



        End If



    Next

End Sub



 Private Shared Function GetData(ByVal query As String) As DataTable



    Dim dt As New DataTable()

    Dim cmd As New SqlCommand(query)

    '  Dim constr As [String] = ConfigurationManager.ConnectionStrings("SQLDB_pp").ConnectionString

    'Dim con As New SqlConnection(SQLDB_pp)

    Dim sda As New SqlDataAdapter()

    cmd.CommandType = CommandType.Text

    cmd.Connection = SQLDB_pp

    sda.SelectCommand = cmd

    sda.Fill(dt)

    Return dt



End Function

Edit 2:

Dim sb As New StringBuilder()



    sb.Append("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in (")



    Dim params As New List(Of SqlParameter)()



    Dim max As Integer = result.Length - 1



    For i As Integer = 0 To max



        If i = max Then

            sb.Append("MacID" & i.ToString())

        Else

            sb.Append("MacID" & i.ToString() & ", ")

        End If

        params.Add(New SqlParameter("MacID" & i.ToString(), result(i)))

    Next



    sb.Append(") ) Z " _

            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in (")



    Dim params2 As New List(Of SqlParameter)()



    Dim max2 As Integer = result.Length - 1



    For j As Integer = 0 To max2



        If j = max2 Then

            sb.Append("MacID" & j.ToString())

        Else

            sb.Append("MacID" & j.ToString() & ", ")

        End If

        params2.Add(New SqlParameter("MacID" & j.ToString(), result(j)))

    Next



    sb.Append(") group by A.MacID, A.EventName) a " _

            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")



    Dim q As String = sb.ToString()

Edit 4:

This is the full code for the sub I'm having problems with. This is the original code before any modifications with slow load times during the 2nd SQL.

Sub draw_chart1() 'All Machines



    Dim check1, check2, fi, cnt



    Dim sql

    Dim myConnectionString As String = "Provider=SQLOLEDB;" & SQLDB_pp.ConnectionString

    fi = 0

    cnt = 0



    PlaceHolder1.Dispose()

    PlaceHolder2.Dispose()

    'Get all machines

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")

    Dim dt1 As DataTable = GetData(query1)





    'Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

    '                                        & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _

    '                                        & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

    '                                        & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

    '                                        & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

    '                                        & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _

    '                                        & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")

    'Dim dt As DataTable = GetData(query)



    For q As Integer = 0 To dt1.Rows.Count - 1



        check1 = dt1.Rows(q)(0).ToString()



        Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

                                            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & check1 & "') ) Z " _

                                            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

                                            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

                                            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

                                            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & check1 & "') group by A.MacID, A.EventName) a " _

                                            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")



        Dim dt As DataTable = GetData(query) '47 sec to process







        Dim mychart As Chart = New Chart

        Dim ChartArea1 As ChartArea = New ChartArea

        Dim Legend1 As Legend = New Legend



        'Dim dt As DataTable = GetData(query) '30 sec to process

        Dim x As String() = New String(dt.Rows.Count - 1) {}

        Dim y As Integer() = New Integer(dt.Rows.Count - 1) {}

        For i As Integer = 0 To dt.Rows.Count - 1



            x(i) = dt.Rows(i)(1).ToString()

            ' y(i) = dt.Rows(i)(2).ToString()

            y(i) = Convert.ToInt32(dt.Rows(i)(2))

        Next



        'Dim myConnection As New OleDbConnection(myConnectionString)

        'Dim myCommand As New OleDbCommand(sql, myConnection)

        'mychart.Width = Unit.Pixel(Session("sw") - 100)

        'mychart.Height = Unit.Pixel((Session("sh") / 2) - 88)



        mychart.Width = 600

        mychart.Height = 400



        mychart.ChartAreas.Clear()

        mychart.ChartAreas.Add("ChartArea1")



        mychart.Series.Clear()

        mychart.Series.Add(0)

        mychart.Series(0).Points.DataBindXY(x, y)



        mychart.Titles.Clear()

        mychart.Titles.Add("[" & q + 1 & "] " & check1.ToString.ToUpper)

        mychart.Titles(0).Font = New System.Drawing.Font("Tahoma", 12, System.Drawing.FontStyle.Bold)

        mychart.Titles(0).BackColor = Color.PaleTurquoise

        mychart.Titles(0).ForeColor = Color.Black



        mychart.Series(0).ChartType = SeriesChartType.Pie

        '  mychart.Series(0).Points.DataBindXY(x, y)

        mychart.Series(0).LegendText = "#VALX"

        mychart.Series(0)("BarLabelStyle") = "Center"

        mychart.Series(0)("pointWidth") = "1"

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).Label = "#PERCENT"

        mychart.Series(0).ShadowColor = Color.Gray

        mychart.Series(0).ShadowOffset = 10



        mychart.Series(0).LabelBackColor = Drawing.Color.Cornsilk

        mychart.Series(0).Font = New Font("Tahoma", 9, FontStyle.Bold)



        'Chart1.Series(0).LabelToolTip = "#LABEL Percent: #PERCENT"

        mychart.Series(0).LegendToolTip = "#VALX - #PERCENT"

        mychart.Series(0).ToolTip = "#VALX - #PERCENT"



        mychart.ChartAreas("ChartArea1").Area3DStyle.Enable3D = True



        mychart.Series(0).CustomProperties = "DrawingStyle=LightToDark"

        'new

        Chart1.Series(0).CustomProperties = "PieLabelStyle=Outside"



        mychart.ChartAreas("ChartArea1").BorderDashStyle = BorderStyle.Solid

        mychart.Palette = ChartColorPalette.None

        mychart.Series(0).BorderDashStyle = ChartDashStyle.Solid

        mychart.Series(0).BorderWidth = 2

        mychart.Series(0).BorderColor = Color.Black



        mychart.PaletteCustomColors = {Drawing.Color.Black, Drawing.Color.White, Drawing.Color.Blue, Drawing.Color.Yellow, Drawing.Color.Red, Drawing.Color.Orange, Drawing.Color.Green}



        mychart.Legends.Clear()

        mychart.Legends.Add(0)

        'Chart1.Legends(0).Enabled = True

        ''Chart1.Legends(0).BackColor = Drawing.Color.LightGreenplace

        mychart.Legends(0).Font = New Font("Tahoma", 10, FontStyle.Bold)

        mychart.Legends(0).Docking = System.Web.UI.DataVisualization.Charting.Docking.Bottom

        'Chart1.Legends(0).Alignment = Drawing.StringAlignment.Center

        'Chart1.Legends(0).BackColor = System.Drawing.Color.Transparent



        mychart.DataBind()



        'myplace.Visible = True

        If (q + 1) Mod 2 <> 0 Then

            PlaceHolder1.Controls.Add(mychart)

            ' Dim spacer As LiteralControl = New LiteralControl("<p />")

            ' PlaceHolder1.Controls.Add(spacer)

        End If

        'Exit For

        If (q + 1) Mod 2 = 0 Then



            PlaceHolder2.Controls.Add(mychart)

            'Dim spacer As LiteralControl = New LiteralControl("<p />")

            ' PlaceHolder2.Controls.Add(spacer)



        End If



    Next

End Sub

You can use String.Join() to join all DataTable column values by converting the DataTable column into string array first:

Dim dt1 As DataTable = GetData(query1)



Dim arr As String() = dt1.AsEnumerable().[Select](Function(x) x.Field(Of String)("MacId")).ToArray()



Dim result As String = String.Join(",", arr)

Or use this line:

Dim result As String = table.AsEnumerable().[Select](Function(x) x("MacId").ToString()).Aggregate(Function(a, b) String.Concat(a, "," & b))

Afterwards, the output string can be passed into query string with table-valued parameters, or using StringBuilder to add parameters for each element of string array created by ToArray() with WHERE IN query:

Dim sb As New StringBuilder()



' example query string

sb.Append("SELECT * FROM TableName WHERE ColumnName IN (")



Dim cmd As New SqlCommand()



' note: array is zero-based

Dim max As Integer = arr.Length - 1



For i As Integer = 0 To max

    If i = max Then

       sb.Append("@MacId" & i.ToString())

    Else

       sb.Append("@MacId" & i.ToString() & ",")

    End If

    cmd.Parameters.AddWithValue("@MacId" & i.ToString(), arr(i))

Next



sb.Append(")")



Dim query As String = sb.ToString()



cmd.CommandText = query



' execute the query

Update:

Since the query executed inside a function, you need to pass the query parameters to the function:

Dim sb As New StringBuilder()



' example query string

sb.Append("SELECT * FROM TableName WHERE ColumnName IN (")



Dim params As New List(Of SqlParameter)()



For i As Integer = 0 To max

    If i = max Then

       sb.Append("@MacId" & i.ToString())

    Else

       sb.Append("@MacId" & i.ToString() & ",")

    End If

    params.Add(New SqlParameter("@MacId" & i.ToString(), arr(i)))

Next



sb.Append(")")



Dim query As String = sb.ToString()



dt = GetData(query, params)

Function contents

Private Shared Function GetData(ByVal query As String, Optional ByVal parameters As List(Of SqlParameter) = Nothing) As DataTable



    Dim dt As New DataTable()

    Dim cmd As New SqlCommand(query)



    If parameter IsNot Nothing Then

       cmd.Parameters.AddRange(parameters.ToArray())

    End If



    Dim sda As New SqlDataAdapter()

    cmd.CommandType = CommandType.Text

    cmd.Connection = SQLDB_pp

    sda.SelectCommand = cmd

    sda.Fill(dt)

    Return dt



End Function

You could use string.Join to patch together all the Rows values of a specific Column using as separator: ", " (comma + white space).

With the DataTable.Rows (DataRowCollection) as source, using the LINQ Select() method, you can transform the Rows[Column] content in the string format that you require.

A possible result:

Dim separator As String = ", "

Dim ColumnIndex as Integer = 0



Dim AllColumnValues As String = String.Join(separator, 

    dt1.Rows.OfType(Of DataRow).Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

If you are referencing the System.Data.DataSetExtension assembly, you can use the DataTable.AsEnumerable extension. The code could change like this:

Dim AllColumnValues As String = String.Join(separator,

    dt1.AsEnumerable.Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

If some of the values can be null/empty, you might want to add a Where() clause, filtering the null values:

Dim AllColumnValues As String = String.Join(separator, 

    dt1.AsEnumerable.Where(Function(r) Not r.IsNull(ColumnIndex)).

        Select(Function(r) "'" + r(ColumnIndex).ToString() + "'"))

Finally after hours of trial and error, I finally got the string to work properly.

Note to self: Having a GridView to check the DataTable contents is a huge help.

Now my code basically looks like so:

    Dim query1 As String = String.Format("SELECT DISTINCT A.MacID FROM dbo.tblMachine A LEFT JOIN  dbo.tblDataHdr b on a.MacID = b.MacID where DayID between '" & TextBox1.Text & "' AND '" & TextBox3.Text & "' ORDER BY A.MACID")

    Dim dt1 As DataTable = GetData(query1)





    Dim result As String = dt1.AsEnumerable().[Select](Function(x) x("MacId").ToString()).Aggregate(Function(a, b) String.Concat(a & "'" & "," & "'" & b))





    Dim query As String = String.Format("SELECT Z.MacID, Z.EventName, ISNULL(DIFF,0) AS DIFF FROM (select distinct MacID, EventName from dbo.tblMachine a " _

                                            & "join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur where EventName <> 'ON'  ) b on b.EventName <> ''  and MacID in ('" & result & "') ) Z " _

                                            & "LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) " _

                                            & "as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A LEFT JOIN ( SELECT MacID, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff FROM  dbo.tblEvtDur " _

                                            & "WHERE DayID between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  GROUP BY MacID ) B ON  A.MacID = B.MacID WHERE DayID " _

                                            & "between '" & TextBox1.Text & "' and '" & TextBox3.Text & "'  AND A.MacID in ('" & result & "') group by A.MacID, A.EventName) a " _

                                            & "ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName")

    Dim dt As DataTable = GetData(query)



'Use this to help check the DataTable/Strings

GridView2.DataSource = dt 'change what you want to check accordingly

GridView2.DataBind()

By using GridView and binding the data to each query really helps in letting me see how the data looks. And the data shown in dt looks perfect.

Now my next problem is actually using the data to create a pie chart for each machine. But I'll post that in a separate question.