Click here to Skip to main content
15,391,277 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table with order details which contains a datetime column called orderdate. Now I want to display the count of specific materials ordered month wise as below.

item  nov 17  dec 17  jan 18  feb 18
item1 5       3       6       3
item2 6       2       7       2

but my condition is tht I have another table as below. count should be shown
in the above datagriview for the groups in the second table as shown below.

month_field from      to
Nov 17      01-Nov-17 30-Nov-17
Dec 17      01-Dec-17 31-Dec-17
Jan 18      01-Jan-18 31-Jan-18
Feb 18      01-Feb-18 28-Feb-18

What I have tried:

Tried couldn't find any solution. pls help
Posted
Updated 15-Apr-18 14:07pm
v2
Comments
CHill60 24-Mar-18 13:38pm
   
Is this a sql table, my sql tabke, a manual datagridview, what?
Your 2nd table is pointless, you can easily determine the start and end dates of a month
#realJSOP 24-Mar-18 14:03pm
   
It looks like the results of a pivoted table to me. Who in their right mind would set up such a schema except via a pivoted table?
Member 13101003 24-Mar-18 14:35pm
   
I want the data in the datagridview like this
item nov 17 dec 17 jan 18 feb 18
item1 5 3 6 3
item2 6 2 7 2
Member 13101003 24-Mar-18 14:37pm
   
the second table is necessary because I need groups... it can be changed as weekly or fortnightly...
Member 13101003 24-Mar-18 15:00pm
   
sql
kmoorevs 24-Mar-18 15:04pm
   
As John mentioned, the results you want are from a pivot function. You can use the grouping table to build the pivot query dynamically using c#. Just google sql pivot and fill in the spaces. Ideally, you would lose the dependency on a 'range table' that requires maintenance.
Member 13101003 24-Mar-18 15:38pm
   
grouping table?
Member 13101003 27-Mar-18 11:52am
   
can someone make a demo ?

I think I have a solution for you, even if it is an ugly one.

First I created som classes that you probably have, but I needed them to get my solution to work properly.
namespace SalesReportApp
{
    internal class Product
    {
        public string productName { get; set; }

        public Product(string productName)
        {
            this.productName = productName;
        }
    }
}


I also needed to have a class for ReportPeriod:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SalesReportApp
{
    public class ReportPeriod
    {
        public string month_field { get; set; }
        public DateTime from { get; set; }
        public DateTime to { get; set; }

        public ReportPeriod()
        {

        }

        public ReportPeriod(string month_field, DateTime from, DateTime to)
        {
            this.month_field = month_field;
            this.from = from;
            this.to = to;
        }
        public ReportPeriod(string month_field, string from, string to)
        {
            this.month_field = month_field;
            this.from = DateTime.Parse(from);
            this.to = DateTime.Parse(to);
        }
    }
}


The sales report holds the vital information we need.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SalesReportApp
{
    public class SalesReport
    {
        public string Item { get; set; }
        public string Month_field { get; set; }
        public int SalesCount { get; set; }

        public SalesReport()
        {

        }

        public SalesReport(string Item, string Month_Field, int SalesCount)
        {
            this.Item = Item;
            this.Month_field = Month_field;
            this.SalesCount = SalesCount;
        }

        public override string ToString()
        {
            return $"Item:{Item}, Month:{Month_field}, Sales:{SalesCount}" ;
        }
    }
}



And all the sales data we now have should bee cooked together in the final report:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SalesReportApp
{
    public class ReportDataRow
    {
        public string Product { get; set; }
        public int Month1 { get; set; }
        public int Month2 { get; set; }
        public int Month3 { get; set; }
        public int Month4 { get; set; }

        public ReportDataRow()
        {

        }

        public ReportDataRow(string product, int month1, int month2, int month3, int month4)
        {
            this.Product = product;
            this.Month1 = month1;
            this.Month2 = month2;
            this.Month3 = month3;
            this.Month4 = month4;
        }
    }
}


I use a little nasty trick the I normally never uses myself to just rename the column namnes to get them right... Naughty trick..... And this class i NOT one of my finest moments in life... but I'm tired and on my way to sleep now....

This is the code in the Windows Form ...

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

// I have a table with order details which contains a datetime column called
// orderdate.Now I want to display the count of specific materials ordered
// month wise as below.
// item nov 17  dec 17  jan 18  feb 18
// item1 5       3       6       3
// item2 6       2       7       2
// 
// but my condition is tht I have another table as below.count should be shown
// in the above datagriview for the groups in the second table as shown below.
// 
// month_field from      to
// Nov 17      01-Nov-17 30-Nov-17
// Dec 17      01-Dec-17 31-Dec-17
// Jan 18      01-Jan-18 31-Jan-18
// Feb 18      01-Feb-18 28-Feb-18


