SQL:查询以按月获取结果 [英] SQL : Query to get results by monthly
本文介绍了SQL:查询以按月获取结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我在下面有这个查询,它给了我批准人批准和拒绝的计数。
但是我无法获得每个月的计数批准和拒绝。我将数据从2011年10月1日提取至2014年12月31日,为期3个月。)b
你能帮我解决这个问题吗?
Hi guys,
I have this query below which gives me the Approved and denied counts by Approver.
But I am unable to get the count for each month Approved and denied. I am pulling the data for back 3 months from 10/1/2018 to 12/31/2018).
Could you please help me with this?
这是怎么回事我希望按月划分。
以下是查询:
This is how I want to be divided by month
Here is the query :
推荐答案
声明@status表(用户名varchar(30), Status varchar(60),StatusDate Datetime,Total int)
插入@status值('mo','Approved','10 / 1/2018',40),
('Mo','Approved','10 / 5/2018',30),
('Mo','拒绝','10 / 6/2018',30),
('Mo','Denied','10 / 15/2018',30),
('Mo','Approved','10 / 25/2018',30),
('Mo', 'Approved','10 / 28/2018',30),
('Mo','Approved','11 / 1/2018',1),
('Mo','Approved','11 / 10/2018', 1),
('Mo','Approved','11 / 11/2018',1),>
('Mo','Denied','11 / 12/2018',1),
('Mo','拒绝','11 / 20/2018',1),
('Mo','Approved','12/20/208',10),
('Mo','Denied','12 / 21/2018',10),
('Mo','Denied','12 / 22/2018',10),
(' Jo','Denied','12 / 22/2018',10),
('Jo','拒绝' ,'12 / 22/2018',10),
('Jo','Approved','12/1 / 2018',10)
$
选择*
来自
(选择用户名,状态,总计,  DateName(m,StatusDate)为StatusMonth
来自@status)为p
PIVOT
(StatusMonth的总和(总计)([10月] ],[11月],[12月]))
as pp
按用户名排序
declare @status table(username varchar(30), Status varchar(60), StatusDate Datetime, Total int)
insert into @status values ('mo', 'Approved', '10/1/2018', 40),
('Mo', 'Approved', '10/5/2018', 30),
('Mo', 'Denied', '10/6/2018', 30),
('Mo', 'Denied', '10/15/2018', 30),
('Mo', 'Approved', '10/25/2018', 30),
('Mo', 'Approved', '10/28/2018', 30),
('Mo', 'Approved', '11/1/2018', 1),
('Mo', 'Approved', '11/10/2018', 1),
('Mo', 'Approved', '11/11/2018', 1),
('Mo', 'Denied', '11/12/2018', 1),
('Mo', 'Denied', '11/20/2018', 1),
('Mo', 'Approved', '12/20/2018', 10),
('Mo', 'Denied', '12/21/2018', 10),
('Mo', 'Denied', '12/22/2018', 10),
('Jo', 'Denied', '12/22/2018', 10),
('Jo', 'Denied', '12/22/2018', 10),
('Jo', 'Approved', '12/1/2018', 10)
select *
from
(select UserName, status, Total, DateName(m, StatusDate) as StatusMonth
from @status) as p
PIVOT
(sum(total) for StatusMonth in ([October],[November],[December]))
as pp
order by username
这篇关于SQL:查询以按月获取结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文