[Template]: Create template for create cube and rollup for the SQL Server

[korawica]

Template SQL

For the CUBE;

SELECT
    SALES_ID
    , TERRITORY_ID
    , SUM(TOTAL) AS TOTAL
FROM {{ target_table }}
GROUP BY CUBE (SALES_ID, TERRITORY_ID);

For the ROLLUP;

SELECT
    YEAR({{ date_col }}) AS ANO
    , MONTH({{ date_col }}) AS MES
    , DAY({{ date_col }}) AS DIA
    , SUM({{ matrix_col }}) AS MT
FROM {{ target_table }}
GROUP BY
    ROLLUP(
        YEAR({{ date_col }}), MONTH({{ date_col }}), DAY({{ date_col }})
    )
;

Description & Usage

Support matrix generator statement for Data Analysis features like GROUPING SET, CUBE, and ROLLUP on the SQL Server database.

SQLPlate System

SQL Server

Jinja2 Version

3.1.6

[korawica]

Reference Docs: SQL: Advanced Aggregations with CUBE and ROLLUP