Scenario:
List Duplicates from UserLog by User but excluding the original record.
| User |
|---|
| ID | Name |
| 1 | Terry |
| 2 | Jerry |
| User Log |
|---|
| ID | User ID | Created Date |
| 1 | 1 | 01/21/2020 |
| 2 | 1 | 01/20/2020 |
| 3 | 1 | 01/19/2020 |
| 4 | 2 | 01/18/2020 |
| 5 | 2 | 01/17/2020 |
Query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| DECLARE @BeginDate DATETIME = '2019-01-10'
DECLARE @EndDate DATETIME = GETDATE()
SELECT ul.*
FROM UserLog ul
JOIN (SELECT UserLogID,
ROW_NUMBER()
OVER (
Partition BY u.UserID
ORDER BY u.CreatedDate ASC) rowId
FROM (SELECT UserID
FROM UserLog u
WHERE u.CreatedDate <= GETDATE()
GROUP BY UserID
HAVING COUNT(DISTINCT UserID) > 1)dups) u2
ON ul.UserID = u2.UserID
WHERE ul.CreatedDate BETWEEN @BeginDate AND @EndDate
AND u2.rowId <> 1
|
Output:
| User ID | Data | Created Date |
|---|
| 1 | entry 3 | 01/21/2020 |
| 1 | entry 2 | 01/20/2020 |
| 2 | entry 2 | 01/18/2020 |
No comments:
Post a Comment