Count of records between min date range and other date
NickName:Nicholas Ask DateTime:2017-05-13T07:09:38

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

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

Answers
Gordon Linoff 2017-05-12T23:14:45

Use window functions:\n\nselect id, count(*)\nfrom (select t.*, min(servicedt) over (partition by id) as min_sd\n from tbl t\n ) t\nwhere servicedt <= dateadd(day, 14, min_sd)\ngroup by id;\n",


More about “Count of records between min date range and other date” related questions

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 HAV...

Show Detail

Count of active records between date range

I have two tables, like: First Table (Users) Id Name Active ------------------- 1 X 1 2 Y 1 3 Z 1 4 X1 0 and the second table (Records) Id UserId D...

Show Detail

Count date strings between a range of dates

I have a hive table (table_1). In that table, one of the columns is called 'date'. Values in that column are 'string' type and in the format 'yyyyMMdd', (ex: 20210102). I am trying to get the count...

Show Detail

Count by date range between min date and max date

I'm trying to return a count by date range between min date and max date for projects. My table contains: RecordID ProjectID StartDate EndDate 1254879 00025 2021-01-01 2021-02-28 1254248 0002

Show Detail

How to group records on basis of specific date range?

Need to group records on day basis in specific date range? I mean i have two dates : start_date and end date and between that date i need to group record on day basis. Ex: date range: 06-11-2019 an...

Show Detail

Query for active records between date range or most recent before date range

I need to find active records that fall between a range of date parameters from a table containing applications. First, I look for a record between the date range in a table called 'app_notes' and ...

Show Detail

SQL Query to count between range of minimum date and date in other column

temp |id|received |changed | |33|2019-02-18|2019-11-18| |33|2019-08-02|2019-11-18| |33|2019-12-27|2019-11-18| |18|2019-07-14|2019-10-18| |50|2019-03-20|2019-05-26| |50|2019-01-19|2019-05-26| The '

Show Detail

SQL HAVING BETWEEN a date range

I am trying to retrieve the count of records between a certain date range that is specific to the users min date and max date in another table. This is what I have so far, however it is excluding at

Show Detail

Select between date range and specific time range

Is there a way to Select records between dates and specific time range. For example all the records from 2013-11-01 to 2013-11-30 between hours 05:00 to 15:00. Here what i made until now. select

Show Detail

Find records IN BETWEEN Date Range

Please see attached image alt text http://img248.imageshack.us/img248/7743/datefrom.png I have a table which have FromDate and ToDate. FromDate is start of some event and ToDate is end of that ev...

Show Detail