Get count for each time row appears in the range between 2 dates

Question

I'am trying to calculate how many times a row "appears" a in the range between 2 dates and grouping them by the month.

So, let's say i have rows that look like this:

   Name    |  StartDate      | EndDate 
-----------|-----------------|------------
   Mathias | 2017-01-01      | 2017-04-01
   Lucas   | 2017-01-01      | 2017-04-01

i would like to get the output that shows how many records exists between the 2 dates in a query, so something like the following output:

   Count   |    Year         | Month 
-----------|-----------------|------------
   2       | 2017            | 1
   2       | 2017            | 2
   2       | 2017            | 3
   2       | 2017            | 4
   0       | 2017            | 5
   0       | 2017            | 6

what i've tried is:

SELECT COUNT(*) as COUNT, YEAR(StartDate) YEAR, MONTH(StartDate) MONTH
FROM NamesTable
 WHERE Start >= '2017-01-01 00:00:00'
 AND Slut <= '2017-06-01 00:00:00'
 group by YEAR(StartDate), MONTH(StartDate)

where this is giving me the expected output of:

   Count   |    Year         | Month 
-----------|-----------------|------------
   2       | 2017            | 1
   0       | 2017            | 2
   0       | 2017            | 3
   0       | 2017            | 4
   0       | 2017            | 5
   0       | 2017            | 6

Because of grouping by the "start date", how can i count rows in the month for every one it expands across?

Is there a StartDate for every month, so that a select distinct StatDate would give a complete list of the desired months? — Turo, Dec 18 '17 at 17:41
every record has a startdate and an enddate, but these can overlap as one record can expand more than 1 month. hope it makes sense — Mathias Rønnow Nørtoft, Dec 19 '17 at 08:05

Juan Carlos Oropeza · Accepted Answer · 2017-12-18T14:27:21.283

0

You need a table with the months range

Table allMonths

+---------+------------+------------+
| monthId | StartDate  |  EndDate   |
+---------+------------+------------+
|       1 | 2017-01-01 | 2017-01-02 |
|       2 | 2017-01-02 | 2017-01-03 |
|       3 | 2017-01-03 | 2017-01-04 |
|       4 | 2017-01-04 | 2017-01-05 |
|       5 | 2017-01-05 | 2017-01-06 |
|       6 | 2017-01-06 | 2017-01-07 |
|       7 | 2017-01-07 | 2017-01-08 |
|       8 | 2017-01-08 | 2017-01-09 |
|       9 | 2017-01-09 | 2017-01-10 |
|      10 | 2017-01-10 | 2017-01-11 |
|      11 | 2017-01-11 | 2017-01-12 |
|      12 | 2017-01-12 | 2018-01-01 |
+---------+------------+------------+

Then your query is:

SELECT am.startDate, COUNT(y.Name)
FROM allMonths am
LEFT JOIN yourTable y
  ON am.StartDate <= y.EndDate
 AND am.EndDate >= y.StartDate
GROUP BY am.startDate

NOTE: You need to check border cases. Maybe you need change >= to > or change EndDate to the last day of the month.

edited Dec 18 '17 at 14:27

answered Dec 18 '17 at 13:56

Juan Carlos Oropeza

47,252
12
78
118

Your solution might work, but i would love not to create a second table just for months, which like feb can vary, right? There must be a way to create a stored pro or view to make this work. – Mathias Rønnow Nørtoft Dec 18 '17 at 14:09
You can create a cte with a query similar to [**this**](https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function) so you create the dates on the fly. But create a table with months isnt expensive, 100 years only need 1200 rows – Juan Carlos Oropeza Dec 18 '17 at 14:15
You can use the previous query to get the `startDate` and [**EOMONTH**](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql) to get `endDate` – Juan Carlos Oropeza Dec 18 '17 at 14:23
I'm wondering you don't like my answer because have to create a table or because don't know how to do it? For the first as I said you can use a view or cte but I rather have a static table. If you dont know how to do it I already provide the link for it but can expand if need it. But you need that month data so you can separate the data. – Juan Carlos Oropeza Dec 18 '17 at 14:26
I switch to `LEFT JOIN` so you can get the count = 0 for months without data. That is why you need a list with all months so have a handle to count months without data. – Juan Carlos Oropeza Dec 18 '17 at 14:28

Mathias Rønnow Nørtoft · Answer 2 · 2017-12-21T08:49:51.893

So, what i ended up doing was something like Juan Carlos proposed, but instead of creating a table i made it up with CTE instead for a cleaner approach:

Declare @todate datetime, @fromdate datetime, @firstOfMonth datetime, @lastOfMonth datetime
Select 
@fromdate='2017-01-11', 
@todate='2017-12-21',
@firstOfMonth =  DATEADD(month, DATEDIFF(month, 0, @fromdate), 0), ----YEAR(@fromdate) + MONTH(@fromdate) + DAY(1),
@lastOfMonth = DATEADD(month, ((YEAR(@fromdate) - 1900) * 12) + MONTH(@fromdate), -1)

 ;with MonthTable (MonthId, StartOfMonth, EndOfMonth) as
 (
        SELECT MONTH(@firstOfMonth) as MonthId, @firstOfMonth as StartOfMonth, @lastOfMonth as EndOfMonth
        UNION ALL   
        SELECT MONTH(DATEADD(MONTH, 1, StartOfMonth)), DATEADD(MONTH, 1, StartOfMonth), DATEADD(MONTH, 1, EndOfMonth)
        FROM MonthTable
         WHERE StartOfMonth <= @todate
 )

 SELECT am.StartOfMonth, COUNT(y.Start) as count
FROM MonthTable am
left JOIN clientList y
    ON y.Start <= am.StartOfMonth
    AND y.End >= am.EndOfMonth
GROUP BY am.StartOfMonth

You need change `COUNT(*)` to something like `COUNT(y.start)` other wise you will get 1 when not result. — Juan Carlos Oropeza, Dec 20 '17 at 13:57

Get count for each time row appears in the range between 2 dates

2 Answers2