如何总结每月支出 [英] How to sum up expenditure month wise

问题描述

朋友，表Employee的结构是EmpId Int32，Date DateTime和Expenditure十进制(18,2).在这里，我明智地存储了员工的支出日期.问题是我需要对每个雇员的支出每月进行明智的汇总，并以以下格式将其显示在诸如网格视图/详细信息"视图之类的控件中:

EmpId月TotalExpenditure

请帮帮我.
感谢

Friends, the table Employee''s structure is EmpId Int32, Date DateTime and Expenditure decimal(18,2). Here I store employees'' expenditure date wise. The problem is that I need to sum up the expenditure month wise for every employee and show it in control like Grid View/Details view in the following format:

EmpId Month TotalExpenditure

Please help me out.
Thanks

推荐答案

看看这个: MSDN: ASP.NET 2.0的GridView示例:在GridView中显示主数据/明细数据 [ ^ ]

您需要创建一个存储的proc来执行此操作.
在您需要获取月份的开始日期和结束日期之前.从前端使用代码.

创建proc prcTotal(@ImpId int，@ sDate datetime，@ eDate datetime)
在tbl_name
如
(
声明@total int，
声明@month varchar(20)，
从tbl_name中选择@total = sum(支出)，其中date_col = @ sDate在Date_col = @ eDate
之间 选择EmpId，``Month''= case
当当月的条件"然后是一月"时，
当每月的情况"然后"2月"时，
当每月的情况"然后"3月"时，
当每月的情况"然后"4月"时，
当月度条件"然后五月"时，
当每月的情况"然后"6月"时，
当每月的情况"然后"7月"时，
当每月的条件"然后八月"时，
当每月的情况"然后"9月"时，
当每月的情况"然后十月"时，
当每月的情况"然后"11月"时，
当月度条件"然后"12月"时，
``MonthExpenditure''= @ total，其中EmpId = @ EmpId和Date_col = @ sDate和Date_col = @ eDate
)

尝试这个.

you need to create a stored proc for doing this.
before you need to get starting date and end date of the month. from the front end using code.

create proc prcTotal(@ImpId int, @sDate datetime, @eDate datetime)
on tbl_name
as
(
Declare @total int,
Declare @month varchar(20),
Select @total = sum(Expenditure) from tbl_name where date_col=@sDate between Date_col=@eDate
Select EmpId, ''Month''= case
when ''Condition for month'' then ''January'',
when ''Condition for month'' then ''February'',
when ''Condition for month'' then ''March'',
when ''Condition for month'' then ''April'',
when ''Condition for month'' then ''May'',
when ''Condition for month'' then ''June'',
when ''Condition for month'' then ''July'',
when ''Condition for month'' then ''August'',
when ''Condition for month'' then ''September'',
when ''Condition for month'' then ''October'',
when ''Condition for month'' then ''November'',
when ''Condition for month'' then ''December'',
''MonthExpenditure''=@total where EmpId=@EmpId and Date_col=@sDate and Date_col=@eDate
)

try this.

查询应类似于以下内容..

The query should be similar to the following..

SELECT EmpID
       , DATENAME(Month, cast( Date as datetimeoffset)) as  Month
       , sum(Expenditure) as TotalExpenditure
      
FROM Employee
GROUP BY EmpID, DATENAME(Month, cast( Date as datetimeoffset))

这篇关于如何总结每月支出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！