Выбирайте пользователей по дате их присоединения и дате выхода

У меня есть следующая таблица:

Человек

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

Я хочу проверить только по дате их присоединения и / или выхода, являются ли они оплачиваемыми (= активными) или нет.

Это платные пользователи:

  1. Пользователь, у которого начальный месяц как минимум за месяц до даты выставления счета, без даты

  2. Пользователь, у которого начальный месяц по крайней мере за один месяц до billdate с оставшейся датой, месяц, равный billdate или позже, чем billdate

Месяц выставления счета = всегда предыдущий месяц.

Я использую следующий запрос:

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)
    )
)

Мои проблемы:

  • Пользователь Test4 все еще присутствует в моей таблице, если я установил дату счета 2018-11-01.
  • Пользовательский Test3 исчезает, если я установил для billdate значение 2019-01-01

Что не так с моим запросом и как его оптимизировать?

Примеры данных:

Список пользователей:

1 - Тест - 10.08.2018 - ПУСТО

2 - Test2 - 10.07.2018 - NULL

3 - Test3 - 10.07.2018 - 31.12.2018

4 - Test4 - 10.08.2018 - 10.09.2018

За расчетный период предыдущего месяца (= 8 / август) = @billdate 2018-09-10, это платные пользователи:

Test2 Test3

Однако, когда я изменяю период выставления счета на 10 октября, это платные пользователи:

Тест Test2 Test3


sql sql-server datetime algorithm
person user3127554    schedule 07.09.2018    source источник
comment
Можете ли вы реализовать @billdate в своих образцах данных?   -  person Kahn Kah    schedule 07.09.2018
comment
Я хотел бы сделать вам общее предложение, когда вы в следующий раз захотите получить здесь помощь: 1) покажите образцы данных, которые четко объясняют ваш вопрос и охватывают ВСЕ крайние случаи, и 2) не оставляйте тонны комментариев под всеми ответы. Если вы обнаружите, что делаете это, вините себя в том, что вы недостаточно хорошо объяснили свой вопрос.   -  person Tim Biegeleisen    schedule 07.09.2018
comment
@TimBiegeleisen большое спасибо за ваш совет, я напомню себе об этом в будущем   -  person user3127554    schedule 07.09.2018
comment
Если вы можете отредактировать мою демонстрацию, добавьте новые данные и затем скажите мне, где мой ответ все еще идет не так, я рад попробовать другое обновление.   -  person Tim Biegeleisen    schedule 07.09.2018
comment
Ваше демо идеально подходит для моего (психоделически заданного) вопроса! Спасибо еще раз!   -  person user3127554    schedule 07.09.2018

Ответы (2)


arrow_upward
3
arrow_downward

Если я правильно понимаю вашу логику, оплачиваемый пользователь - это тот, у которого начальный месяц по крайней мере за месяц до текущего месяца, а конечный месяц меньше или равен текущему месяцу.

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;

Эта логика выставления счетов, по-видимому, соответствует тому, что клиенты получают бесплатно первый месяц, но оплачиваются со второго по последний месяц.

Демо

person Tim Biegeleisen    schedule 07.09.2018
comment
Нет, оплачиваемый пользователь может быть следующим: 1 - Пользователь, чей начальный месяц по крайней мере за месяц до даты выставления счета, без оставшейся даты | 2 - Пользователь, начальный месяц которого по крайней мере за один месяц до billdate с датой left month, равной billdate или более поздней, чем billdate - person user3127554; 07.09.2018
comment
Я изменил его и добавил образцы данных - person user3127554; 07.09.2018
comment
@TimBiegeleisen Ваш запрос не будет работать для пользователей, которые присоединились в прошлом году и у которых нет date_left - person Kahn Kah; 07.09.2018
comment
@TimBiegeleisen в вашем демо, когда я меняю billdate на 2019-01-01, человек Test3 не оплачивается. Или это потому, что вы не проверяли, чтобы проверить предыдущий месяц? - person user3127554; 07.09.2018
comment
Итак, чтобы внести ясность, вы сейчас говорите, что клиенту выставляется счет за один месяц после месяца, в котором он ушел? - person Tim Biegeleisen; 07.09.2018
comment
Да, но для даты выставления счета следует проверить previous month. Это означает, что если billdate - 2019-01-01, он должен проверить до декабря 2018 года. - person user3127554; 07.09.2018
comment
Да, потому что в январе 2019 года мы проверяем плательщиков за декабрь 2018 года. - person user3127554; 07.09.2018
comment
Да!! но я не понял, что вы изменили, я подумал, что вы сделаете это, вычтя -1 месяц. Большое спасибо, прошу прощения за недоразумение, не могли бы вы просто отредактировать ответ, чтобы включить его в мой запрос? Мне все равно нужно использовать clausule, верно? - person user3127554; 07.09.2018
comment
Можно ли исправить это в моем запросе без использования CASE-операторов? поскольку я на самом деле использую это для получения только совпавших строк без добавления тестового столбца - person user3127554; 07.09.2018
comment
@TimBiegeleisen Я заметил одну «ошибку» на самом деле в вашей последней демонстрации, она все еще видит, что пользователи присоединились в предыдущем месяце выставления счетов как оплачиваемые (даже если они не должны этого делать). если дата выставления счета 2018-09-01, пользователь, присоединившийся 2018-08-30, должен быть помечен как non billable - person user3127554; 07.09.2018

arrow_upward
0
arrow_downward

используйте флаг make для сравнения месяца и года присоединения с месяцем и годом счета и создайте флаг для каждого пользователя, затем используйте подзапрос для фильтрации

  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'

По вашему запросу

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'
person Zaynul Abadin Tuhin    schedule 07.09.2018