add support for date_spine

[dataders]

In this dbt slack thread, someone shared a dbt-utils-esque version of the date_spine macro.

We should bring this into this package. It might even also work for Synapse, I don't see any recursive CTEs...

steps to implement macro

1. create a datetime folder inside of tsql-utils/macros/dbt_utils/
2. put the macro in a file called date_spine.sql to the newly created datetime folder
3. add a macro to the end of the file to make it also work for synapse

   {% macro synapse__date_spine(datepart, start_date, end_date) -%}
       {% do return( tsql_utils.sqlserver__date_spine(datepart, start_date, end_date)) %}
   {%- endmacro %}

4. delete these lines https://github.com/dbt-msft/tsql-utils/blob/c47616b07656085b6e92711182ac3a37f13e24f6/integration_tests/dbt_utils/dbt_project.yml#L34-L36

example macro

{% macro sqlserver__date_spine_sql(start_date, end_date, datepart) %}
with
l0 as (
    select c
    from (select 1 union all select 1) as d(c)
),
l1 as (
    select
        1 as c
    from l0 as a
    cross join l0 as b
),
l2 as (
    select 1 as c
    from l1 as a
    cross join l1 as b
),
l3 as (
    select 1 as c
    from l2 as a
    cross join l2 as b
),
l4 as (
    select 1 as c
    from l3 as a
    cross join l3 as b
),
l5 as (
    select 1 as c
    from l4 as a
    cross join l4 as b
),
nums as (
    select row_number() over (order by (select null)) as rownum
      from l5
),
rawdata as (
    select top ({{dbt_utils.datediff(start_date, end_date, datepart)}})  + rownum -1 as n
    from nums
    order by rownum
),
all_periods as (
    select (
        {{
            dbt_utils.dateadd(
                datepart,
                'n',
                start_date
            )
        }}
    ) as date_{{datepart}}
    from rawdata
),
filtered as (
    select *
    from all_periods
    where date_{{datepart}} <= {{ end_date }}
)
select * from filtered
{% endmacro %}
{% macro sqlserver__date_spine(start_date, end_date, datepart) -%}
    {% set date_spine_query %}
        {{sqlserver__date_spine_sql(start_date, end_date, datepart)}} order by 1
    {% endset %}
    {% set results = run_query(date_spine_query) %}
    {% if execute %}
    {% set results_list = results.columns[0].values() %}
    {% else %}
    {% set results_list = [] %}
    {% endif %}
    {%- for date_field in results_list %}
        select '{{ date_field }}' as date_{{datepart}} {{ 'union all ' if not loop.last else '' }}
    {% endfor -%}
{% endmacro %}

[alittlesliceoftom]

Yes please!

On Thu, 11 Feb 2021, 16:59 Anders, notifications@github.com wrote:

In this dbt slack thread https://getdbt.slack.com/archives/CMRMDDQ9W/p1612885997028500, someone shared a dbt-utils-esque version of the date_spine macro.

We should bring this into this package. It might even also work for Synapse, I don't see any recursive CTEs...

{% macro sqlserverdate_spine_sql(start_date, end_date, datepart) %} with l0 as ( select c from (select 1 union all select 1) as d(c) ), l1 as ( select 1 as c from l0 as a cross join l0 as b ), l2 as ( select 1 as c from l1 as a cross join l1 as b ), l3 as ( select 1 as c from l2 as a cross join l2 as b ), l4 as ( select 1 as c from l3 as a cross join l3 as b ), l5 as ( select 1 as c from l4 as a cross join l4 as b ), nums as ( select row_number() over (order by (select null)) as rownum from l5 ), rawdata as ( select top ({{dbt_utils.datediff(start_date, end_date, datepart)}}) + rownum -1 as n from nums order by rownum ), all_periods as ( select ( {{ dbt_utils.dateadd( datepart, 'n', startdate ) }} ) as date{{datepart}} from rawdata ), filtered as ( select from allperiods where date{{datepart}} <= {{ end_date }} )select from filtered {% endmacro %} {% macro sqlserver__date_spine(start_date, end_date, datepart) -%} {% set date_spine_query %} {{sqlserverdate_spine_sql(start_date, end_date, datepart)}} order by 1 {% endset %} {% set results = run_query(date_spine_query) %} {% if execute %} {% set results_list = results.columns[0].values() %} {% else %} {% set results_list = [] %} {% endif %} {%- for date_field in results_list %} select '{{ datefield }}' as date{{datepart}} {{ 'union all ' if not loop.last else '' }} {% endfor -%} {% endmacro %}

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/dbt-msft/tsql-utils/issues/27, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACVRBJI2IEYQ5V5E56YNCZ3S6QEF5ANCNFSM4XPF2G6Q .

[dataders]

closed by #28