I'm trying to get the count of records of users who appear between a certain date range, specifically the min(date) for each unique user and that min(date) + 14 days. I've checked this link
SQL HAVING BETWEEN a date range
but it's not what I'm looking for. Here's an example of what I'm working with and what I've tried to do
+----+------------+
| ID | ServiceDt |
+----+------------+
| 10 | 2017-03-02 |
| 10 | 2017-03-05 |
| 10 | 2017-03-06 |
| 10 | 2017-03-14 |
| 10 | 2017-03-27 |
| 11 | 2017-03-10 |
| 11 | 2017-03-19 |
| 11 | 2017-04-02 |
| 11 | 2017-04-14 |
| 11 | 2017-04-23 |
| .. | .. |
The query is:
SELECT ID, COUNT(ServiceDt) AS date_count
FROM (
SELECT ID, ServiceDt
FROM tbl
GROUP BY ID, ServiceDt
HAVING ServiceDt BETWEEN MIN(ServiceDt) AND DATEADD(day, +14, MIN(ServiceDt))
) AS R1
GROUP BY ID
When I do the above query I get the following result.
+----+------------+
| ID | date_count |
+----+------------+
| 10 | 5 |
| 11 | 5 |
| .. | .. |
I also tried using CONVERT(date, ...)
, but I get the same resulting table above. I want the result to be
+----+------------+
| ID | date_count |
+----+------------+
| 10 | 4 |
| 11 | 2 |
| .. | .. |
Can someone please guide me on what I can do to get my desired output, thanks
Copyright Notice:Content Author:「Nicholas」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/43947603/count-of-records-between-min-date-range-and-other-date