按年/月和用户计数和分组数据

先生蛋糕

我有一个看起来像这样的数据集:

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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章