Select Date if between any row's start and end date

Question

I'm creating a list of months using the method shown here: Months between two dates

Once I have those dates I want to keep only those where they are between any row's start and end date for a particular client.

So the table is:

client  start_date  end_date
1       2014-06-01  2016-02-29
1       2016-03-01  2016-12-31
1       2017-04-01  NULL

Where NULL represents still active without a future end_date set.

So what I would like to get is (I'm using EOMONTH for each month):

2014-06-30
2014-07-31
... ect ...
2016-11-30
2016-12-31
2017-04-30
2017-05-31
... ect ...

So the months between December 2016 and April 2017 aren't there. There could be any number of rows for each client. They may be without gaps and they may be with gaps, as in the case above.

score 0 · Accepted Answer · answered Dec 27 '18 at 18:14

So I feel a bit silly now! It's quite simple:

WITH dates AS (
SELECT  EOMONTH(DATEADD(MONTH, x.number, '2016-01-01')) calendar_month
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
    AND x.number <= DATEDIFF(MONTH, '2016-01-01', '2018-12-31'))

SELECT
    dates.calendar_month

FROM
    clients
    LEFT JOIN dates ON dates.calendar_month BETWEEN EOMONTH(clients.start_date) AND EOMONTH(clients.end_date) OR 
        (dates.calendar_month >= clients.start_date AND clients.end_date is NULL)

Select Date if between any row's start and end date

1 Answers1