I have a table as below
| activityName | UserID | deviceID | createdDate |
|------------------------------------------------------------|
| ON | 1 | adddsad |2020-01-09 00:02:59.477 |
| OFF | 1 | adddsad |2020-01-09 00:50:39.857 |
| ON | 2 | bdddsad |2020-01-09 00:51:11.480 |
| OFF | 2 | bdddsad |2020-01-09 00:51:19.450 |
when I use STRING_AGG
like this which is accurate and returns the desired result
SELECT STRING_AGG(activityName + ' - ' + CONVERT(varchar, createdDate), ' | ') AS tag,
deviceID,
UserID
FROM (SELECT tag,
deviceID,
UserID
FROM tbl_DailyLogMaster
WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE())
GROUP BY userID) a
GROUP BY UserID;
It will return like this
| tag | deviceID | UserID |
|------------------------------------------------------------------------------------|
| ON - 2020-01-09 00:02:59.477 | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 |
| ON - 2020-01-09 00:51:11.480 | OFF - 2020-01-09 00:51:19.450 | bdddsad | 2 |
On production I have SQL Server 2014 running and had to work on alternative for STRING_AGG
which is not supported on older version
here is alternative I created
SELECT deviceID,
UserID,
STUFF((SELECT activityName + ' - ' + CONVERT(varchar, createdDate)
FROM tbl_DailyLogMaster
WHERE userID = tbl_DailyLogMaster.UserID
AND CONVERT(date, createdDate) = CONVERT(date, GETDATE())
ORDER BY UserID
FOR XML PATH('')),1,1,'') AS tag
FROM tbl_DailyLogMaster
WHERE CONVERT(date, createdDate) = CONVERT(date, GETDATE())
GROUP BY UserID,
deviceID,
UserID,
createdDate,
activityName;
it returns like this
| tag | deviceID | UserID |
|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 |
| N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | adddsad | 1 |
| N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | bdddsad | 2 |
| N - Jan 9 2020 12:51AMOFF - Jan 9 2020 12:51AMON - Jan 9 2020 12:02AMOFF - Jan 9 2020 12:50AM | OFF - 2020-01-09 00:50:39.857 | bdddsad | 2 |
What I am doing wrong with second query?
See Question&Answers more detail:
os