CODEWITHSUNDEEP
sqlsql-serversql-server-2012
N.A
N.A

Reputation: 45

Count of records between min date range and other date

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

Upvotes: 1

Views: 219

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use window functions:

select id, count(*)
from (select t.*, min(servicedt) over (partition by id) as min_sd
      from tbl t
     ) t
where servicedt <= dateadd(day, 14, min_sd)
group by id;

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

Another option is to use cross apply() to get the first ServiceDt for each id and use that in your where clause.

select id, count(*) as date_count
from t
  cross apply (
    select top 1
      i.ServiceDt
    from t i
    where i.Id = t.Id
    order by i.ServiceDt
    ) x
where t.ServiceDt <= dateadd(day,14,x.ServiceDt)
group by id

rextester demo: http://rextester.com/WXA46698

returns:

+----+------------+
| id | date_count |
+----+------------+
| 10 |          4 |
| 11 |          2 |
+----+------------+

Upvotes: 0

Related Questions