-
Notifications
You must be signed in to change notification settings - Fork 503
/
date_spine.sql
75 lines (49 loc) · 1.58 KB
/
date_spine.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
{% macro get_intervals_between(start_date, end_date, datepart) -%}
{{ return(adapter.dispatch('get_intervals_between', 'dbt_utils')(start_date, end_date, datepart)) }}
{%- endmacro %}
{% macro default__get_intervals_between(start_date, end_date, datepart) -%}
{%- call statement('get_intervals_between', fetch_result=True) %}
select {{ dbt.datediff(start_date, end_date, datepart) }}
{%- endcall -%}
{%- set value_list = load_result('get_intervals_between') -%}
{%- if value_list and value_list['data'] -%}
{%- set values = value_list['data'] | map(attribute=0) | list %}
{{ return(values[0]) }}
{%- else -%}
{{ return(1) }}
{%- endif -%}
{%- endmacro %}
{% macro date_spine(datepart, start_date, end_date) %}
{{ return(adapter.dispatch('date_spine', 'dbt_utils')(datepart, start_date, end_date)) }}
{%- endmacro %}
{% macro default__date_spine(datepart, start_date, end_date) %}
{# call as follows:
date_spine(
"day",
"to_date('01/01/2016', 'mm/dd/yyyy')",
"dbt.dateadd(week, 1, current_date)"
) #}
with rawdata as (
{{dbt_utils.generate_series(
dbt_utils.get_intervals_between(start_date, end_date, datepart)
)}}
),
all_periods as (
select (
{{
dbt.dateadd(
datepart,
"row_number() over (order by 1) - 1",
start_date
)
}}
) as date_{{datepart}}
from rawdata
),
filtered as (
select *
from all_periods
where date_{{datepart}} <= {{ end_date }}
)
select * from filtered
{% endmacro %}