-
SQL Baby 161 Reputation points
2021-06-28T14:03:20.517+00:00 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 30 additional answers
Sort by: Most helpful
Count dates in request

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