Categories
Development SQL

SQL – ‘1’ IF hour in month EXISTS, ‘0’ IF NOT EXISTS

I have a table that has aggregations down to the hour level YYYYMMDDHH. The data is aggregated and loaded by an external process (I don’t have control over). I want to test the data on a monthly basis.

The question I am looking to answer is: Does every hour in the month exist?

I’m looking to produce output that will return a 1 if the hour exists or 0 if the hour does not exist.

The aggregation table looks something like this…

YYYYMM  YYYYMMDD    YYYYMMDDHH  DATA_AGG
201911  20191101    2019110100  100
201911  20191101    2019110101  125
201911  20191101    2019110103  135
201911  20191101    2019110105  95
…   …   …   …
201911  20191130    2019113020  100
201911  20191130    2019113021  110
201911  20191130    2019113022  125
201911  20191130    2019113023  135

And defined as…

CREATE TABLE YYYYMMDDHH_DATA_AGG AS (
    YYYYMM      VARCHAR,
    YYYYMMDD    VARCHAR,
    YYYYMMDDHH  VARCHAR,
    DATA_AGG    INT
);

I’m looking to produce the following below…

YYYYMMDDHH     HOUR_EXISTS
2019110100     1
2019110101     1
2019110102     0
2019110103     1
2019110104     0
2019110105     1
...            ...

In the example above, two hours do not exist, 2019110102 and 2019110104.

I assume I’d have to join the aggregation table against a computed table that contains all the YYYYMMDDHH combos???

The database is Snowflake, but assume most generic ANSI SQL queries will work.

Leave a Reply

Your email address will not be published. Required fields are marked *