Count records group by Year and Week Number from DateTime column in ASP.Net using C# and VB.Net

Hi chetan,

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView runat="server" ID="gvDetails"></asp:GridView>

Namepsaces

C#

using System.Data;
using System.Globalization;
using System.Linq;

VB.Net

Imports System.Data
Imports System.Globalization
Imports System.Linq

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    List<MyClass> datas = new List<MyClass>();
    datas.Add(new MyClass { ProductCode = "001", _Date = Convert.ToDateTime("2020/12/28") });
    datas.Add(new MyClass { ProductCode = "002", _Date = Convert.ToDateTime("2020/12/29") });
    datas.Add(new MyClass { ProductCode = "003", _Date = Convert.ToDateTime("2020/12/30") });
    datas.Add(new MyClass { ProductCode = "004", _Date = Convert.ToDateTime("2021/01/01") });
    datas.Add(new MyClass { ProductCode = "005", _Date = Convert.ToDateTime("2021/01/02") });
    datas.Add(new MyClass { ProductCode = "006", _Date = Convert.ToDateTime("2021/01/03") });
    datas.Add(new MyClass { ProductCode = "007", _Date = Convert.ToDateTime("2021/01/04") });
    datas.Add(new MyClass { ProductCode = "008", _Date = Convert.ToDateTime("2021/01/05") });

    var result = datas.Select(x => new
    {
        ProductCode = x.ProductCode,
        Date = x._Date,
        Week = GetWeekNumber(x._Date),
        Year = x._Date.Year
    }).ToList();

    DataTable dt = new DataTable();
    dt.Columns.Add("Year");
    dt.Columns.Add("Week");
    dt.Columns.Add("ProductCodeCount");
    foreach (var _year in result.Select(x => new { x.Date.Year, x.Week }).Distinct())
    {
        DataRow dr = dt.NewRow();
        dr["Year"] = _year.Year;
        dr["Week"] = _year.Week;
        dr["ProductCodeCount"] = result.Where(x => x.Year == _year.Year && x.Week == _year.Week).Count();
        dt.Rows.Add(dr);
    }

    gvDetails.DataSource = new DataView(dt).ToTable(true);
    gvDetails.DataBind();
}

public int GetWeekNumber(DateTime time)
{
    DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
    if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
    {
        time = time.AddDays(3);
    }

    return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

public class MyClass
{
    public string ProductCode { get; set; }
    public DateTime _Date { get; set; }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim datas As List(Of [MyClass]) = New List(Of [MyClass])()
    datas.Add(New [MyClass] With {.ProductCode = "001", ._Date = Convert.ToDateTime("2020/12/28")})
    datas.Add(New [MyClass] With {.ProductCode = "002", ._Date = Convert.ToDateTime("2020/12/29")})
    datas.Add(New [MyClass] With {.ProductCode = "003", ._Date = Convert.ToDateTime("2020/12/30")})
    datas.Add(New [MyClass] With {.ProductCode = "004", ._Date = Convert.ToDateTime("2021/01/01")})
    datas.Add(New [MyClass] With {.ProductCode = "005", ._Date = Convert.ToDateTime("2021/01/02")})
    datas.Add(New [MyClass] With {.ProductCode = "006", ._Date = Convert.ToDateTime("2021/01/03")})
    datas.Add(New [MyClass] With {.ProductCode = "007", ._Date = Convert.ToDateTime("2021/01/04")})
    datas.Add(New [MyClass] With {.ProductCode = "008", ._Date = Convert.ToDateTime("2021/01/05")})
    Dim result = datas.Select(Function(x) New With {Key _
        .ProductCode = x.ProductCode, Key _
        .Date = x._Date, Key _
        .Week = GetWeekNumber(x._Date), Key _
        .Year = x._Date.Year
    }).ToList()
    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("Year")
    dt.Columns.Add("Week")
    dt.Columns.Add("ProductCodeCount")

    For Each _year In result.Select(Function(x) New With {x.Date.Year, x.Week}).Distinct()
        Dim dr As DataRow = dt.NewRow()
        dr("Year") = _year.Year
        dr("Week") = _year.Week
        dr("ProductCodeCount") = result.Where(Function(x) x.Year = _year.Year AndAlso x.Week = _year.Week).Count()
        dt.Rows.Add(dr)
    Next

    gvDetails.DataSource = New DataView(dt).ToTable(True)
    gvDetails.DataBind()
End Sub

Public Function GetWeekNumber(ByVal time As DateTime) As Integer
    Dim day As DayOfWeek = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time)
    If day >= DayOfWeek.Monday AndAlso day <= DayOfWeek.Wednesday Then
        time = time.AddDays(3)
    End If

    Return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday)
End Function

Public Class [MyClass]
    Public Property ProductCode As String
    Public Property _Date As DateTime
End Class

Output