SQL Query multiple users who started on the same date

Question

I've got a table something like this:

UserID  TeamID  StartedDate
36202   1213    27/11/2019 9:00
36203   1213    1/11/2019 10:30
36203   1207    24/11/2019 10:00
36205   1207    21/11/2019 9:15
36203   1213    1/11/2019 10:30
36214   1217    10/11/2019 10:00
36205   1207    24/11/2019 10:00
36202   1213    1/11/2019 10:30

How do I query a list of users who have the same "StartedDate" for each unique date?

Output should be look like this.

StartedDate         UserID's
24/11/2019 10:00    36203 & 36205
1/11/2019 10:30     36202 & 36203

I need to ignore time & focus on the date only.

What is your SQL Server version? – Zhorov Jan 07 '20 at 07:17 — Zhorov, Jan 07 '20 at 07:17

Nguyễn Văn Phong · Accepted Answer · 2020-01-08T00:06:46.687

2

You can achieve it by this way, Live demo here

SELECT DISTINCT C2.StartedDate, 
SUBSTRING(
(
   SELECT ', ' + CAST(C1.UserID AS VARCHAR(20))
   FROM TempTable C1
   WHERE C1.StartedDate = C2.StartedDate
  ORDER BY C1.StartedDate
  FOR XML PATH ('')
), 2, 1000) AS "UserList"
FROM TempTable C2

Output

StartedDate          UserList
1/11/2019 10:30      36203, 36203, 36202
10/11/2019 10:00     36214
21/11/2019 9:15      36205
24/11/2019 10:00     36203, 36205
27/11/2019 9:00      36202

edited Jan 08 '20 at 00:06

answered Jan 07 '20 at 07:17

Nguyễn Văn Phong

13,506
17
39
56

This worked for me - thank you! PS - is there any way to change the "(No column name)"? – Mo_Dlg Jan 07 '20 at 23:20
1

You just add more like this `AS "UserList"`, demo [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=898273cae3e51a7d1ed1fff1a480cacb). – Nguyễn Văn Phong Jan 08 '20 at 00:05
Did select answer as accepted.Im brand new here, dont think i've any upvote privileges here - Sorry! – Mo_Dlg Jan 08 '20 at 05:48

score 1 · Answer 2 · answered Jan 07 '20 at 07:26

Try this:

DECLARE @DataSource TABLE
(
    [UserID] INT
   ,[TeamID] INT
   ,[StartedDate] VARCHAR(24)
);

INSERT INTO @DataSource ([UserID], [TeamID], [StartedDate])
VALUES (36202, 1213,  '27/11/2019 9:00')
      ,(36203, 1213,  '1/11/2019 10:30')
      ,(36203, 1207,  '24/11/2019 10:00')
      ,(36205, 1207,  '21/11/2019 9:15')
      ,(36203, 1213,  '1/11/2019 10:30')
      ,(36214, 1217,  '10/11/2019 10:00')
      ,(36205, 1207,  '24/11/2019 10:00')
      ,(36202, 1213,  '1/11/2019 10:30');

-- SQL Server 2017+
SELECT [StartedDate]
      ,STRING_AGG([UserID], ',') AS [UserID's]
FROM
(
    SELECT DISTINCT [StartedDate]
                   ,[UserID]
    FROM @DataSource
) DS
GROUP BY [StartedDate];

-- SQL Server
WITH DataSoruce AS
(
    SELECT DISTINCT [StartedDate]
    FROM @DataSource
)
SELECT *
FROM DataSoruce A
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT DISTINCT ',' + CAST([UserID] AS VARCHAR(12))
            FROM @DataSource S
            WHERE A.[StartedDate] = S.[StartedDate]
            FOR XML PATH, TYPE 
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) R ([UserID's])

Reza Jenabi · Answer 3 · 2020-01-07T08:16:04.030

you can use the bellow code.

You need FOR XML and PATH

SELECT DISTINCT 
   CONVERT(DATE, StartedDate)  AS StartedDate,
   STUFF(
       (
           SELECT DISTINCT ' & ' + CAST(userid AS VARCHAR(MAX))
           FROM   #YourTable
           WHERE  (
                      CONVERT(DATE, StartedDate) = CONVERT(DATE, Results.StartedDate)
                  )
                  FOR XML PATH(''),TYPE
       ).value('(./text())[1]', 'VARCHAR(MAX)'),
       1,
       2,
       ''
   )                                AS 'UserID''s'
FROM   #YourTable                               Results

SQL Query multiple users who started on the same date

3 Answers3