CODEWITHSUNDEEP
sqlhana
Aneema
Aneema

Reputation: 176

SQL function to divide one row in four distinct rows

Does anyone has an idea on how to split the yearly financial revenue from a single row into 4 rows representing the four quarters? (assuming every quarter generates the same amount of revenues)? Here below an example: on the upper side what I currently have and on the lower side the splitting I want to achieve.

Text

I did some research over the internet and found CROSS JOIN could definitely help but don't know how.

Upvotes: 1

Views: 682

Answers (2)

Lars Br.
Lars Br.

Reputation: 10388

HANA comes with specific disaggregation functions that work on SERIES tables/data and that can handle horizontal disaggregation similar to the described requirement. (see docu here)

However, series data requires some pre-thought and familiarity with the concepts, so the following is a "series light" version of a statement that solves the problem:

DROP TABLE geo_years;
CREATE COLUMN TABLE geo_years(
    geo_level_1 nvarchar (20)
    , geo_level_2 nvarchar (20)
    , YEAR integer
    , revenue decimal (10, 2)
);

INSERT INTO geo_years VALUES ('EUROPE', 'FRANCE', 2020, 100.00);
INSERT INTO geo_years VALUES ('EUROPE', 'FRANCE', 2021, 200.00);

SELECT 
      gy.geo_level_1
    , gy.geo_level_2
    , gy."YEAR"
    , st.GENERATED_PERIOD_START pstart
    , quarter(add_months(to_date(gy."YEAR"), 3 * (st.GENERATED_period_start-1))) AS QUATER_DATE
    , to_decimal (revenue / 4, 10, 2) AS revenue_disagg
    FROM 
        geo_years gy
    cross JOIN series_generate_integer (1, 1, 5) st
ORDER BY
    geo_level_1, geo_level_2, "YEAR", pstart;

GEO_LEVEL_1|GEO_LEVEL_2|YEAR|PSTART|QUATER_DATE|REVENUE_DISAGG|
-----------+-----------+----+------+-----------+--------------+
EUROPE     |FRANCE     |2020|     1|2020-Q1    |         25.00|
EUROPE     |FRANCE     |2020|     2|2020-Q2    |         25.00|
EUROPE     |FRANCE     |2020|     3|2020-Q3    |         25.00|
EUROPE     |FRANCE     |2020|     4|2020-Q4    |         25.00|
EUROPE     |FRANCE     |2021|     1|2021-Q1    |         50.00|
EUROPE     |FRANCE     |2021|     2|2021-Q2    |         50.00|
EUROPE     |FRANCE     |2021|     3|2021-Q3    |         50.00|
EUROPE     |FRANCE     |2021|     4|2021-Q4    |         50.00|

The key elements here are:

  • generate 4 records for every record in the geo_year: this is done by CROSS JOIN series_generate_integer (1, 1, 5) st. See docu for this function here.
  • calculate the begin dates for every quarter and format the result as YYYY-QTR: quarter(add_months(to_date(gy."YEAR"), 3 * (st.GENERATED_period_start-1))) AS QUATER_DATE
  • distribute the REVENUE evenly over the number of quarters (4): to_decimal (revenue / 4, 10, 2) AS revenue_disagg

And that is really all there is to it.

Upvotes: 3

eshirvana
eshirvana

Reputation: 24568

something like this should work:

select geo_level1,geo_level2,year,q.quarter,revenue / 4
from table t
join (select * from values ((1),(2),(3),(4)) q(quarter))
 on 1 = 1

Upvotes: 0

Related Questions