none
The annual query of the database RRS feed

  • Question

  • The annual query of the database
    I have Data Grid View with a numeric values column and a date column
    I want to make an annual query where all the months of the year are listed and if there is a month found, the cell values for the month are collected
    And put it as follows

    count             date

    column 1      column 2

    50                 1/01/2018

    60                 31/01/2018

    70                 10/03/2018

    80                 1/04/2018

    90                 1/05/2018

    100               1/12/2018

    wanted to another datagridview

    month                 day                                count

    1               1/01/2018- 31/01/2018             50+60=  110

    2                                                               0

    3                                                             70

    4                                                             80

    5                                                               90

    6                                                     0

    7  ,8,9,10,11                                      0 etic..


    12                                                    100



    • Edited by monemas Wednesday, February 21, 2018 6:47 PM
    Wednesday, February 21, 2018 6:44 PM

Answers

  • Hi monemas,

    There are two things to pay attention to, one is using Distinct in sql query, another is using dictionary to integrate data

     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView2.DataSource = fun()
        End Sub
        Dim dic As New Dictionary(Of Integer, Integer)
        Dim dt As New DataTable
        Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testdb;Integrated Security=True"
            Dim sql As String = "select distinct count(*) as count, date from Test7 group by date"
            Using conn As New SqlConnection(str)
                conn.Open()
                Using cmd As New SqlCommand(sql, conn)
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Sub
    
    
        Private Function fun() As DataTable
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim date1 As DateTime = Convert.ToDateTime(dt(i)(1)).ToString("yyyy-MM-dd")
                Dim month As Integer = date1.Month
                If dic.ContainsKey(month) = True Then
                    dic(month) += dt(i)(0)
                Else
                    dic(month) = dt(i)(0)
                End If
            Next
            Dim dt1 As New DataTable
            dt1.Columns.Add("Month", GetType(Integer))
            dt1.Columns.Add("Count", GetType(Integer))
            For Each pair As KeyValuePair(Of Integer, Integer) In dic
                'MsgBox(pair.Key & "  -  " & pair.Value)
                dt1.Rows.Add(pair.Key, pair.Value)
            Next
            Return dt1
        End Function

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Saturday, February 24, 2018 6:56 PM
    Saturday, February 24, 2018 8:47 AM
    Moderator

All replies

  • Use Count and Distinct in your select statement . The rest you should be able to do yourself because it is simply using a Datatable and that we have shown you already more times. 

    https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct


    Success
    Cor

    Wednesday, February 21, 2018 6:51 PM
  • Hi monemas,

    There are two things to pay attention to, one is using Distinct in sql query, another is using dictionary to integrate data

     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView2.DataSource = fun()
        End Sub
        Dim dic As New Dictionary(Of Integer, Integer)
        Dim dt As New DataTable
        Private Sub Form8_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testdb;Integrated Security=True"
            Dim sql As String = "select distinct count(*) as count, date from Test7 group by date"
            Using conn As New SqlConnection(str)
                conn.Open()
                Using cmd As New SqlCommand(sql, conn)
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Sub
    
    
        Private Function fun() As DataTable
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim date1 As DateTime = Convert.ToDateTime(dt(i)(1)).ToString("yyyy-MM-dd")
                Dim month As Integer = date1.Month
                If dic.ContainsKey(month) = True Then
                    dic(month) += dt(i)(0)
                Else
                    dic(month) = dt(i)(0)
                End If
            Next
            Dim dt1 As New DataTable
            dt1.Columns.Add("Month", GetType(Integer))
            dt1.Columns.Add("Count", GetType(Integer))
            For Each pair As KeyValuePair(Of Integer, Integer) In dic
                'MsgBox(pair.Key & "  -  " & pair.Value)
                dt1.Rows.Add(pair.Key, pair.Value)
            Next
            Return dt1
        End Function

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Saturday, February 24, 2018 6:56 PM
    Saturday, February 24, 2018 8:47 AM
    Moderator