asdsa, Sql Server - rextester

Language: Layout:

CREATE TABLE [record](
    [id][int] NOT NULL,
    [name][nvarchar](20) NOT NULL,
    [targetdate][date] NULL,
PRIMARY KEY CLUSTERED
    (
        [id] ASC
    ))
   
   
   INSERT INTO [record] VALUES (1,'Event 1','2017-09-10');
   INSERT INTO [record] VALUES (2,'Event 2','2017-10-27');
   INSERT INTO [record] VALUES (3,'Event 3','2017-10-25');
   INSERT INTO [record] VALUES (4,'Event 4','2017-11-01');
   INSERT INTO [record] VALUES (5,'Event 5','2017-11-05');
   INSERT INTO [record] VALUES (6,'Event 6','2017-11-11');
   INSERT INTO [record] VALUES (7,'Event 7','2017-12-20');
   INSERT INTO [record] VALUES (8,'Event 8','2017-12-20');
   INSERT INTO [record] VALUES (9,'Event 9','2018-11-5');
   INSERT INTO [record] VALUES (10,'Event 10','2018-11-5');
   INSERT INTO [record] VALUES (11,'Event 11','2018-11-5');
   INSERT INTO [record] VALUES (12,'Event 12','2018-11-5');

WITH cte AS (
     SELECT *,CASE WHEN DATEDIFF(DAY,GETDATE(),targetdate) <0   THEN 0
                   WHEN DATEDIFF(DAY,GETDATE(),targetdate) <=10 THEN 10
                   WHEN DATEDIFF(DAY,GETDATE(), targetdate)<=30 THEN 30
                   ELSE 31 END AS grp
     FROM [record]   
)
SELECT DISTINCT grp, COUNT(*) OVER(ORDER BY grp) AS running_count
FROM cte;

-- The end output would be: passed = 1, tenDays = 3, thirtyDays = 6. Ideally I would also be able to return a count of all those over 30 days as well
   -- the exact format returned isn't too critical, as long as the numbers are returned in a known order/format I can interpret them

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 12, absolute service time: 0,22 sec