在SQL Server中查询月度报告 [英] Query for Monthly Report in sql server
问题描述
我有一个包含以下字段的表.
1.id
2.标题
3.fileno
4.date_recieving
5.current_status
在current_status中,四个值被保存为open,closed,pending和inprogress.
我想在rdlc中做月度报告.
在这里我有一个下拉菜单,用户从下拉列表中选择年份.例如,用户选择2012.now我想要这样的报告.意味着月份一月份有多少个文件处于打开,关闭,待处理和进行中的状态.
开放封闭待处理进行中总计
1月2 0 5 6
2月4 2 1 7
三月
.
.
12月
请帮助我,并告诉我查询报告的方式.
在此先感谢.
i have a table which has following fields.
1.id
2.title
3.fileno
4.date_recieving
5.current_status
in current_status four values are saved open,closed,pending and inprogress.
i want to make monthly report in rdlc.
where i have a drop down from which user select year from dropdown list.e.g user select 2012.now i want report like this.mean how many files in month january are open,closed,pending and in progress.
Open Closed Pending InProgress Total
Jan 2 0 5 6
Feb 4 2 1 7
March
.
.
Dec
kindly help me,and tell me the query for report like this.
thanks in advance.
推荐答案
您可以使用以下查询根据状态获取文件总数:
选择
SUM(当``Open''时为case current_status,然后1 else 0 end)为``Open'',
SUM(关闭"时为case current_status,然后为"1 else 0 end")为关闭",
SUM(当待处理"时为case current_status,然后为1;否则0结束)为待处理",
SUM(当``InProgress''时为case current_status,然后1 else 0 end)为``InProgress'',
SUM(1)为总计"
来自#TEMP1
只需在其中添加按月分组的逻辑即可.
Hi,
You can get total no of files based on status using following query :
SELECT
SUM( case current_status when ''Open'' then 1 else 0 end ) as ''Open'',
SUM( case current_status when ''Closed'' then 1 else 0 end ) as ''Closed'',
SUM( case current_status when ''Pending'' then 1 else 0 end ) as ''Pending'',
SUM( case current_status when ''InProgress'' then 1 else 0 end ) as ''InProgress'',
SUM(1) as ''Total''
FROM #TEMP1
Just add logic of group by month into that.
选择sum(current_status)
从表
按current_status分组
拥有month(date_recieving)=''1''
select sum(current_status)
from table
group by current_status
having month(date_recieving)=''1''
Hi,
SELECT *
FROM
(
SELECT datename(month,date_recieving) As [Month], current_status, ID
FROM files
) t
PIVOT
(
COUNT(ID) FOR current_status IN ([Pending],[Closed])
) as pvt
这篇关于在SQL Server中查询月度报告的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!