sql - 在 Hive 或 SQL Server 中将日期拆分为多个日期范围

Question

我想将以下日期转换为不同的日期范围，这里 emp 从 2019-07-25 到 2099-02-14 属于钦奈位置，但在这两者之间，emp 在 2020-02-15 和 2020-02 之间从 DEL 工作-23。

所以我想将以上日期转换为以下日期范围

标签： sqlsql-serverhivehiveql

Answer 1

要处理多级嵌套，您可以取消透视数据并

with e as (
      -- unpivot the dates
      select employeeid, fromdate as dte,
             placename, placecode
      from t
      union all
      select employeeid, enddate, null, null
      from t
     ),
     e2 as (
     -- impute the intermediate placenames
     select e.*,
            max(placename) over (partition by employeeid, grp) as imputed_placename
     from (select e.*,
                  count(placename) over (partition by employeeid order by dte) as grp
           from e
          ) e
    )
select employeeid, fromdate,
       dateadd(day, -1, lead(fromdate) over (partition by employeeid order by dte)) as enddate,
       placename, placecode
from e1;