I have the data in the sql table in quarterly format. I need to be able to split it into monthly with value split evenly ([value/3) in to each month. Can you please assist on how to achieve this using SQL? Thank you.

CodePudding user response：

In Oracle can you use this script:

with mytable  as (
                  select to_date('2022-01-01', 'YYYY-MM-DD') as startX, to_date('2022-04-01', 'YYYY-MM-DD') as endX, 25629  as valueX from dual union
                  select to_date('2022-04-01', 'YYYY-MM-DD') as startX, to_date('2022-07-01', 'YYYY-MM-DD') as endX, 993621 as valueX from dual union
                  select to_date('2022-07-01', 'YYYY-MM-DD') as startX, to_date('2022-10-01', 'YYYY-MM-DD') as endX,     21 as valueX from dual union
                  select to_date('2022-10-01', 'YYYY-MM-DD') as startX, to_date('2023-01-01', 'YYYY-MM-DD') as endX,   7777 as valueX from dual 
                 ),
     mymonths as (
                  select '01' as month_n from dual union
                  select '02' as month_n from dual union
                  select '03' as month_n from dual union
                  select '04' as month_n from dual union
                  select '05' as month_n from dual union
                  select '06' as month_n from dual union
                  select '07' as month_n from dual union
                  select '08' as month_n from dual union
                  select '09' as month_n from dual union
                  select '10' as month_n from dual union
                  select '11' as month_n from dual union
                  select '12' as month_n from dual
                )
     select month_n, startX, valueX/3
       from mytable, mymonths
      where month_n between to_char(startX, 'MM') and to_char(endX-1, 'MM'); 

MONTHS_N STARTX       VALUEX/3
-------- ---------- ----------
01       01/01/2022       8543
02       01/01/2022       8543
03       01/01/2022       8543
04       01/04/2022     331207
05       01/04/2022     331207
06       01/04/2022     331207
07       01/07/2022          7
08       01/07/2022          7
09       01/07/2022          7
10       01/10/2022 2592,33333
11       01/10/2022 2592,33333
12       01/10/2022 2592,33333

Thank you.

CodePudding user response：

Assuming you can figure out how to generate monthly dates, which is RDBMS dependent, here's a solution that might work depending on if you can use window functions.

Note this doesn't hard-code divide by 3 in case you're in a partial quarter.

WITH combined AS (
  SELECT *,
  FROM your_tbl q
  JOIN monthly_dates m
  ON m.monthly_dt >= q.start_dt
  AND m.monthly_dt < q.end_dt
  )
  SELECT *
  , values / COUNT(1) OVER(PARTITION BY start_dt, end_dt) as monthly_values
  FROM combined

sqlfiddle