I have a table with person's id, department, start date, end date as shown below:

Source Table:

I want to break the rows in months between the startdate and enddate for each id each department as shown below:

Expected Output:

I tried with Row generator in snowflake but as we need to get the different months between two dates based on each id's and dept name, I couldn't achieve it. Please suggest for possible solution.

CodePudding user response：

Try this one:

WITH Source_Table AS (

    SELECT ID, Dept_Name, TO_DATE(Start_Date, 'dd/MM/yyyy') AS Start_Date, TO_DATE(End_Date, 'dd/MM/yyyy') AS End_Date
    FROM (VALUES 
    (8797627, 'Dept1', '29/09/2021', '15/10/2021'),
    (8797627, 'Dept2', '29/09/2021', '27/12/2021'),
    (5489321, 'Dept1', '01/02/2022', '15/04/2022'),
    (5489321, 'Dept3', '01/01/2021', '01/02/2022')) T(ID, Dept_Name, Start_Date, End_Date)

), Gen_Month AS (

    SELECT DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY 1)-1, TO_DATE('01/01/2000', 'dd/MM/yyyy')) AS Months
      FROM TABLE(GENERATOR(ROWCOUNT => 1200)) AS v
)
SELECT *
  FROM Source_Table AS st
  LEFT JOIN Gen_Month AS gm ON gm.Months BETWEEN DATE_TRUNC(MONTH, st.Start_Date) AND DATE_TRUNC(MONTH, End_Date)
 ORDER BY ID, Dept_Name, Months

Result:

CodePudding user response：

with data(id, dept_name, start_date, end_date) as (
    select * from values
    (1, 'a', '2021-09-21'::date, '2021-10-15'::date)
), range_cte as (
    select row_number()over(order by null)-1 as rn
    from table(generator(ROWCOUNT => 100))
)
select d.id
    ,d.dept_name
    ,dateadd(month, r.rn, date_trunc(month, d.start_date)) as months
from data as d
join range_cte as r
where months < end_date
order by 1,2;

gives: