Any way to include empty grouped by time columns

[Jeff Sumner]

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`
<snip>
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]

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

1. Write a separate SQL query to generate just the timeline (see query below)
2. Outer join the output of above with your original query
3. 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]

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