CODEWITHSUNDEEP
sqlsql-server
Kowalski
Kowalski

Reputation: 13

Count concurrent dates in user-input date range using SQL

The user will input a date range, and I want to output in SQL every date between and including that range in the number of concurrent uses of said equipment.

In this example, the user date range is 03/08/2016 to 03/09/2016, so you can see below I include anything on or between those dates (grouped by category, but I've simplified here by only using 'powerchair')

The table schema is as follows;

trans_date | trans_end_date | eq_category
17/03/2016 | 16/10/2016     | POWERCHAIR
08/08/2016 | 08/08/2016     | POWERCHAIR
12/08/2016 | 12/08/2016     | POWERCHAIR
17/08/2016 | 18/08/2016     | POWERCHAIR
22/08/2016 | 22/08/2016     | POWERCHAIR
26/08/2016 | 26/08/2016     | POWERCHAIR
02/09/2016 | 02/09/2016     | POWERCHAIR

And I would like to output;

date       | concurrent_use
03-08-2016 |    1
04-08-2016 |    1
05-08-2016 |    1
06-08-2016 |    1
07-08-2016 |    1
08-08-2016 |    2
09-08-2016 |    1
10-08-2016 |    1
11-08-2016 |    1
12-08-2016 |    2
13-08-2016 |    1
14-08-2016 |    1
15-08-2016 |    1
16-08-2016 |    1
17-08-2016 |    2
18-08-2016 |    2
19-08-2016 |    1
20-08-2016 |    1
21-08-2016 |    1
22-08-2016 |    2
23-08-2016 |    1
24-08-2016 |    1
25-08-2016 |    1
26-08-2016 |    2
27-08-2016 |    1
28-08-2016 |    1
29-08-2016 |    1
30-08-2016 |    1
31-08-2016 |    1
01-09-2016 |    1
02-09-2016 |    2
03-09-2016 |    1

Anything 1 or 0, I can then filter out as there mustn't have been any equipment out concurrently that day.

I don't think this is a gaps/islands problem, but I'm drawing a blank trying to get this in an SQL statement.

Upvotes: 1

Views: 328

Answers (2)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Try like below. You need to generate dates using recursive cte. Then we need to count the no of occurrences of each date falling in range. 

     ;WITH CTE
     AS (SELECT CONVERT(DATE, '2016-08-03', 103) DATE1
         UNION ALL
         SELECT Dateadd(DAY, 1, DATE1) AS DATE1
         FROM   CTE
         WHERE  Dateadd(DD, 1, DATE1) <= '2016-09-03')
SELECT C.DATE1,
       Count(1) OCCURENCES
FROM   CTE C
       JOIN #TABLE1 T
         ON C.DATE1 BETWEEN [TRANS_DATE] AN [TRANS_END_DATE]
GROUP  BY C.DATE1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270613

You need a set of numbers or dates. So, if you want everything in that range:

with d as (
      select cast('2016-08-03' as date) as d
      union all
      select dateadd(day, 1, d.d)
      from d
      where d < '2016-09-03'
     )
select d.d, count(s.trans_date)
from d left join
     schema s
     on d.d between s.trans_date and s.trans_date_end
group by d.d;

I'm not sure if both the start and end dates are included in the range.

Upvotes: 1

Related Questions