snowflake070

WITH DATES AS
(
    SELECT
        day_num,

        -- This will get the forecast dates for the next week (week is Sun. to Sat.

        DATEADD(day,day_num - 8, LAST_DAY(DATEADD(week,1,CURRENT_DATE()),week)) AS forecast_date_valid_std,

        DATEADD(day,DAYOFYEAR(forecast_date_valid_std) - 1,DATE_TRUNC(year,DATEADD(year,-1,forecast_date_valid_std))) AS history_date_valid_std
    FROM
    (
        SELECT
            ROW_NUMBER() OVER (ORDER BY SEQ1()) AS day_num
        FROM
            TABLE(GENERATOR(rowcount => 7))
    )
),
HISTORY AS
(
    SELECT
        postal_code,
        country,
        date_valid_std,
        day_num,
        min_temperature_air_2m_f,
        avg_temperature_air_2m_f,
        max_temperature_air_2m_f,
        avg_temperature_dewpoint_2m_f,
        avg_wind_speed_10m_mph,
        avg_wind_direction_10m_deg,
        tot_precipitation_in,
        tot_snowfall_in
    FROM
        DATES AS d
    LEFT JOIN
        HISTORY_DAY AS h
    ON
        d.history_date_valid_std = h.date_valid_std
),
FORECAST AS
(
    SELECT
        postal_code,
        country,
        date_valid_std,
        day_num,
        min_temperature_air_2m_f,
        avg_temperature_air_2m_f,
        max_temperature_air_2m_f,
        avg_temperature_dewpoint_2m_f,
        avg_wind_speed_10m_mph,
        avg_wind_direction_10m_deg,
        tot_precipitation_in,
        tot_snowfall_in
    FROM
        DATES AS d
    LEFT JOIN
        FORECAST_DAY AS f
    ON
        d.forecast_date_valid_std = f.date_valid_std
)
SELECT
    h.postal_code,
    h.country,
    h.date_valid_std AS history_date_valid_std,
    f.date_valid_std AS forecast_date_valid_std,
    h.min_temperature_air_2m_f AS history_min_temperature_air_2m_f,
    f.min_temperature_air_2m_f AS forecast_min_temperature_air_2m_f,
    h.avg_temperature_air_2m_f AS history_avg_temperature_air_2m_f,
    f.avg_temperature_air_2m_f AS forecast_avg_temperature_air_2m_f,
    h.max_temperature_air_2m_f AS history_max_temperature_air_2m_f,
    f.max_temperature_air_2m_f AS forecast_max_temperature_air_2m_f,
    h.avg_temperature_dewpoint_2m_f AS history_avg_temperature_dewpoint_2m_f,
    f.avg_temperature_dewpoint_2m_f AS forecast_avg_temperature_dewpoint_2m_f,
    h.avg_wind_speed_10m_mph AS history_avg_wind_speed_10m_mph,
    f.avg_wind_speed_10m_mph AS forecast_avg_wind_speed_10m_mph,
    h.avg_wind_direction_10m_deg AS history_avg_wind_direction_10m_deg,
    f.avg_wind_direction_10m_deg AS forecast_avg_wind_direction_10m_deg,
    h.tot_precipitation_in AS history_tot_precipitation_in,
    f.tot_precipitation_in AS forecast_tot_precipitation_in,
    h.tot_snowfall_in AS history_tot_snowfall_in,
    f.tot_snowfall_in AS forecast_tot_snowfall_in
FROM
    HISTORY AS h
INNER JOIN
    FORECAST AS f
ON
    h.postal_code = f.postal_code AND
    h.country = f.country AND
    h.day_num = f.day_num
WHERE
    h.postal_code = '24171'
;