Count dates in request

Question

Hello.
I have T-SQL request:
SELECT
sp.[tp_Title],
CAST(DATEADD(HOUR,3,MAX([dt])) AS date) as [dt]
FROM [PS_PROD].[dbo].[User_Activity] as ua
inner join [WSS_PWA].[pjrep].[MSP_EpmResource_UserView] as ps on ps.ResourceUID=ua.ProjectUserGUID
inner join [WSS_PWA].[dbo].[UserInfo] as sp on LOWER(sp.tp_Login)=LOWER(ps.ResourceNTAccount)
GROUP BY sp.[tp_Title]
order by [dt] desc
The result is:
user1 2021-06-28
user2 2021-06-28
user3 2021-06-27
user4 2021-06-26
user5 2021-06-26
user6 2021-06-26
How to modify request for count dates for result:
user1 2021-06-28 2
user2 2021-06-28 2
user3 2021-06-27 1
user4 2021-06-26 3
user5 2021-06-26 3
user6 2021-06-26 3

Answer 1

Try this:
--Create Test Table
DECLARE @TestTbale TABLE (
[tp_Title] varchar(50) NULL,
[dt] Date NULL)

-- Test  Data  
  
INSERT INTO @TestTbale  
([tp_Title], [dt])  
Select 'user1', cast('2021-06-28' as date) union all  
Select 'user2', cast('2021-06-28' as date) union all  
Select 'user3' ,cast('2021-06-27' as date) union all  
Select 'user4', cast('2021-06-26' as date) union all  
Select 'user5', cast('2021-06-26' as date) union all  
Select 'user6', cast('2021-06-26' as date)  
  
;WITH TitleDatesCnt_CTE as(  
 select * from @TestTbale -- replace this select with your query  
 /*  
 SELECT  
 sp.[tp_Title],  
 CAST(DATEADD(HOUR,3,MAX([dt])) AS date) as [dt]  
 FROM [PS_PROD].[dbo].[User_Activity] as ua  
 inner join [WSS_PWA].[pjrep].[MSP_EpmResource_UserView] as ps on ps.ResourceUID=ua.ProjectUserGUID  
 inner join [WSS_PWA].[dbo].[UserInfo] as sp on LOWER(sp.tp_Login)=LOWER(ps.ResourceNTAccount)  
 GROUP BY sp.[tp_Title]  
 --order by [dt] desc  
 */  
  )  
Select [tp_Title], [dt], COUNT([dt]) OVER(PARTITION BY [dt]) as total_Dates   
FROM TitleDatesCnt_CTE   
order by [tp_Title]  

here is the result:
tp_Title dt total_Dates
user1 2021-06-28 2
user2 2021-06-28 2
user3 2021-06-27 1
user4 2021-06-26 3
user5 2021-06-26 3
user6 2021-06-26 3