-
Notifications
You must be signed in to change notification settings - Fork 24
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
add support for date_spine #27
Comments
Yes please!
…On Thu, 11 Feb 2021, 16:59 Anders, ***@***.***> 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 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 %}
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
<#27>, or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ACVRBJI2IEYQ5V5E56YNCZ3S6QEF5ANCNFSM4XPF2G6Q>
.
|
closed by #28 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
datetime
folder inside oftsql-utils/macros/dbt_utils/
date_spine.sql
to the newly createddatetime
foldertsql-utils/integration_tests/dbt_utils/dbt_project.yml
Lines 34 to 36 in c47616b
example macro
The text was updated successfully, but these errors were encountered: