Select users based on their date joined and date left

user_3127554 0
Viewed 123

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

datetime sql-server sql algorithm
2 Answers

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

user_18632290

use make flag comparing join month and year with bill month and year and create a flag for each user then use sub-query for filter

  select * from t
(
 select *,case when (month(date_joined)=month(billdate) 
   and  year(date_joined)=year(billdate)) then 'N' else 'Y' end as flag_billable
  from user
) as t where t.flag_billable='Y'

For your query 

DECLARE @billdate AS DATE = '2018-09-01';
select * from 
(
    SELECT * ,case when (month(date_joined)=month(@billdate) 
           and  year(date_joined)=year(@billdate)) then 'N' else 'Y' end as  flag_billable
    FROM person
) t where t.flag_billable='Y'
user_68085830
Related