Pl / SQL并从开始和停止日期构建每月数据

Question

PL/SQL不是我的强项。 我在SQL方面不错，但是如果可能的话，我有一个挑战，我可以真正使用您的帮助。我正在使用SQL开发人员，如果有帮助的话。

我有一个表是来自另外两个表的连接，但可以说，它具有以下应用列：

FTE_NAME              (VARCHAR2)
PRIMARY_BILLALBE_ROLE (VARCHAR2)
INVOICABLE_ALLOCATION (NUMBER)
CONTRACTED_FTE        (NUMBER)
FTE_COUNTRY           (VARCHAR2)
BILLING_START_DATE    (DATE)
BILLING_END_DATE      (DATE)

以下是我正在尝试执行的操作的示例：
我实际上已经使用 VBA 和 excel 完成了此操作，它工作得很好，但现在数据在 Oracle 服务器上，是时候更新了。

示例行：

|   FTE_NAME | PRIMARY_BILLABLE_ROLE | INVOICEABLE_ALLOCATION | CONTRACTED_FTE | FTE_COUNTRY | BILLING_START_DATE | BILLING_END_DATE |
|------------|-----------------------|------------------------|----------------|-------------|--------------------|------------------|
| John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |     May, 01 2013 |
| John Smith |               Manager |                      1 |              1 |         USA |       May, 02 2013 |           (null) |

我需要做的是PL/SQL代码将构建一个月度表，并逐行包含或排除该月的行，因此从01-JAN-2013到05-MAY-2013，月度表现在可能看起来像这样，前面有一个MONTH列：

|           MONTHLY |   FTE_NAME | PRIMARY_BILLABLE_ROLE | INVOICEABLE_ALLOCATION | CONTRACTED_FTE | FTE_COUNTRY | BILLING_START_DATE | BILLING_END_DATE |
|-------------------|------------|-----------------------|------------------------|----------------|-------------|--------------------|------------------|
|  January, 01 2013 | John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |    May, 01 2013  |
| February, 01 2013 | John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |    May, 01 2013  |
|    March, 01 2013 | John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |    May, 01 2013  |
|    April, 01 2013 | John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |    May, 01 2013  |
|      May, 01 2013 | John Smith |     Associate Manager |                      1 |              1 |         USA |   January, 01 2013 |    May, 01 2013  |
|      May, 01 2013 | John Smith |               Manager |                      1 |              1 |         USA |       May, 02 2013 |           (null) |

MAY行都将

包含在01-MAY-2013行中，因为该经理在那几天仍然担任助理经理。 我使用开始日期和结束日期来计算多少天。

我需要帮助的重要部分是如何使用每月的第一天使用 MONTHLY 列构建表。 每天将有1000条线路和每栋建筑。 我会让此代码在将提供报表和仪表板的视图中运行。

我非常感谢您能提供的任何帮助。

大卫

Answer 1

您可以从头开始填充所需的日期，如以下示例查询所示：

with report_params as (
  -- just to introduce variables 
  select 
    2013 year_value,
    4    start_month,
    6    end_month
  from  dual
),
report_months as (
  -- list of first dates of all required months 
  select 
    add_months( -- add months to
        trunc( -- January, 1st of year from parameters
          to_date(
            (select year_value from report_params), 
            'yyyy'
          ),
          'yyyy'
        ), 
        (select start_month from report_params) + level - 2
    )
  from 
    dual 
  connect by 
    -- select number of rows (levels) from start_month to end_month
    level <= (select end_month - start_month + 1 from report_params)
)
select * from report_months;

另一种可能性是分析一个表并在最小值和最大值之间生成音调：

with bound_months as (
  select 
    min(trunc(billing_start_date,'mm')) as first_month,
    max(trunc(billing_start_date,'mm')) as last_month
  from 
    applicable_columns
),
report_months as (
  select 
    add_months(
      (select first_month from bound_months),
      level - 1
    )
      as first_date
  from 
    dual 
  connect by 
    -- select number of rows (levels) from start_month to end_month
    level <= (select months_between(last_month,first_month)+1 from bound_months)
)
select * from report_months;

之后，您可以将月份列表与适用的列联接到数据表/视图：

with bound_months as (
  select 
    min(trunc(billing_start_date,'mm')) as first_month,
    max(trunc(billing_start_date,'mm')) as last_month
  from 
    applicable_columns
),
report_months as (
  select 
    add_months(
      (select first_month from bound_months),
      level - 1
    )
      as first_date
  from 
    dual 
  connect by 
    -- select number of rows (levels) from start_month to end_month
    level <= (select months_between(last_month,first_month)+1 from bound_months)
)
select
  months.first_date, 
  data.*
from 
 report_months      months,
 applicable_columns data
where 
 data.billing_start_date < add_months(months.first_date,1) 
 and
 nvl(data.billing_end_date, months.first_date) >= months.first_date
order by
  first_date, fte_name, primary_billable_role

SQLFiddle test