Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разрыв в датах  [new]
Mish_Mish
Member

Откуда:
Сообщений: 15
Доброго дня.

Доступ в "систему" предоставляется на определенный период с d_start по d_finish.
Как найти 1) дату и 2) последнюю дату, когда клиент возобновил пользование нашими услугами после разрыва.
Примечание: не считать разрыв период менее 30 дней (включительно).

Спасибо.

Пример:
declare @t table(d_start datetime, d_finish datetime, product nvarchar(100))

insert @t
select '01.01.2010', '31.12.2010', 'товар №1'
union all
select '01.06.2010', '30.06.2011', 'товар №2'
union all
select '20.07.2011', '31.12.2011', 'товар №3' -- разрыв в 20 дней, но это не считаем разрывом, т.к. это меньше 30 дней.
union all
select '15.02.2012', '28.02.2014', 'товар №4' -- разрыв №1, 15.02.2012 -дата, когда клиент возобновил пользование нашими услугами после разрыва.
union all
select '01.01.2014', '31.12.2014', 'товар №5'
union all
select '01.01.2016', '31.12.2016', 'товар №6' -- разрыв №2, 01.01.2016 -дата, когда клиент возобновил пользование нашими услугами после разрыва.
12 дек 16, 13:23    [19993030]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
aleks2
Guest
select * from aTable as t
  where not exists( select * from aTable as t1 where t1.ClientID = t.ClientID and t1.d_finish >= dateadd( day, -30, t.d_start  ) )
order by t.d_start desc


top(1) ужо сам нарисуй.
12 дек 16, 14:24    [19993489]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Mish_Mish
Доступ в "систему" предоставляется на определенный период с d_start по d_finish.
Как найти 1) дату и 2) последнюю дату, когда клиент возобновил пользование нашими услугами после разрыва.
Примечание: не считать разрыв период менее 30 дней (включительно).

1. Что такое "1) дату и 2) последнюю дату" - весьма непонятно, чем одно отличается от другого.
2. Диапазоны могут пересекаться? В рамках услуги, я так понимаю, нет. А в рамках клиента?
12 дек 16, 16:58    [19994474]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Mish_Mish
Member

Откуда:
Сообщений: 15
Руслан Дамирович,

1. Что такое "1) дату и 2) последнюю дату" - весьма непонятно, чем одно отличается от другого.
Отв: По сути, это одно и тоже. "2) последнюю дату" - это max дата из всех разрывов.

2. Диапазоны могут пересекаться? В рамках услуги, я так понимаю, нет. А в рамках клиента?
Отв: Диапазоны могут пересекаться в рамках клиента.
12 дек 16, 17:13    [19994521]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
Mish_Mish,

можно считать накопительный итог в днях от первой покупки и для поиска разрывов сравнивать предыдущее и текущее значения.
или самообъединение. Если есть дата покупки товара. Вы же как -то определили нумерацию товаров, т.е. можете их упорядочить.
12 дек 16, 17:38    [19994622]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
ТС, вот алгоритм.
h1, h2, h3 - получение перекрывающихся диапазонов дат.
h4 - получение того, что тебе нужно.

+ Просьба убрать от экранов беременных женщин, кормящих грудью матерей и впечатлительных программистов

IF OBJECT_ID( 'tempdb..#t' ) IS NOT NULL
  DROP TABLE #t
;
CREATE TABLE #t( 
  [client_id] INT,
  [d_start] DATE,
  [d_finish] DATE,
  [product] NVARCHAR(100) )
;
INSERT
INTO
  #t
VALUES
  ( 1, '20100101', '20101231', 'товар №1' ),
  ( 1, '20100601', '20110630', 'товар №2' ),
  ( 1, '20110720', '20111231', 'товар №3' ), -- разрыв в 20 дней, но это не считаем разрывом, т.к. это меньше 30 дней.
  ( 1, '20120215', '20140228', 'товар №4' ), -- разрыв №1, 15.02.2012 -дата, когда клиент возобновил пользование нашими услугами после разрыва.
  ( 1, '20140101', '20141231', 'товар №5' ),
  ( 1, '20160101', '20161231', 'товар №6' )  -- разрыв №2, 01.01.2016 -дата, когда клиент возобновил пользование нашими услугами после разрыва. 
;
WITH
h1 AS (
  SELECT
    [client_id],
    [is_end] = 0,
    [dt] = [d_start]
  FROM
    #t
  UNION ALL
  SELECT
    [client_id],
    [is_end] = 1,
    [dt] = [d_finish]
  FROM
    #t
),
h2 AS (
  SELECT
    *,
    [is_open] = SUM( CASE 
                  WHEN [is_end] = 0 THEN 1 
                  WHEN [is_end] = 1 THEN -1 
                END ) 
                OVER ( 
                  PARTITION BY
                    [client_id]
                  ORDER BY 
                    [dt] 
                  ROWS BETWEEN
                        UNBOUNDED PRECEDING
                    AND CURRENT ROW )
  FROM
    h1
),
h3 AS (
  SELECT
    [client_id],
    [dt_e] = [dt],
    [dt_s] = LAG( [dt] ) 
              OVER ( 
                PARTITION BY 
                  [client_id]
                ORDER BY 
                  [dt] ),
    [is_end]
  FROM
    h2
  WHERE
        [is_end] = 0 AND [is_open] = 1
    OR  [is_end] = 1 AND [is_open] = 0
),
h4 AS (
  SELECT
    [client_id],
    [dt_s],
    [dt_e],
    [dt_e_prev] = LAG( [dt_e] ) 
                  OVER (
                    PARTITION BY
                      [client_id]
                    ORDER BY
                      [dt_s] )
  FROM 
    h3
  WHERE
    [is_end] = 1
)
SELECT
  [client_id],
  [dt_e_prev],
  [dt_s],
  [dt_e]
FROM
  h4
WHERE
  DATEDIFF( DAY, [dt_e_prev], [dt_s] ) >= 30
;

Не откажусь от улучшенной версии алгоритма.
13 дек 16, 11:46    [19996538]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Mish_Mish
Member

Откуда:
Сообщений: 15
Руслан Дамирович,
Отлично. Спасибо большое!
13 дек 16, 12:40    [19996771]     Ответить | Цитировать Сообщить модератору
 Re: Разрыв в датах  [new]
Mish_Mish
Member

Откуда:
Сообщений: 15
aleks2, и Вам спасибо за помощь:)
13 дек 16, 12:46    [19996799]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить