SQL (Snowflake) Create month table-CodePudding

I would like to create a monthly summary table to sum values where the date column is within two other different dates from another existing table.

I've tried with the following SQL code but Snowflake returns this error: SQL compilation error: error line 9 at position 10 invalid identifier 'DATE'.

select 
    dateadd(month, seq, dt::date) as DATE,
    year(DATE) as Y,
    month(DATE) as M,
    (select 
        sum(items.total_price_disc_conv)
    from BI.MODELS.CONTR_LINES_ACC as items
    where DATE between items.subscription_start_date and items.subscription_end_date
    ) as ARR
from (
    select 
        seq4() as seq,  
        dateadd(month, 1, '2019-12-01'::date) as dt 
    from table(generator(rowcount => (12 * 5))
    )
);

CodePudding user response：

I think you are hitting this error because you try to use an ALIAS (DATE) in the WHERE filter. Can you try this one?

select 
    dateadd(month, seq, dt::date) as DATE,
    year(DATE) as Y,
    month(DATE) as M,
    (select 
        sum(items.total_price_disc_conv)
    from BI.MODELS.CONTR_LINES_ACC as items
    where dateadd(month, seq, dt::date) between items.subscription_start_date and items.subscription_end_date
    ) as ARR
from (
    select 
        seq4() as seq,  
        dateadd(month, 1, '2019-12-01'::date) as dt 
    from table(generator(rowcount => (12 * 5))
    )
);

Of course, it's better to use MY_DATE instead of DATE.