How do we create a query to get current month and previous month data

I tried creating a table containing your data. Based on that table I created a query that provides you with the requested results:

 IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].[details]')
                  AND type IN ( N'U' ))
  DROP TABLE [dbo].[details]

CREATE TABLE details
  (
     [summary]     [NVARCHAR](60),
     [status]      [NVARCHAR](15),
     [createddate] [DATETIME],
     [closeddate]  [DATETIME]
  )

INSERT INTO [details]
            ([summary],
             [status],
             [createddate],
             [closeddate])
VALUES      ('changed battery',
             'closed',
             '2/1/2023',
             '2/1/2023'),
            ('top off fluid',
             'open',
             '2/10/2023',
             NULL ),
            ('radiator swap',
             'closed',
             '2/9/2023',
             '2/10/2023'),
            ('replaced headlight',
             'acknowledged',
             '1/23/2023',
             NULL),
            ('engine swap',
             'open',
             '1/2/2023',
             NULL),
            ('changed battery',
             'open',
             '2/20/2023',
             NULL),
            ('top off fluid',
             'closed',
             '1/11/2022',
             '1/1/2022'),
            ('radiator swap',
             'closed',
             '10/1/2022',
             '10/5/2022'),
            ('engine swap',
             'open',
             '1/1/2023',
             NULL),
            ('replaced headlight',
             'closed',
             '9/15/2022',
             '9/15/2022'),
            ('changed battery',
             'acknowledged',
             '8/1/2022',
             '8/2/2022'),
            ('top off fluid',
             'closed',
             '10/23/2022',
             '10/23/2022')

SELECT summary,
       Sum(closedcases)              AS [closed],
       Sum(previousmonthclosedcases) AS [previous_month_closed],
       Sum(opencases)                AS [open]
FROM  (SELECT summary,
              CASE
                WHEN status = 'closed' THEN 1
                ELSE 0
              END AS closedcases,
              CASE
                WHEN status = 'closed'
                     AND closeddate < Dateadd(month, Datediff(month, 0, Getdate(
                                                     )), 0)
                    THEN 1
                ELSE 0
              END AS previousmonthclosedcases,
              CASE
                WHEN status = 'open' THEN 1
                ELSE 0
              END AS opencases
       FROM   details) AS calculations
GROUP  BY summary  

Hi @lastcoder

If I understand correctly, you can try this query.

CREATE TABLE sampletable
  (summary varchar(50),
   status varchar(20),
   created_date datetime,
   closed_date datetime)
INSERT INTO sampletable VALUES  
('changed battery','closed','2/1/2023','2/1/2023'),
('top off fluid','open','2/10/2023',NULL ),
('radiator swap','closed','2/9/2023','2/10/2023'),
('replaced headlight','acknowledged','1/23/2023',NULL),
('engine swap','open','1/2/2023',NULL),
('changed battery','open','2/20/2023',NULL),
('top off fluid','closed','1/11/2022','1/1/2022'),
('radiator swap','closed','10/1/2022','10/5/2022'),
('engine swap','open','1/1/2023',NULL),
('replaced headlight','closed','9/15/2022','9/15/2022'),
('changed battery','acknowledged','8/1/2022','8/2/2022'),
('top off fluid','closed','10/23/2022','10/23/2022')

;with CTE as(
  select summary,status,
         case when datepart(mm,created_date) = datepart(mm,getdate())
		 and datepart(yy,created_date) = datepart(yy,getdate()) then 'y' else 'n' end as jud
  from sampletable where status in ('closed','open'))
select summary,sum(case when status = 'closed' and jud = 'y' then 1 else 0 end) as closed,
       sum(case when status = 'closed' and jud = 'n' then 1 else 0 end) as previous_month_closed,
	   sum(case when status = 'open' then 1 else 0 end) as 'open'
from CTE group by summary;

Best regards,

Percy Tang

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.