Select users based on their date joined and date left

Question

I have the following table:

Person

UserID Name Date_Joined Date_Left 
1      Test  2018-08-10 NULL
2      Test2 2018-07-10 NULL
3      Test3 2018-07-10 2018-12-31
4      Test4 2018-08-10 2018-09-10

I want to check by only their join and/or left date if they are billable(=active) or not.

These are billable users:

1. User whose start month is at least one month before billdate with no date left

2. User whose start month is at least one month before billdate with date left month equal to the billdate or later than the billdate

Month of billing = always the previous month.

I use the following query:

DECLARE @billdate AS DATE = '2018-09-01';
SELECT * 
FROM person
WHERE CompanyID = 1205 AND (
    (
        date_joined <= EOMONTH(@billdate, -1)
    )
    OR
    (
        date_left >  EOMONTH(@billdate, -1) AND
        date_left <= EOMONTH(@billdate)
    )
)

My problems:

• User Test4 is still present in my table if I set the billdate to 2018-11-01.
• User Test3 dissapears if i set billdate to 2019-01-01

What is wrong with my query and how can I optimize this?

Sample data:

User list:

1 - Test - 2018-08-10 - NULL

2 - Test2 - 2018-07-10 - NULL

3 - Test3 - 2018-07-10 - 2018-12-31

4 - Test4 - 2018-08-10 - 2018-09-10

For the bill period of the previous month (= 8 / August) = @billdate 2018-09-10, these are the billable users:

Test2 Test3

However, when I change the bill period to 10 / october, these are the billable users:

Test Test2 Test3

Answer 1

If I understand your logic correctly, a billable user is one whose start month is at least one month before the current month, and whose end month is less than or equal to the current month.

SELECT *,
    CASE WHEN @billdate >= DATEADD(d, 1, EOMONTH(date_joined)) AND
              (@billdate <= DATEADD(d, 1, EOMONTH(date_left)) OR date_left IS NULL)
         THEN 'billable'
         ELSE 'not billable' END AS status
FROM person;

This billing logic appears to be consistent with customers getting the first month free, but being billable from the second up to the final month.

Demo