Query to group by for last n number of days-CodePudding

i have a table of employees with empnum,name, age , date of join.

I need to find out count of records for the last 90 days , group by age. How can a write a query in TSQL.

CodePudding user response：

SELECT age , count(*)  
from table1 
where DATEADD(dd,-90,GETUTCDATE()) 
Group by age

CodePudding user response：

You can apply filter, followed by group by.

SELECT age, COUNT(*) as CountOfEmployees
FROM employees
WHERE dateOfJoin < CAST(DATEADD(dd,-90,GETDATE()) AS DATE)
group by age

CodePudding user response：

It would be incorrect to save AGE, usually, it is something like DateOfBirth and you calculate the age from there.

SELECT DATEDIFF(year, DateOfBirth, getdate()), COUNT(*) as CountOfEmployees
FROM employees
WHERE CAST(DATEADD(dd,-90,GETDATE()) AS DATE) < dateOfJoin 
group by DATEDIFF(year, DateOfBirth, getdate())