我有一个看起来像这样的数据集:
GO
CREATE TABLE [dbo].[taskDB](
[ticket] [varchar](50) NULL,
[created] [date] NULL,
[closed] [date] NULL,
[rating] [varchar](50) NULL
[user] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023345', CAST(N'2019-09-01' AS Date), CAST(N'2020-01-17' AS Date), N'Low', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023346', CAST(N'2019-08-01' AS Date), CAST(N'2019-08-03' AS Date), N'Critical', N'Tom')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023347', CAST(N'2019-09-01' AS Date), CAST(N'2019-09-20' AS Date), N'Critical', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023348', CAST(N'2019-08-01' AS Date), CAST(N'2020-08-06' AS Date), N'Critical', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023349', CAST(N'2020-08-01' AS Date), CAST(N'2020-08-05' AS Date), N'Medium', N'Tom')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023350', CAST(N'2019-08-01' AS Date), CAST(N'2019-08-05' AS Date), N'Medium', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023351', CAST(N'2019-12-22' AS Date), CAST(N'' AS Date), N'High', N'Tom')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023352', CAST(N'2019-11-07' AS Date), CAST(N'2020-08-05' AS Date), N'Medium', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023353', CAST(N'2020-08-02' AS Date), CAST(N'' AS Date), N'Low', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023354', CAST(N'2019-08-02' AS Date), CAST(N'2019-08-05' AS Date), N'Medium'N, 'Tom')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023355', CAST(N'2019-010-02' AS Date), CAST(N'' AS Date), N'Low', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023356', CAST(N'2019-08-02' AS Date), CAST(N'2019-08-05' AS Date), N'Critical', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023357', CAST(N'2019-08-06' AS Date), CAST(N'2020-07-05' AS Date), N'Critical', N'Tom')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023358', CAST(N'2019-10-04' AS Date), CAST(N'' AS Date), N'Low', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023359', CAST(N'2019-12-02' AS Date), CAST(N'2020-02-25' AS Date), N'High', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023360', CAST(N'2019-08-05' AS Date), CAST(N'2019-08-05' AS Date), N'Medium', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023361', CAST(N'2020-08-02' AS Date), CAST(N'' AS Date), N'High', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023362', CAST(N'2019-09-02' AS Date), CAST(N'2019-10-06' AS Date), N'Critical', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023363', CAST(N'2019-10-03' AS Date), CAST(N'2019-11-08' AS Date), N'High', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023365', CAST(N'2019-10-03' AS Date), CAST(N'2019-12-08' AS Date), N'N/A', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023364', CAST(N'2019-11-03' AS Date), CAST(N'2019-11-05' AS Date), N'High', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023366', CAST(N'2020-06-03' AS Date), CAST(N'' AS Date), N'High', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023368', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'High', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023367', CAST(N'2019-11-03' AS Date), CAST(N'' AS Date), N'N/A', N'Pete')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023371', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'N/A', N'John')
GO
INSERT [dbo].[taskDB] ([ticket], [created], [closed], [rating], [user]) VALUES (N'023370', CAST(N'2019-08-03' AS Date), CAST(N'2019-08-05' AS Date), N'Critical', N'Pete')
GO
我正在使用以下查询来计算每月关闭的门票数量:
declare @FromDate datetime,
@ToDate datetime;
SET @FromDate = (Select min(created) From [dbo].[taskDB]);
SET @ToDate = (Select max(created) From [dbo].[taskDB]);
declare @openTicketsByMonth table (firstDayOfMonth datetime, firstDayNextMonth datetime, year int, month int, count int)
Insert into @openTicketsByMonth(firstDayOfMonth, firstDayNextMonth, year, month)
Select top (datediff(month, @FromDate, @ToDate) + 1)
dateadd(month, number, @FromDate),
dateadd(month, number + 1, @FromDate),
year(dateadd(month, number, @FromDate)),
month(dateadd(month, number, @FromDate))
from [master].dbo.spt_values
where [type] = N'P' order by number;
update R
Set R.count = (Select count(1) from [dbo].[taskDB] where created < R.firstDayNextMonth and (closed <= R.firstDayNextMonth and closed >= R.firstDayOfMonth)),
From @openTicketsByMonth R
select year,
month,
count
from @openTicketsByMonth
我想显示每个用户每月关闭的凭单数量,但是我无法获得INSERT INTO-Select语句,其中包含每个用户的行集,这些行概述了按月计数。我猜我将需要某种while循环,该循环在一个临时表上进行迭代,该临时表包括一个不同用户的列表来完成此操作。我知道MSSQL中的循环不是最佳实现。做到这一点的最佳方法是什么?
您不需要循环,得出年和月,然后按它们分组
;WITH cteMonth as (
SELECT ticket,created,closed,rating,[user] as UserName
, YEAR(closed) as ClosedYear
, MONTH(closed) as ClosedMonth
FROM dbo.taskDB
)SELECT COUNT(ticket) as ClosedCount, UserName, ClosedYear, ClosedMonth
FROM cteMonth
GROUP BY ClosedYear, ClosedMonth, UserName
ORDER BY ClosedYear, ClosedMonth, UserName
另外,我认为您不需要它们,但是如果我误解了您的问题,并且您想要开始和结束日期,可以按如下所示从年份和月份推导它们
,DATEFROMPARTS(ClosedYear,ClosedMonth,1)作为firstDayOfMonth,DATEADD(month,1,DATEFROMPARTS(ClosedYear,ClosedMonth,1))作为firstDayNextMonth
再想一想,您不需要将结果放入临时表中,但是如果想要的话,语法如下
declare @ClosedTicketsByUser table (Username nvarchar(50), firstDayOfMonth datetime, firstDayNextMonth datetime, ClosedYear int, ClosedMonth int, ClosedCount int)
;WITH cteMonth as (
SELECT ticket,created,closed,rating,[user] as UserName
, YEAR(closed) as ClosedYear
, MONTH(closed) as ClosedMonth
FROM dbo.taskDB
)
INSERT INTO @ClosedTicketsByUser(ClosedCount, UserName, ClosedYear, ClosedMonth, firstDayOfMonth, firstDayNextMonth)
SELECT COUNT(ticket) as ClosedCount, UserName, ClosedYear, ClosedMonth
, DATEFROMPARTS (ClosedYear, ClosedMonth, 1) as firstDayOfMonth
, DATEADD(month, 1, DATEFROMPARTS (ClosedYear, ClosedMonth, 1)) as firstDayNextMonth
FROM cteMonth
GROUP BY ClosedYear, ClosedMonth, UserName
ORDER BY ClosedYear, ClosedMonth, UserName
SELECT * FROM @ClosedTicketsByUser
编辑:在您说过的评论中,您希望添加零,以便可以进行绘制,您仍然可以通过在NULL零中添加一个USERS x DATES和COALESCE临时表来使用此结构
;WITH cteTickets as (
SELECT ticket,created,closed,rating,[user] as UserName
, YEAR(closed) as ClosedYear
, MONTH(closed) as ClosedMonth
FROM dbo.taskDB
), cteUsers as (SELECT DISTINCT UserName FROM cteTickets
), cteDates as (SELECT DISTINCT ClosedYear, ClosedMonth
, DATEFROMPARTS (ClosedYear, ClosedMonth, 1) as firstDayOfMonth
, DATEADD(month, 1, DATEFROMPARTS (ClosedYear, ClosedMonth, 1)) as firstDayNextMonth
FROM cteTickets
), cteCount as (SELECT COUNT(ticket) as ClosedCount, UserName, ClosedYear, ClosedMonth
FROM cteTickets GROUP BY ClosedYear, ClosedMonth, UserName
) SELECT U.*, D.*, COALESCE(C.ClosedCount, 0) as ClosedCount
FROM cteUsers as U CROSS JOIN cteDates as D
LEFT OUTER JOIN cteCount as C ON C.UserName = U.UserName AND C.ClosedYear = D.ClosedYear AND C.ClosedMonth = D.ClosedMonth
ORDER BY firstDayOfMonth, UserName
编辑:在这里它是与注释中请求的数据透视表
;WITH cteTickets as (
SELECT ticket,created,closed,rating,[user] as UserName
, YEAR(closed) as ClosedYear
, MONTH(closed) as ClosedMonth
FROM dbo.taskDB
), cteUsers as (SELECT DISTINCT UserName FROM cteTickets
), cteDates as (SELECT DISTINCT ClosedYear, ClosedMonth
, DATEFROMPARTS (ClosedYear, ClosedMonth, 1) as firstDayOfMonth
, DATEADD(month, 1, DATEFROMPARTS (ClosedYear, ClosedMonth, 1)) as firstDayNextMonth
FROM cteTickets
), cteCount as (SELECT COUNT(ticket) as ClosedCount, UserName, ClosedYear, ClosedMonth
FROM cteTickets GROUP BY ClosedYear, ClosedMonth, UserName
), cteFinal as ( SELECT U.*, D.*, COALESCE(C.ClosedCount, 0) as ClosedCount
FROM cteUsers as U CROSS JOIN cteDates as D
LEFT OUTER JOIN cteCount as C ON C.UserName = U.UserName AND C.ClosedYear = D.ClosedYear AND C.ClosedMonth = D.ClosedMonth
)
SELECT firstDayOfMonth, [John], [Pete], [Tom] FROM (SELECT * FROM cteFinal) as F
PIVOT (MIN(ClosedCount) FOR UserName IN ([John], [Pete], [Tom])) as P
ORDER BY firstDayOfMonth
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句