SQL Server中的年度报告 [英] Yearly Report in SQL Server

问题描述

我有一个表格，其中有两个下拉菜单，分别是来自年份"和"
" 到年"，当用户选择从年"(例如2000)和到年"(例如2005)时，我想要这样的报告
我有一个名为FileRegister的表，其中包含字段id，title，fileno，daterecieving和当前状态，在当前状态下，我保存了四个值open，close，pending和Inprogress.我不知道.

I have a form in which i have a two dropdown one for "From Year" and other for
"To Year", when the user select the "from year" e.g 2000 and "To Year" e.g 2005, then i want report like this
i have a table named FileRegister with fields id,title,fileno,daterecieving and current status, in current status i save four values open,close,pending and Inprogress.i know that year can be found from daterecieving,but for multiple year like this i have no idea.

Year           open      close      pending         inprogress      total
2000           1          3          4                 5            13
2001
2002
2003
2004
2005

从此查询中，我可以选择不同值的当前状态.

From this query i can select the current status different values.

SELECT 
SUM( case current_status when 'Open' then 1 else 0 end ) as 'Open',
SUM( case current_status when 'Close' 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 'In Progress' then 1 else 0 end ) as 'InProgress',
SUM(1) as 'Total' from FileRegister

推荐答案

是的，我已经解决了我自己的问题，感谢那些花时间阅读我的文章的人

Yes i have solve this my self thanks for those who spend there time to read my post

SELECT YEAR (DATE_RECIEVING)  As [Yearnumber],
SUM( case current_status when 'Open' then 1 else 0 end ) as 'Open',
SUM( case current_status when 'Close' 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 'In Progress' then 1 else 0 end ) as 'InProgress',
SUM(1) as 'Total' from FileRegister 
where YEAR(DATE_RECIEVING)>='2001' and YEAR(DATE_RECIEVING)<='2014' 
group by YEAR(date_recieving)

在2001年和2014年，您可以像我在应用程序中一样替换存储过程的参数:)

in 2001 and 2014 you can replace your parameters of store procedure as i did in my application :)

这篇关于SQL Server中的年度报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！