Get sum of each day (C# and SQL server)

Question

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 3-Feb-18 3:15am

Grafid

Updated 4-Feb-18 0:10am

v2

Add a Solution

2 solutions

Solution 2

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

Posted 4-Feb-18 0:04am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2018-02-03T03:42:00

Solution 1

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

Posted 3-Feb-18 3:42am

OriginalGriff

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