在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屋！