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

chetan
 
on Mar 17, 2021 03:54 AM
Sample_163960.zip
3166 Views

The query groups the data by year and week but i want last 5 weeks data. And when new years starts it should consider week 1 when there is first full week of 7days.

I have written the following query. 

List<DefectPPM_Details> defectppm_details = new List<DefectPPM_Details>();
defectppm_details = (from d in _context.DefectEntries
                    join b in _context.Barcode_InitialPrinting_Details on d.ProductBarcode equals b.ProductBarcode
                    where b.assline == line && DbFunctions.TruncateTime(d.defectEntryOn) < DbFunctions.TruncateTime(dtFrom)
                    group d by new { week = Math.Floor((decimal)SqlFunctions.DatePart("wk", d.defectEntryOn)),d.defectEntryOn.Value.Year } into g
                    orderby g.Key descending
                    select new DefectPPM_Details
                    {
                        defectEntryOnyear = g.Key.Year,
                        week = g.Key.week,
                        ProductBarcode = g.Select(z => z.ProductBarcode).Count()                                   
                    }).ToList();

Sample data

Productcode  date

00001        2020/12/28

00002        2020/12/29

00003        2020/12/30

00004        2021/01/01

00005        2021/01/02

00006        2021/01/03

00007        2021/01/04

00008        2021/01/05

Output required is

Year    week    productcodecount

2020    53        6

2021    1          2

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Mar 18, 2021 08:18 AM
on Jan 03, 2022 11:25 AM

Hi chetan,

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

Note: For this sample i have used temporary DataTable. For more details refer How to create Temporary Table in ASP.Net using C# and VB.Net.

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

YearWeekProductCodeCount
2020 53 3
2021 53 3
2021 1 2