namespace SalesReportApp
{
    public partial class Form1 : Form
    {
        List<Product> products = new List<Product>();
        List<ReportPeriod> reportPeriods = new List<ReportPeriod>();
        List<SalesReport> salesReports = new List<SalesReport>();
        List<ReportDataRow> reportdataSource = new List<ReportDataRow>();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            // **************************
            // ***** ADING PRODUCTS *****
            // **************************

            products.Add(new Product(productName: "Item1"));
            products.Add(new Product(productName: "Item2"));


            // *********************************
            // ***** ADDING REPORT PERIODS *****
            // *********************************

            reportPeriods.Add(new ReportPeriod(month_field: "Nov 17", from: "2017-11-01", to: "2017-11-30"));
            reportPeriods.Add(new ReportPeriod(month_field: "Dec 17", from: "2017-12-01", to: "2017-12-31"));
            reportPeriods.Add(new ReportPeriod(month_field: "Jan 18", from: "2018-01-01", to: "2018-01-31"));
            reportPeriods.Add(new ReportPeriod(month_field: "Feb 18", from: "2018-02-01", to: "2018-02-28"));


            // ********************************
            // ***** ADDING SALES REPORTS *****
            // ********************************

            // Sales for Nov 17
            salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Nov 17", SalesCount: 5));
            salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Nov 17", SalesCount: 6));

            // Sales for Dec 17
            salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Dec 17", SalesCount: 3));
            salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Dec 17", SalesCount: 2));

            // Sales for Jan 18
            salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Jan 18", SalesCount: 6));
            salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Jan 18", SalesCount: 7));

            // Sales for Feb 18
            salesReports.Add(new SalesReport(Item: "Item1", Month_Field: "Feb 18", SalesCount: 3));
            salesReports.Add(new SalesReport(Item: "Item2", Month_Field: "Feb 18", SalesCount: 2));



            // Add rows to dataGridView
            reportdataSource.Clear();
            foreach (Product prod in products)
            {
                // Work on 1 Item at a time....
                List<SalesReport> salesReportSubList = new List<SalesReport>();
                foreach (var salesItem in salesReports.Where(i => i.Item == prod.productName))
                {
                    salesReportSubList.Add((SalesReport)salesItem);
                }
                

                // Fill cell values with fresh data....
                ReportDataRow reportDataRow = new ReportDataRow();

                // This is a very pad solution but it probably work anyway
                for (int columnIndex = 0; columnIndex < reportPeriods.Count; columnIndex++)
                {
                    // Get sales info for product
                    int sales = salesReportSubList[columnIndex].SalesCount;
                    if (columnIndex == 0) reportDataRow.Month1 = sales;
                    if (columnIndex == 1) reportDataRow.Month2 = sales;
                    if (columnIndex == 2) reportDataRow.Month3 = sales;
                    if (columnIndex == 3) reportDataRow.Month4 = sales;
                }
                // Add row to datasource
                reportDataRow.Product = prod.productName;
                reportdataSource.Add(reportDataRow);
            }

            // Data binding populates DataGridView, with columns and data rows (Tuples).
            dataGridView1.DataSource = reportdataSource;

            // Change Column headers after databinding is done
            for(int columnIndex=0; columnIndex< reportPeriods.Count; columnIndex++)
            {
                dataGridView1.Columns[columnIndex+1].HeaderText = reportPeriods[columnIndex].month_field;
            }


        }
    }
}
   
v3
Comments
Member 13101003 14-Apr-18 11:32am
   
can you select from the second table by sql query?
BillWoodruff 16-Apr-18 21:25pm
   
Think about whether you are here to help people learn to code, and improve their coding skills, or, if you are here to write code for people that may be useful, but leaves the OP unchallenged, perhaps dependent.
P Conny M Westh 17-Apr-18 6:20am
   
The original question had to little information to be solved easy, thats why I had to make many asumptions. And It was a big task to make it work in my demo/test environment. I dont like to provide untested code, so I had to bild a simple solution. I still working on improvements to the "pattern", It might just be less code and easier to follow, but I make no promise.
BillWoodruff 17-Apr-18 20:24pm
   
I can't stop you from playing Santa Claus.
P Conny M Westh 17-Apr-18 20:32pm
   
Ha, ha, I'm just learning myself, so this is just fun for myself....

Pivot tables are a very interesting area to learn about.... I was a bit tired when I posted the first solution, the code became "messy", i normally do better.... Sorry about that....
I made som updates to the form code to emphesize how to make selections with LINQ (this is NOT SQL, even if the syntax is similar).

I added some LINQ code to filter, I I put that code in the transfer of data fråm the "database"-objects to the "report"-objects.


I added the method
LoadDataFromDatabase()
that simulates the database request. But i real code you should really make the major selection by having a very tight SQL query. The information from the database that you dont need, should NEVER be fetched at all.

This is not optimized at all, but only a way of showing how some of the functionality works with LINQ and how to build a Pivot table out of live data, in a relatively simple way.

all data is also hard coded, but this is only to make this sample code simple to understand, for a novice developer. In real life you whould NEVER write code this way.

make sure you learn how to use Entity framework, for accessing the database, and retriving data from it. Entity Framework utilizes LINQ.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

// I have a table with order details which contains a datetime column called
// orderdate.Now I want to display the count of specific materials ordered
// month wise as below.
// item nov 17  dec 17  jan 18  feb 18
// item1 5       3       6       3
// item2 6       2       7       2
// 
// but my condition is tht I have another table as below.count should be shown
// in the above datagriview for the groups in the second table as shown below.
// 
// Month_field from      to
// Nov 17      01-Nov-17 30-Nov-17
// Dec 17      01-Dec-17 31-Dec-17
// Jan 18      01-Jan-18 31-Jan-18
// Feb 18      01-Feb-18 28-Feb-18


namespace SalesReportApp
{
    public partial class Form1 : Form
    {
        // This is the original data from the database, that should be 
        // filtered with the SQL query before you load it in theis 
        // reporting application, otherwise you load of completly useless 
        // info, this affects the performance emencly...
        private List<Product> databaseProducts;
        private List<ReportPeriod> databaseReportPeriods;
        private List<SalesReport> databaseSalesReports;

        // This is the active filtered data the you gonna display in your report
        private List<Product> products;
        private List<ReportPeriod> reportPeriods;
        private List<SalesReport> salesReports;
        private List<ReportDataRow> reportdataSource;

        public Form1()
        {
            InitializeComponent();
        }

        private void DefaultInitializers()
        {
            // This is the original data from the database, that should be 
            // filtered with the SQL query before you load it in theis 
            // reporting application, otherwise you load of completly useless 
            // info, this affects the performance emencly...
            databaseProducts = new List<Product>();
            databaseReportPeriods = new List<ReportPeriod>();
            databaseSalesReports = new List<SalesReport>();

            // This is the active filtered data the you gonna display in your report
            products = new List<Product>();
            reportPeriods = new List<ReportPeriod>();
            salesReports = new List<SalesReport>();
            reportdataSource = new List<ReportDataRow>();
        }



        private void LoadDataFromDatabase()
        {
            // **************************
            // ***** ADING PRODUCTS *****
            // **************************
            databaseProducts.Clear();
            databaseProducts.Add(new Product(productName: "Item1"));
            databaseProducts.Add(new Product(productName: "Item2"));
            databaseProducts.Add(new Product(productName: "Item3"));
            databaseProducts.Add(new Product(productName: "Item4"));


            // *********************************
            // ***** ADDING REPORT PERIODS *****
            // *********************************
            databaseReportPeriods.Clear();
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jul 17", from: "2017-07-01", to: "2017-07-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Aug 17", from: "2017-08-01", to: "2017-08-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Sep 17", from: "2017-09-01", to: "2017-09-30"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Oct 17", from: "2017-10-01", to: "2017-10-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Nov 17", from: "2017-11-01", to: "2017-11-30"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Dec 17", from: "2017-12-01", to: "2017-12-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jan 18", from: "2018-01-01", to: "2018-01-31"));
            databaseReportPeriods.Add(new ReportPeriod(Month_field: "Feb 18", from: "2018-02-01", to: "2018-02-28"));


            // ********************************
            // ***** ADDING SALES REPORTS *****
            // ********************************
            databaseSalesReports.Clear();

            // Sales for Jul 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jul 17", SalesCount: 5));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jul 17", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Jul 17", SalesCount: 9));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Jul 17", SalesCount: 12));

            // Sales for Aug 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Aug 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Aug 17", SalesCount: 2));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Aug 17", SalesCount: 13));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Aug 17", SalesCount: 14));

            // Sales for Sep 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Sep 17", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Sep 17", SalesCount: 7));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Sep 17", SalesCount: 15));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Sep 17", SalesCount: 16));

            // Sales for Oct 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Oct 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Oct 17", SalesCount: 2));
            databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Oct 17", SalesCount: 17));
            databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Oct 17", SalesCount: 18));



            // Sales for Nov 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Nov 17", SalesCount: 5));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Nov 17", SalesCount: 6));

            // Sales for Dec 17
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Dec 17", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Dec 17", SalesCount: 2));

