Any way to include empty grouped by time columns

Question

I have an Analytics report which shows Major Production bugs by Quarter. Chart looked nice, but we got the number of bugs down to 0 the last couple of quarters and so those come back as empty rows for my query - I'd like them to show as a 0 column in my chart, not get skipped. Any way to do this? SELECT CONCAT(DATE_FORMAT(`Issue`.`created_at`, 'y'), '-Q', QUARTER(`Issue`.`created_at`)) AS `CY Quarter`, COUNT(DISTINCT `Issue`.`issue_id`) AS `Count of unique Issue Issue ID` FROM `jira_issue` AS `Issue` WHERE `Issue`.`issue_type` IN ('Bug') AND `Issue`.`created_at` >= TIMESTAMP({CALENDAR.START}) AND `Issue`.`created_at` < (TIMESTAMP({CALENDAR.END}) + INTERVAL 1 DAY) GROUP BY CONCAT(DATE_FORMAT(`Issue`.`created_at`, 'y'), '-Q', QUARTER(`Issue`.`created_at`)) LIMIT 100000;

Inder Singh · Answer

@Jeff Sumner One way to do this would be to:

* Write a separate SQL query to generate just the timeline (see query below)
* Outer join the output of above with your original query
* Use "zero fill" step to fill zeros in empty quarters

Here is the query you can use for step 1:

with date_range as (
SELECT sequence(to_date({CALENDAR.START}), to_date({CALENDAR.END}), interval 1 day) as date_day
),

date_range_explode as (
SELECT explode(date_day) as all_days from date_range
),

years_qtrs as (
SELECT concat(YEAR(`all_days`), '-Q', QUARTER(`all_days`)) AS `yrqtrs`
from date_range_explode
group by yrqtrs
)

SELECT * from years_qtrs

Thanks,

Inder

Tomislav Tobijas · Answer

Hi @Jeff Sumner ,

I'm just gonna move this to Atlassian Analytics group as this is more related to that app, as I guess you'll get better assistance there.

Cheers,
Tobi