Skip to content
Advertisement

Count and group data by Year/month and by user

I have a dataset that looks like this:

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

I am using the following query to count the number of tickets closed per month:

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

I want to show the amount of tickets closed per month BY each user but I cannot get the INSERT INTO-Select statement include a set of rows for each users that outline the count by month. I am guessing i would need some sort of while loop that iterates over a temporary table that includes a list of distinct users to accomplish this. I know loops in MSSQL are not the best to implement. What is the best way to accomplish this?

Advertisement

Answer

You don’t need a loop, derive year and month and then group by them

  ;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

Also, I don’t think you need them for this, but in case I misunderstood your question and you want start and end dates in this you can back-derive them from year and month as follows

, DATEFROMPARTS (ClosedYear, ClosedMonth, 1) as firstDayOfMonth , DATEADD(month, 1, DATEFROMPARTS (ClosedYear, ClosedMonth, 1)) as firstDayNextMonth

And one more thought, you don’t need to put the results into a temporary table, but if you wanted to the syntax would be as follows

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

EDIT: In the comment you said you wanted zeros added back in so you can plot this, you can still do that with this structure by adding in a temporary table of USERS x DATES and COALESCE in the zeros over NULLS

;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

EDIT: Here it is with a pivot table as requested in comments

  ;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
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement