Search code examples
sqlpivotvertica

Pivot a Date Range into multiple rows

I have a table T in this format:

ClientName StartMonth EndingMonth
X Dec 2018 Jan 2021

I want the output of my query to be:

ClientName MonthRange Year #
X Dec 2018-Nov 2019 1
X Dec 2019-Nov 2020 2
X Dec 2020-Nov 2021 3

Can someone help me what is the best way to tackle this problem?

Solution

  • Try this:

    WITH
indata(clientname,startmonth,endmonth) AS(
  SELECT 'x',DATE '2018-12-01', DATE '2021-01-01'
)
,
-- a series of at least 3 integers - no other way ...
y(y) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
)
SELECT 
  clientname
,  TO_CHAR(ADD_MONTHS(startmonth,(y-1)*12),'Mon-YYYY')
 ||'-'
 ||TO_CHAR(ADD_MONTHS(startmonth,(y-1)*12+11),'Mon-YYYY') AS monthrange
, y AS "year#"
FROM indata CROSS JOIN y
WHERE ADD_MONTHS(startmonth,(y-1)*12) <= endmonth
ORDER BY y;

clientname|monthrange       |year#
x         |Dec-2018-Nov-2019|    1
x         |Dec-2019-Nov-2020|    2
x         |Dec-2020-Nov-2021|    3