Консолидация записей фактов по меткам времени

Я нахожусь в базе данных Microsoft SQL Server. Я хотел бы объединить начальный и конечный периоды для сотрудников. Например, приведена следующая простая таблица:

create table dbo.foo (
employee_key bigint,
effective_start_date date,
effective_end_date date,
account_name varchar(100));


insert into foo (employee_key,
effective_start_date,
effective_end_date,
account_name)
values (1
    ,'2017-01-01'
    ,'2017-01-31'
    ,'Google')
,(1
    ,'2017-02-01'
    ,'2017-02-28'
    ,'Apple')
,(1
    ,'2017-03-01'
    ,'2017-03-31'
    ,'Google')
,(1
    ,'2017-04-01'
    ,'9999-12-31'
    ,'Google')

Сотрудник перемещал счета пару раз. Я хотел бы сохранить изменения в феврале и обратно. Однако я не хочу видеть 2 записи в конце. Вместо этого я хочу, чтобы срок пребывания в Google продолжался с 2017-03-01 по 9999-12-31.

Кстати, это один из примеров. То есть, "битые" записи не всегда находятся в конце срока пребывания сотрудника.

Ожидаемые результаты:

employee_key | effective_start_date | effective_end_date | account_name
1            | 2017-01-01           | 2017-01-31         | Google
1            | 2017-02-01           | 2017-02-28         | Apple
1            | 2017-03-01           | 9999-12-31         | Google

2 ответа

Решение

Вы можете использовать row_number и получить этот результат... Я использовал два подзапроса, но это можно решить и без этого:

;With Cte as (
select *, Bucket = sum(accn) over(partition by employee_key order by effective_start_date) 
    from (
        Select *,case when account_name <> lag(account_name) over(partition by employee_key order by effective_start_date) 
            then 1 else 0 end as AccN
            from foo ) A
    )
    Select top (1) with ties employee_key, Min(effective_start_date) over(partition by employee_key,Bucket) AS [effective_start_date],      
                Max(effective_end_date) over(partition by employee_key,Bucket) AS [effective_end_date], account_name
    from cte
    order by row_number() over(partition by employee_key, bucket order by effective_start_date)

Вывод, как показано ниже:

+--------------+----------------------+--------------------+--------------+
| employee_key | Effective_start_Date | Effective_End_date | account_name |
+--------------+----------------------+--------------------+--------------+
|            1 | 2017-01-01           | 2017-01-31         | Google       |
|            1 | 2017-02-01           | 2017-02-28         | Apple        |
|            1 | 2017-03-01           | 9999-12-31         | Google       |
+--------------+----------------------+--------------------+--------------+

демонстрация

Вы можете попробовать использовать этот SQL

-- Table to group by
SELECT Row_number() 
         OVER ( 
           ORDER BY employee_key, effective_start_date, effective_end_date) RN, 
       *, 
       Cast(NULL AS BIGINT) 
       GroupBy 
INTO   #tmp 
FROM   #foo 

-- Variables 
DECLARE @GroupBy BIGINT = 0 
DECLARE @lastEmployee_key BIGINT 
DECLARE @lastAccount_Name VARCHAR(100) 


--- Creating the data group 
UPDATE #tmp 
SET    @GroupBy = groupby = CASE 
                              WHEN employee_key = Isnull(@lastEmployee_key, -1) 
                                   AND account_name = Isnull(@lastAccount_Name, 
                                                      '') 
                                   THEN @GroupBy 
                              ELSE @GroupBy + 1 
                            END, 
       @lastEmployee_key = employee_key = employee_key, 
       @lastAccount_Name = account_name = account_name 


--- Output 
SELECT employee_key, 
       Min(effective_start_date) effective_start_date, 
       Max(effective_end_date)   effective_end_date, 
       account_name 
FROM   #tmp 
GROUP  BY groupby, 
          employee_key, 
          account_name 

Результат

employee_key | effective_start_date|effective_end_date | account_name
-------------| --------------------|------------------ | -------------
1            | 2017-01-01          |2017-01-31         | Google
1            | 2017-02-01          |2017-02-28         | Apple
1            | 2017-03-01          |9999-12-31         | Google
Другие вопросы по тегам