Click here to Skip to main content
15,917,062 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please HELP !!
I have an app that I created using excel, but I want to move it to a c# and SQL server.
The app is designed to input sales operation (DATE, Order Number, customer name, amount) liek this :
DAT        Num     NAME    AMOUNT
01/01/2018	1	JHON	$600.00
01/01/2018	2	JHON	$320.00
01/01/2018	3	FREDY	$20.00
02/01/2018	4	JHON	$32.00
02/01/2018	5	FREDY	$360.00
03/01/2018	6	MARK	$100.00
03/01/2018	7	MARK	$223.00


and I need to get the sum of operation for each day like this :
DAY	        AMOUNT	        NUMBER OF CUSTOMER
01/01/2018	$940.00         3
02/01/2018	$392.00         2
03/01/2018	$323.00         2


I'm not sure how to do it in c#, I hope someone can help me with this.
I already made my c# and linked it to SQL server and all data is being saved as a test. now i just need to get those filtered data for each day.
Thank you

What I have tried:

I tried a bunch of code (LINQ) but I didn't get it well. as i'm a c# noob, I can't tell if the code is right.
Update : I tried this SQL Server procedure
USE [SoutienEtudiant]
GO
/****** Object:  StoredProcedure [dbo].[getAllDaysBetweenTwoDate]    Script Date: 2/3/2018 1:29:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getAllDaysBetweenTwoDate]
(
@FromDate DATETIME,    
@ToDate DATETIME
)
AS
BEGIN
    
    DECLARE @TOTALCount INT
    SET @FromDate = DATEADD(DAY,-1,@FromDate)
    Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

    WITH d AS 
            (
              SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
                OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
              FROM sys.all_objects
            )
        SELECT AllDays From d
        
    RETURN 
END


and in the app :
dataGridView1.DataSource = etu.getAllDaysBetweenTwoDate(dateTimePicker1.Value, dateTimePicker2.Value);


and it populate the datagridview1 with the filtered dates correctly. I just need now to put beside each date the right amount for the opertion, and the number of sales.
Posted
Updated 4-Feb-18 0:10am
v2

Try:
SQL
SELECT Day, SUM(AMOUNT) AS Amount, COUNT(Amount) AS [Number of customer] 
FROM MyTable
GROUP BY Day
HAVING Day BETWEEN @FromDate AND @ToDate
 
Share this answer
 
Comments
Grafid 3-Feb-18 10:14am    
Thanks that worked. thank you so much.
I just have a concern, why this code doesn't include the first day of the date range.
I have data in : 31/01/2018 and 01/02/2018 and 02/02/2018 but it only return data from 01/02/2018 and 02/02/2018.
it does exclude the 1st day of the range which is 31/01/2018
In case you would like to know a linq solution:

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("DAT", typeof(DateTime)));
dt.Columns.Add(new DataColumn("Num", typeof(int)));
dt.Columns.Add(new DataColumn("NAME", typeof(string)));
dt.Columns.Add(new DataColumn("AMOUNT", typeof(double)));

dt.Rows.Add(new object[]{new DateTime(2018,1,1), 1, "JHON", 600.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,1), 2, "JHON", 320.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,1), 3, "FREDY", 20.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,2), 4, "JHON", 32.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,2), 5, "FREDY", 360.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,3), 6, "MARK", 100.00});
dt.Rows.Add(new object[]{new DateTime(2018,1,3), 7, "MARK", 223.00});


var result = dt.AsEnumerable()
	.GroupBy(x=>x.Field<DateTime>("DAT"))
	.Select(grp=>new
	{
		DAT = grp.Key.ToString("dd/MM/yyyy"),
		AMOUNT = grp.Sum(a=>a.Field<double>("AMOUNT")),
		NoOfCust = grp.Count()
	});


A result variable stores:
DAT        AMOUNT NoOfCust
01/01/2018 940    3 
02/01/2018 392    2 
03/01/2018 323    2 
 
Share this answer
 

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