有没有一种方法可以显示日期范围或date_from& date_to在以YYYYMM作为BM的新字段中 [英] Is there a way to show date range or date_from & date_to in a new field with YYYYMM as a BM

问题描述

我有一个合同表，如下所示：

I have a table of contracts as shown below:

CREATE TABLE test_data_c ( contractid,valid_from,valid_to, year_month, 
    sales_product_number, currency_tc, end_customer_no, contract_quantity ) AS
    SELECT 15939, 201801,  201812,'201801-201812', 'SP000095923', 'EUR', 400009, 17000 FROM DUAL UNION ALL
    SELECT 16000, 201806,  201809,'201806-201809', 'SP000095333', 'USD', 400010, 23000 FROM DUAL UNION ALL
    SELECT 17000, 201810,  201903,'201810-201903', 'SP000095999', 'EUR', 400050, 20000 FROM DUAL ;

我想获得结果，如示例所示，其中创建了一个带有YYYYMM的新字段以显示日期范围作为BM。

I would like to get the outcome as shown in the example where a new field with YYYYMM is created to show the date range as BM.

示例3。

推荐答案

一种方法是使用递归查询：

One way is using a Recursive Query:

with cte (BM, contractid,valid_from,valid_to, year_month) as
  ( select substr(year_month, 1, 6) as BM
       ,contractid,valid_from,valid_to, year_month
    from test_data_c t
    union all
    select to_char(add_months(to_date(BM, 'YYYYMM'), 1), 'YYYYMM')
       ,contractid,valid_from,valid_to, year_month
    from cte 
    where cte.BM < substr(year_month, 8, 6) 
  )
select * 
from cte
order by 2,1

请参见小提琴

这篇关于有没有一种方法可以显示日期范围或date_from& date_to在以YYYYMM作为BM的新字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！