            // Sales for Jan 18
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jan 18", SalesCount: 6));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jan 18", SalesCount: 7));

            // Sales for Feb 18
            databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Feb 18", SalesCount: 3));
            databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Feb 18", SalesCount: 2));
        }


        private void Form1_Load(object sender, EventArgs e)
        {
            // Initialize objects
            DefaultInitializers();

            // Load data from database....
            // Filter as much as possible at the SQL-level to maximize efficency
            LoadDataFromDatabase();

            // ******************************************************
            // ***** ADING UNFILTERED PRODUCTS TO ACTUAL REPORT *****
            // ******************************************************
            products.Clear();

            // Here we dont do any selection at all, we just swallow the entire list of products
            products.AddRange(databaseProducts);

            // ***********************************************************
            // ***** ADDING FILTERED REPORT PERIODS TO ACTUAL REPORT *****
            // ***********************************************************
            reportPeriods.Clear();

            // here we use LINQ to make a selection of input data
            reportPeriods.AddRange(databaseReportPeriods.Where(p => p.from>=DateTime.Parse("2017-11-01") && p.to <= DateTime.Parse("2018-02-28") ));

            // **********************************************************
            // ***** ADDING FILTERED SALES REPORTS TO ACTUAL REPORT *****
            // **********************************************************
            salesReports.Clear();

            // Here we filter manually, by looping throug the incoming data and select the individual records we want
            // This is just one possible method to make your selection of toples(rows) from the database
            // LINQ is very powerful, if you spend some time learning how it works.
            // If you use Entity Framework, you have the possibility to use the LINQ, syntax directly to the database.
            //salesReports.Add(databaseSalesReports.Where(r => r.Month_field == period.Month_field));
            foreach (var period in reportPeriods)
            {
                //var sr2 = databaseReportPeriods.Where(r => r.Month_field == period.Month_field);

                var sr3 = databaseSalesReports.Where(r => r.Month_field == period.Month_field);

                foreach (SalesReport sr in sr3)
                {
                    // SalesReport salesReport = new SalesReport(Item:sr.Item, Month_field:sr.Month_field, SalesCount:sr.SalesCount);
                    salesReports.Add(sr);
                }
            }


            // Add rows to dataGridView
            reportdataSource.Clear();
            foreach (Product prod in products)
            {
                // Work on 1 Item at a time....
                List<SalesReport> salesReportSubList = new List<SalesReport>();
                foreach (var salesItem in salesReports.Where(i => i.Item == prod.productName))
                {
                    salesReportSubList.Add((SalesReport)salesItem);
                }

                // Fill cell values with fresh data....
                ReportDataRow reportDataRow = new ReportDataRow();

                // This is a very pad solution but it probably work anyway
                for (int columnIndex = 0; columnIndex < reportPeriods.Count; columnIndex++)
                {
                    var sr = salesReportSubList.SingleOrDefault(r => r.Item == prod.productName && r.Month_field == reportPeriods[columnIndex].Month_field);
                    if (sr != null)
                    {
                        // Get sales info for product
                        // int sales = salesReportSubList[columnIndex].SalesCount;
                        if (columnIndex == 0) reportDataRow.Month1 = sr.SalesCount;
                        if (columnIndex == 1) reportDataRow.Month2 = sr.SalesCount;
                        if (columnIndex == 2) reportDataRow.Month3 = sr.SalesCount;
                        if (columnIndex == 3) reportDataRow.Month4 = sr.SalesCount;
                    }
                }
                // Add row to datasource
                reportDataRow.Product = prod.productName;
                reportdataSource.Add(reportDataRow);
            }

            // Data binding populates DataGridView, with columns and data rows (Tuples).

            // Without filtering
            dataGridView1.DataSource = reportdataSource;
            
            // With filtering through LINQ expression
            //dataGridView1.DataSource = reportdataSource.Where(r => r.Product == "Item1");

            // Change Column headers after databinding is done
            for (int columnIndex=0; columnIndex< reportPeriods.Count; columnIndex++)
            {
                dataGridView1.Columns[columnIndex+1].HeaderText = reportPeriods[columnIndex].Month_field;
            }


        }
    }
}
   
v3
Comments
BillWoodruff 17-Apr-18 20:18pm
   
When updating a code example, revise your first post, do not post a second "solution."

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900