SQL Server中的每周报告 [英] Weekly report in sql server

问题描述

我有一个表格，其中我有两个下拉菜单，分别是月份和月份，当用户选择年份(例如2012年)和月份(例如1月)时，我想要这样的报告
我有一个名为FileRegister的表，具有字段id，title，fileno，daterecieving和当前状态，在当前状态下，我保存四个值open，close，pending和Inprogress.

打开关闭待处理的进度总计
第1 1 3 4 5 13周
第2周
第3周
第4周
第5周

I have a form in which i have a two dropdown one for yeaer and other for month, when the user select the year e.g 2012 and month e.g january, 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.

open close pending inprogress total
Week1 1 3 4 5 13
Week2
Week3
Week4
Week5

kindly help me.

推荐答案

您可以使用 ^ ]查询.

You can get the desired result by using a Pivot[^] query.

您可以尝试以下操作-

You try as the following -

Declare @year int
Declare @month int
Set @year=2012
Set @month=2

SELECT WeekNumber, [Open],[close],[pending],[inprogress]
FROM
(
Select DATEPART(DW,daterecieving  ) as WeekNumber, current_status,COunt(current_status) as StatusCount from FileRegister
Where DATEPART(YEAR,daterecieving )=@year AND DATEPART(M,daterecieving) =@month GROUP BY DATEPART(DW,daterecieving  ), current_status
) SourceTable
PIVOT
(
COunt(current_status)
FOR current_status IN
( [Open],[close],[pending],[inprogress] )) as OutputTable
Order by OutputTable.WeekNumber

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