question

ZaytsevVasily-8124 avatar image
0 Votes"
ZaytsevVasily-8124 asked SQLBaby-8546 edited

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SQLBaby-8546 avatar image
0 Votes"
SQLBaby-8546 answered SQLBaby-8546 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.