CODEWITHSUNDEEP
c#sql-server
Cristian Muscalu
Cristian Muscalu

Reputation: 9915

Multiple entries within the hour should be counted only once

//Query data
public void GetDataByCountry(string gameID)
{
    string query = "SELECT clickDate, userIp FROM gameLinks 
                     where userCountry = @userCountry";

    using (con = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(query, con);
        command.Parameters.AddWithValue("@userCountry", gameID);
        con.Open();

        SqlDataReader reader = command.ExecuteReader();

        dgvHourIp.Rows.Clear();
        while (reader.Read())
        {
            string[] row = new[]
            {
                reader[0].ToString(), //hour
                reader[1].ToString()  //ip
            };
            dgvHourIp.Rows.Add(row);
        }
        reader.Close();
        reader.Dispose();
    }
}

Output: enter image description here

Desired Output enter image description here

How can i manipulate this result to be filtered by this condition:
- if the same IP has multiple entries within the hour, it should be counted only once

Should i try to convert the reader[] in C# and then try to compare it?
Or should i use only SQL queryes instead?

Upvotes: 2

Views: 68

Answers (2)

McNets
McNets

Reputation: 10807

According to @Cris suggestion, records should be ordered by ip, date.

string query = "SELECT clickDate, userIp FROM gameLinks where userCountry = @userCountry order by userIp, clickDate";

The use two variables just to store last row.

SqlDataReader reader = command.ExecuteReader();

srtring lastIp = "";
DateTime lastDate;

dgvHourIp.Rows.Clear();
while (reader.Read())
{
    string[] row = new[]
    {
        reader[0].ToString(), //hour
        reader[1].ToString()  //ip
     };

     if (lastIp != "")
     {
         TimeSpan ts = Convert.ToDateTime(row[0]).Substract(lastDate));
         if (lastIp == row[1] && ts.TotalMinutes < 60)
         {
             lastIp = row[1];
             continue;
         }
     }
     lastIp = row[1];
     lastDate = Convert.ToDateTime(row[0]);

     dgvHourIp.Rows.Add(row);
}

Only one thing, what happens when:

record 1 --> 40 min --> record 2 --> 50 min --> record 3

Upvotes: 1

Surya
Surya

Reputation: 101

you can use 

DataTable dttemp = yourtableName.DefaultView.ToTable(true, "Hour", "IPAddress");

now,you have a distinct data inside dttemp variable

or you can use LINQ

var distrows= yourtableName.AsEnumerable().Distinct();

Upvotes: 1

Related Questions