CODEWITHSUNDEEP
snowflake-cloud-data-platform
Przemek Zdunek
Przemek Zdunek

Reputation: 11

how to assign 0 value for not existing records

in snowflake, while using

select sum(sale)
from ..
group by date

Friday is not returned, as no records for Friday, January 10th exists.

how to get explicit 0 value for missing data on Friday 10th?

date weekday sale clerk
2020-01-06 Monday 1,500 John
2020-01-07 Tuesday 1,300 John
2020-01-08 Wednesday 1,400 John
2020-01-09 Thursday 1,600 John
2020-01-11 Saturday 1,700 John
2020-01-12 Sunday 1,200 John
2020-01-06 Monday 1,200 Peter
2020-01-07 Tuesday 1,100 Peter
2020-01-08 Wednesday 1,700 Peter
2020-01-09 Thursday 1,500 Peter
2020-01-11 Saturday 1,000 Peter
2020-01-12 Sunday 1,900 Peter

Upvotes: 1

Views: 595

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

You will need 3 elements:

  • A list of all dates, so we can find a number for each.
  • A right/left join, so the join works even if there's no data for that day
  • A IFNULL(), to transform a null value for a date into a 0.

For example:

with all_dates as (
    select dateadd('day', row_number() over(order by 0) -1, '2020-01-01') generated_day
    from table(generator(rowcount => 6))
), data as (
    select '2020-01-01'::date d, 3 value
    union all select '2020-01-04', 7
)

select generated_day, ifnull(value, 0)
from data a
right join all_dates b
on a.d=b.generated_day
;

enter image description here

See also https://stackoverflow.com/a/65822976/132438.

Upvotes: 2

Related Questions