SQL: How to split data from quaterly to monthly with date

Question

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.

start	end	value
2022-01-01	2022-04-01	25629
2022-04-01	2022-07-01	993621



CREATE TABLE #your_tbl
    ("start_dt" timestamp, "end_dt" timestamp, "values" int)
;
    
INSERT INTO #your_tbl
    ("start_dt", "end_dt", "values")
VALUES
    ('2020-01-01 00:00:00', '2020-04-01 00:00:00', 114625),
    ('2020-04-01 00:00:00', '2020-07-01 00:00:00', 45216),
    ('2020-07-01 00:00:00', '2020-10-01 00:00:00', 513574)
    

DECLARE @datefrom datetime
DECLARE @dateto datetime
SET @datefrom='2022-04-01'
SET @dateto = '2022-07-01'

;WITH cte AS
(
SELECT @datefrom as MyDate
UNION ALL
SELECT DATEADD(month,1,MyDate)
FROM cte
WHERE DATEADD(month,1,MyDate)<@dateto
),

combined AS (
  SELECT *
  FROM #your_tbl q
  JOIN cte m
  ON YEAR(m.MyDate) >= q.start_dt
  AND MONTH(m.MyDate) < q.end_dt
  )
  SELECT *, [values]/COUNT(1) OVER(PARTITION BY [start_dt], [end_dt]) as monthly_values
  FROM combined

DROP TABLE #your_tbl

create a table that is every month date that you want. join together where monthdate >= start and < end. now that theyre all in 1 table you can count rows partition by start-end that will give you 3... then divide you can figure it out from there. without rdbms info i dont want to write out a solution only to find out you cant use window functions or something — Josh, Jul 14 '22 at 16:32

score 0 · Answer 1 · answered Jul 14 '22 at 16:36

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.

Josh · Answer 2 · 2022-07-14T18:57:51.487

0

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

edited Jul 14 '22 at 18:57

answered Jul 14 '22 at 16:46

Josh

1,493
1
13
24

Hi Josh, I have tried your approach. However, I get following error: A table can only have one timestamp column. Because table '#your_tbl' already has one, the column 'end_dt' cannot be added. I have put my query in the description above. – Vish Jul 14 '22 at 18:52
@Vish I just updated the fiddle to use DATE instead of TIMESTAMP. But what is your real data? are start and end dates? – Josh Jul 14 '22 at 18:58
the start and end dates are the quarterly start and end date. time stamp is not necessary. However, I am not sure if I can hardcore monthly dates just like in your fiddle. I have dates till 2022. – Vish Jul 14 '22 at 20:39
It depends on your RDBMS, so I didnt add that part. Youre probably using Microsoft SQL Server I'm guessing. You should build a calendar table as a permanent part of your warehouse because it comes in handy for times like this. Some RDBMS have a handy way to generate a range of dates, but you should research that issue directly. There are many options to generate your monthly dates. https://stackoverflow.com/questions/7885851/months-between-two-dates – Josh Jul 14 '22 at 21:09

SQL: How to split data from quaterly to monthly with date

2 Answers2