I have a log with a status, and a status start and end date

Status	Start	End
A	1/1/25	2/13/25
A	2/14/25	2/27/25
A	1/15/25	3/15/25
A	2/28/25	...
A	1/05/25	1/20/25

I have a log with a status, and a status start and end date

Status	Start	End
A	1/1/25	2/13/25
A	2/14/25	2/27/25
A	1/15/25	3/15/25
A	2/28/25	...
A	1/05/25	1/20/25

I wish to link it to a date date and return the count of items in status A that are active between the first and end of the month of the date table.

By this I mean something like this:

                     2/1/25                       2/28/25 
                       |-----------------------------|
                           Start ---------- End 
          Start-------------End
                                 Start --------------------
          Start--------------------------------------------

In this example, the status is "active" in a month when the start and end dates

1. wholly occur within the month
2. Starts before the 1st and ends in the month
3. Starts in the month and ends after the end of the month
4. Starts before the month and has no end date

The table returned would look like:

year	Month	Status	Count of Active
2025	02	A	4

etc.

Of course, using the date table you should see the counts for each year and month.

Thank you very much for your time and effort for considering this.

• t-sql

Share Improve this question Follow edited Mar 25 at 20:32 user1911400 asked Mar 25 at 20:17 user1911400user1911400 333 bronze badges 2

• Is the logic in the overlap tag helpful? – HABO Commented Mar 26 at 0:14
• Seems to me as simple as NOT ((status start and status end are both < start) || (status start and end are both > end)). Am I missing something? – StriplingWarrior Commented Mar 27 at 23:10

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can create a date dimension or calendar table which can contain year,all months and their start and end date.

In the example below I have created a temporary CTE date_calendar to hold those values to fetch the information of start and end date of a month.

You can then join the calendar CTE with your logs table based on the four condition

WITH 
date_calendar AS (
SELECT 
2025 AS year,1 AS month,'2025-01-01' AS month_start,'2025-01-31' AS month_end
UNION ALL
SELECT 2025, 2, '2025-02-01', '2025-02-28'
UNION ALL
SELECT 2025, 3, '2025-03-01', '2025-03-31'
)
SELECT 
d.year, d.month, l.status,COUNT(*) AS count_of_active
FROM date_calendar d
LEFT JOIN  logs l
ON l.Status = 'A'
AND (
(l.starts >= d.month_start AND l.ends <= d.month_end) --Entirely within the month
OR (l.starts < d.month_start AND l.ends BETWEEN d.month_start AND d.month_end) --starts before the month starts, ends in month
OR (l.starts BETWEEN d.month_start AND d.month_end AND (l.Ends IS NULL OR l.Ends > d.month_end)) -- starts in month, or no end or ends after the month
OR (l.starts < d.month_start AND (l.ends IS NULL OR l.ends > d.month_end)) --starts before, ends after the month
)
GROUP BY d.year, d.month, l.status
ORDER BY d.year, d.month;

Output

Fiddle Demo

| year | month | status | count_of_active |
|------|-------|--------|-----------------|
| 2025 | 1     | A      | 3               |
| 2025 | 2     | A      | 4               |
| 2025 | 3     | A      | 2               |