Snowflake SQL - convert mmddyyyy to mmyyyy and then group by mmyyyy

Question

Working in snowflake sql. I have a date field that is mm-dd-yyyy. I want to convert it to mm-yyyy and then group the rest of the query at the mm-yyyy level.

I have:

select
    SFDC_ACCOUNT_KEY,
    date_trunc('month',DATE_CURRENT) as mm_yyyy,
    max(ACTIVE_COMMERCIAL_UNITS) as max_active_commercial_units,
    max(ACTIVE_HOA_UNITS) as max_active_hoa_units,
    max(ACTIVE_RESIDENTIAL_UNITS) as max_active_res_units,
    max(TOTAL_ACTIVE_UNITS) as max_total_active_units
FROM SHARED.CUSTOMERS_DIM
group by sfdc_account_key, date_current
order by sfdc_account_key, date_current;

Because the original date is an mm-dd-yyyy, I'm still returning a day-level data set (the original table is on an mm-dd-yyyy level), when I want it wrapped at an mm-yyyy level.

This is driving me nuts because I'm sure it's pretty simple, but it's late and I'm confused.

Any help would be greatly appreciated.

score 2 · Accepted Answer · answered Aug 22 '19 at 05:10

use date_trunc('month',DATE_CURRENT) instead of date_current in group by

select
    SFDC_ACCOUNT_KEY,
    date_trunc('month',DATE_CURRENT) as mm_yyyy,
    max(ACTIVE_COMMERCIAL_UNITS) as max_active_commercial_units,
    max(ACTIVE_HOA_UNITS) as max_active_hoa_units,
    max(ACTIVE_RESIDENTIAL_UNITS) as max_active_res_units,
    max(TOTAL_ACTIVE_UNITS) as max_total_active_units
FROM SHARED.CUSTOMERS_DIM
group by sfdc_account_key, date_trunc('month',DATE_CURRENT)
order by sfdc_account_key, date_trunc('month',DATE_CURRENT)

Snowflake SQL - convert mmddyyyy to mmyyyy and then group by mmyyyy

1 Answers1