CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Derek Lilley
Derek Lilley

Reputation: 95

Snowflake SQL: Need a Date Spine added to Existing Table Columns

I have two separate tables. Table1 contains only 1 column, called date_spine and it has all dates from 2021-01-01 to current date. Table2 contains distinct columns of State, Store. My final goal is to create a new Table which contains all Dates in the Data Spine against every unique State, Store combination. Below is a brief example. I am hoping for SQL Script which will work in Snowflake. Thank you in advance!!

State Store Date_Spine
IL Chicago 2021-01-01
IL Chicago 2021-01-02
IL Chicago 2021-01-03
IL Chicago 2021-01-04
IL Chicago 2021-01-05
MO St Louis 2021-01-01
MO St Louis 2021-01-02
MO St Louis 2021-01-03
MO St Louis 2021-01-04
MO St Louis 2021-01-05

Upvotes: 2

Views: 2248

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521063

You may use a cross join approach with calendar tables:

SELECT t1.Date_Spine, t2.State, t2.Store
FROM (SELECT DISTINCT Date_Spine FROM Table1) t1
CROSS JOIN (SELECT DISTINCT State, Store FROM Table2) t2
ORDER BY t2.State, t2.Store, t1.Date_Spine;

Upvotes: 2

Related Questions