我想要一个C＃桌面应用程序的SQL查询来计算当月或每月的每日销售量 [英] I want a SQL query for C# desktop app to calculate daily sale of current month or group by month

问题描述

I have Table  Daily_Sale Sample is given 
<pre>     Date_Time             Sale      Cost     Profit     Margin

Friday, June 29, 2018	   33945     20000	  13945	    41.081
Monday, April 16, 2018	   63303     43000	  20303	    32.072
Saturday, April 14, 2018   104534    74000	  30534	    29.209
Sunday, April 15, 2018	   9800      70000	  28000	    28.571
Thursday, June 28, 2018	   27156     16000	  11156	    41.081
Tuesday, May 1, 2018	   17034     12000	  5034	    29.552
Wednesday, May 2, 2018	   19753     12000	  7752	    39.246

我想按月计算销售额。实际上我想在datagridview和当月图表中显示销售，成本利润和保证金。



请帮帮我



我的尝试：



我正在尝试

I want to calculate sale by month.Actually I want to show sale,cost profit and margin in datagridview and chart of current month.

Please help me

What I have tried:

I'm trying

cmd = new SqlCommand("Select * from Daily_Sale ", con);

    adapt = new SqlDataAdapter();
    adapt.SelectCommand = cmd;
    dt = new DataTable();
    adapt.Fill(dt);
    bsource = new BindingSource();
    bsource.DataSource = dt;
    dataGridView1.DataSource = bsource;

    chart1.Series["Sale"].XValueMember = "Total_Sale";
    chart1.Series["Sale"].YValueMembers = "Total_Sale";
    chart1.Series["Profit"].XValueMember = "Total_Profit";
    chart1.Series["Profit"].YValueMembers = "Total_sale";
    chart1.Series["Margin"].XValueMember = "Margin";
    chart1.Series["Margin"].YValueMembers = "Total_sale";
    chart1.DataSource = hJSportsDataSet8.Daily_Sale;
    chart1.DataBind();


    cmd = con.CreateCommand();
    cmd.CommandText = "Select * from Daily_Sale ";
    SqlDataReader reader;
    reader = cmd.ExecuteReader();
    while (reader.Read())
    {

        chart1.Series["Sale"].XValueMember = "Date_Time";
        chart1.Series["Sale"].YValueMembers = "Total_Sale";
        chart1.Series["Profit"].XValueMember = "Date_Time";
        chart1.Series["Profit"].YValueMembers = "Total_Profit";
        chart1.Series["Margin"].XValueMember = "Date_Time";
        chart1.Series["Margin"].YValueMembers = "Margin";
    }

但我确实这显示了完整的表数据。

But I'm Sure this show the entirely table data.

推荐答案

尝试：

Try:

SELECT  Date_Time = DATEADD(MONTH, DATEDIFF(MONTH, 0, Date_Time), 0), 
        SUM(Sale) AS Sales,
        SUM(Cost) AS Costs,
        SUM(Profit) AS Profits,
        SUM(Margin) AS Margins
FROM    Daily_Sale
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Date_Time), 0);

经过很多次尝试不同的查询我得到了它。这很简单

After a lot of trying different queries I got it . it's simple

cmd = new SqlCommand("Select * from Daily_Sale Where Month(Date_Time)=Month( 
getDate() ) ", con);

实际上我将SQL数据库中的Date_Time格式从varchar更改为DateTime，我认为最初的做法是将日期和时间用日期时间格式而不是字符或字符串保存到初学者避免进一步的问题。所以在插入Daily Sale之前我刚写了这段代码。

Actually I change my Date_Time format in SQL database from varchar to DateTime and I realize as a beginner that it's best practice to save Date and time in datetime format rather a character or string, to avoid from further problems. so before insert into Daily Sale I just wrote this code.

dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "yyyy/MM/dd";

cmd_dily_sale = new SqlCommand("insert into Daily_Sale(Date_Time,Total_Sale,Total_Cost,Total_Profit,Margin)values(@Date_Time,@Total_Sale,@Total_Cost,@Total_Profit,@Margin)", con);
                cmd_dily_sale.Parameters.AddWithValue("@Date_Time",dateTimePicker1.Text);
cmd_dily_sale.Parameters.AddWithValue("@Total_Sale", sale);
cmd_dily_sale.Parameters.AddWithValue("@Total_Cost", total_cost);
cmd_dily_sale.Parameters.AddWithValue("@Total_Profit", profit);
cmd_dily_sale.Parameters.AddWithValue("@Margin", margin);
cmd_dily_sale.ExecuteNonQuery();

这篇关于我想要一个C＃桌面应用程序的SQL查询来计算当月或每月的每日销售量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！