Daterange handling partial periods

As a best practice, you don't want to show "partial" historical periods, and you'd like an easy way to separate out currently "not fully baked" data from historical data.  Example, quarterly or monthly aggregation over the past year on 9/15/2018 will only give you data for September 2017 that is after 9/15/2017 -- which is incomplete and a bit misleading.

I've started using this snippet as a workaround at the top of queries:

with dates as
(
  select
    [_start:aggregation] as _start,
    _end as _end,
    [_end:aggregation] as _partial_period_start
  from
  (
    select
      [daterange_start] as _start,
      [daterange_end] as _end
  ) a
)

You can then create the query as normal and use 

WHERE date_col BETWEEN dates._start AND dates._end

instead of the typical [date_col=daterange].  

Hope this is useful

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like3 Follow
  • 3 Likes
  • 3 mths agoLast active
  • 906Views
  • 1 Following