Building Calendar Dimension Table from Scratch with SQL | SQL For Analytics

[@zeeshanazeem5212]

Ankit Bhai, boht barhya kaam kiya hai... 2 months se dhoondh raha tha. Itnay din se yeh video kidhar th bhayya. Thanks ❤

[@shoibazareen4489]

Thanks for creating the data engineering videos, I love your explanation. Good luck sir

[@theraizadatalks14]

Thank you for this video, I've converted it into Stored Procedure to make it more dynamic. Please find it below :
/*
The stored procedure sp_create_calendar_dim_table is
to generate a calendar dimension table (cal_dim_new) containing a range of dates between the specified start and end dates
as parameters.
*/
-- Creation of Sp : sp_create_calender_dim_table
CREATE PROCEDURE sp_create_calender_dim_table
@start_date date,
@end_date date
as
BEGIN
with recursive_cte as (
Select @start_date as cal_date
union all
Select dateadd(dd,1,cal_date) as cal_date
from recursive_cte
where cal_date < @end_date
)
Select row_number() over(order by (select null)) as id, cal_date,
datepart(year,cal_date) as cal_year,
datepart(dayofyear,cal_date) as cal_year_day,
datepart(quarter,cal_date) as cal_quarter,
datepart(month,cal_date) as cal_month,
datename(month,cal_date) as cal_month_name,
datepart(day,cal_date) as cal_month_day,
datepart(week,cal_date) as cal_week,
datepart(weekday,cal_date) as cal_week_day,
datename(weekday,cal_date) as cal_day_name
into cal_dim_new
from recursive_cte
option (maxrecursion 0)
END;
-- Execute Sp : sp_create_calender_dim_table
EXEC sp_create_calender_dim_table @start_date = '2000-01-01', @end_date = '2050-12-31'
-- Check the cal_dim_new table
Select * from cal_dim_new

[@harshaltare119]

So excited for the upcoming query questions related to this calendar dimension table.

[@Ashu23200]

you blown my mind. god bless another god of sql.

[@TheCasualDiary]

Great explaination🎉

[@srinubathina7191]

Thank You Bro Super explanation. I am learning very good stuff from you

[@AamerSuhail]

No more distraction.. Just stick to Ankit Bhansal YT channel for SQL & thanks me later 😅

[@Ashu23200]

absolute true

[@iamkiri_]

you are the man ... Always great content -:)

[@rockagain5416]

Very good Logic ❤❤❤

[@SagarKumar-hh8kt]

Can you explain me what is the use of in joining of two table or in self join?

[@nandan7755]

Nice explanation dates functions Ankit bhaiya ❤❤🎉🎉

[@GSK_G17]

Hi Ankit, it is a great learning, I have been following you from the beginning, would you please implement day light saving logic in the where condition, ex: my views delivering the report every 1 hour to the business

[@ankitbansal6]

What exactly do you want please explain

[@Justin2k20]

Thank you Ankit 🙏🏻

[@gayatri7536]

Thankyou it's really superb 👏👏

[@ankitbansal6]

Welcome 😊

[@shristisrivastava1324]

Please make a video on subquery.
I am not able to understand how to solve subqueries problem.
I tried a lot from different different resources but not able to solve it .
And if there is any cheat method for this then also please share.
Thankyou

[@prasannaeega7459]

S plz share

[@PraveenSinghLko]

Sir is it correct according to you ?
WITH cte AS (
SELECT CAST('2000-01-01' AS date) AS cal_date
UNION ALL
SELECT DATEADD(DAY, 1, cal_date) AS cal_date
FROM cte
WHERE cal_date < CAST('2050-12-30' AS date)
)
SELECT
cal_date,
DATEPART(YEAR, cal_date) AS cal_year,
DATEPART(dayofyear, cal_date) AS cal_year_day,
DATEPART(quarter, cal_date) AS cal_quarter,
DATEPART(month, cal_date) AS cal_month,
DATENAME(month, cal_date) AS cal_month_name,
DATEPART(day, cal_date) AS cal_month_day,
DATEPART(week, cal_date) AS cal_week,
DATEPART(weekday, cal_date) AS cal_week_day,
DATENAME(weekday, cal_date) AS cal_day_name
INTO calendar_table
FROM cte
OPTION (MAXRECURSION 32676);

[@0099kunal1]

Hey Ankit are we considering leap year also in the calendar?

[@ankitbansal6]

Yes absolutely

[@shivammishra-mk9jp]

Hey Ankit,
Can you please help me with how to create this dimension table in mysql
I am done with all the script following your video but I got stuck at last point when we need to create the physical table.
This query is not working in mysql....please help.
SELECT row_number() over(order by cal_date asc) as id , * into calendar_dimension from cte ;
SET SESSION cte_max_recursion_depth = 1000000;
Thanks in advance

[@ankitbansal6]

Use create table as instead of into

[@JayPatel-wv4mz]

@@ankitbansal6 Hello Ankit, Can you help in correcting the mistake, below is the script I'm using in MYSQL CREATE TABLE IF NOT EXISTS calendar_dim (
cal_date DATE,
cal_year INT,
cal_year_day INT,
cal_quarter INT,
cal_month INT,
cal_month_name VARCHAR(20),
cal_month_day INT,
cal_week INT,
cal_week_day INT,
cal_day_name VARCHAR(20)
);
-- Use the recursive CTE to generate data
insert into calender_dim
WITH RECURSIVE cte AS (
-- Anchor member
SELECT
CAST('2022-01-01' AS DATE) AS cal_date,
YEAR(CAST('2022-01-01' AS DATE)) AS cal_year,
DAYOFYEAR(CAST('2022-01-01' AS DATE)) AS cal_year_day,
QUARTER(CAST('2022-01-01' AS DATE)) AS cal_quarter,
MONTH(CAST('2022-01-01' AS DATE)) AS cal_month,
MONTHNAME(CAST('2022-01-01' AS DATE)) AS cal_month_name,
DAY(CAST('2022-01-01' AS DATE)) AS cal_month_day,
WEEK(CAST('2022-01-01' AS DATE)) AS cal_week,
DAYOFWEEK(CAST('2022-01-01' AS DATE)) AS cal_week_day,
DAYNAME(CAST('2022-01-01' AS DATE)) AS cal_day_name
UNION ALL
-- Recursive member
SELECT
ADDDATE(cal_date, INTERVAL 1 DAY) AS cal_date,
YEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year,
DAYOFYEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year_day,
QUARTER(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_quarter,
MONTH(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month,
MONTHNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_name,
DAY(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_day,
WEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week,
DAYOFWEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week_day,
DAYNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_day_name
FROM cte
WHERE cal_date < CAST('2025-01-10' AS DATE)
)
select * from calender_dim; Getting zero record insert into calender_dim

[@ankitbansal6]

@@JayPatel-wv4mz remove into at the end ..into calendardim ...

[@JayPatel-wv4mz]

@@ankitbansal6 sir , If I remove the insert into statement then I'm not getting any record from calender_dim table. It is not inserting the value into calender_dim table and since we have created CTE so I cannot use that CTE outside the recursive table . see the script below. CREATE TABLE IF NOT EXISTS calendar_dim (
cal_date DATE,
cal_year INT,
cal_year_day INT,
cal_quarter INT,
cal_month INT,
cal_month_name VARCHAR(20),
cal_month_day INT,
cal_week INT,
cal_week_day INT,
cal_day_name VARCHAR(20)
);
-- Use the recursive CTE to generate data
insert into calender_dim
WITH RECURSIVE cte AS (
-- Anchor member
SELECT
CAST('2022-01-01' AS DATE) AS cal_date,
YEAR(CAST('2022-01-01' AS DATE)) AS cal_year,
DAYOFYEAR(CAST('2022-01-01' AS DATE)) AS cal_year_day,
QUARTER(CAST('2022-01-01' AS DATE)) AS cal_quarter,
MONTH(CAST('2022-01-01' AS DATE)) AS cal_month,
MONTHNAME(CAST('2022-01-01' AS DATE)) AS cal_month_name,
DAY(CAST('2022-01-01' AS DATE)) AS cal_month_day,
WEEK(CAST('2022-01-01' AS DATE)) AS cal_week,
DAYOFWEEK(CAST('2022-01-01' AS DATE)) AS cal_week_day,
DAYNAME(CAST('2022-01-01' AS DATE)) AS cal_day_name
UNION ALL
-- Recursive member
SELECT
ADDDATE(cal_date, INTERVAL 1 DAY) AS cal_date,
YEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year,
DAYOFYEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year_day,
QUARTER(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_quarter,
MONTH(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month,
MONTHNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_name,
DAY(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_day,
WEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week,
DAYOFWEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week_day,
DAYNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_day_name
FROM cte
WHERE cal_date < CAST('2025-01-10' AS DATE))
select * from calender_dim;

[@rituraj5249]

BUT IN MYSQL, insert into syntax not working,"CREATE TABLE IF NOT EXISTS calendar_dim (
cal_date DATE,
cal_year INT,
cal_year_day INT,
cal_quarter INT,
cal_month INT,
cal_month_name VARCHAR(20),
cal_month_day INT,
cal_week INT,
cal_week_day INT,
cal_day_name VARCHAR(20)
);
-- Use the recursive CTE to generate data
insert into calender_dim
WITH RECURSIVE cte AS (
-- Anchor member
SELECT
CAST('2022-01-01' AS DATE) AS cal_date,
YEAR(CAST('2022-01-01' AS DATE)) AS cal_year,
DAYOFYEAR(CAST('2022-01-01' AS DATE)) AS cal_year_day,
QUARTER(CAST('2022-01-01' AS DATE)) AS cal_quarter,
MONTH(CAST('2022-01-01' AS DATE)) AS cal_month,
MONTHNAME(CAST('2022-01-01' AS DATE)) AS cal_month_name,
DAY(CAST('2022-01-01' AS DATE)) AS cal_month_day,
WEEK(CAST('2022-01-01' AS DATE)) AS cal_week,
DAYOFWEEK(CAST('2022-01-01' AS DATE)) AS cal_week_day,
DAYNAME(CAST('2022-01-01' AS DATE)) AS cal_day_name
UNION ALL
-- Recursive member
SELECT
ADDDATE(cal_date, INTERVAL 1 DAY) AS cal_date,
YEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year,
DAYOFYEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year_day,
QUARTER(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_quarter,
MONTH(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month,
MONTHNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_name,
DAY(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_day,
WEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week,
DAYOFWEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week_day,
DAYNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_day_name
FROM cte
WHERE cal_date < CAST('2025-01-10' AS DATE)
)
select * from calender_dim;" this one is working but output record is showing zero. please help

[@JayPatel-wv4mz]

Hey use below script for my sql @rituraj5249

[@JayPatel-wv4mz]

CREATE TABLE IF NOT EXISTS calender_dim (
cal_date DATE,
cal_year INT,
cal_year_day INT,
cal_quarter INT,
cal_month INT,
cal_month_name VARCHAR(20),
cal_month_day INT,
cal_week INT,
cal_week_day INT,
cal_day_name VARCHAR(20)
);
-- Use the recursive CTE to generate data
WITH recursive cte AS (
-- Anchor member
SELECT
CAST('2022-01-01' AS DATE) AS cal_date,
YEAR(CAST('2022-01-01' AS DATE)) AS cal_year,
DAYOFYEAR(CAST('2022-01-01' AS DATE)) AS cal_year_day,
QUARTER(CAST('2022-01-01' AS DATE)) AS cal_quarter,
MONTH(CAST('2022-01-01' AS DATE)) AS cal_month,
MONTHNAME(CAST('2022-01-01' AS DATE)) AS cal_month_name,
DAY(CAST('2022-01-01' AS DATE)) AS cal_month_day,
WEEK(CAST('2022-01-01' AS DATE)) AS cal_week,
DAYOFWEEK(CAST('2022-01-01' AS DATE)) AS cal_week_day,
DAYNAME(CAST('2022-01-01' AS DATE)) AS cal_day_name
UNION ALL
-- Recursive member
SELECT
ADDDATE(cal_date, INTERVAL 1 DAY) AS cal_date,
YEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year,
DAYOFYEAR(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_year_day,
QUARTER(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_quarter,
MONTH(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month,
MONTHNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_name,
DAY(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_month_day,
WEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week,
DAYOFWEEK(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_week_day,
DAYNAME(ADDDATE(cal_date, INTERVAL 1 DAY)) AS cal_day_name
FROM cte
WHERE cal_date < CAST('2025-01-10' AS DATE))
select * from cte;
-- Just modified your query. This will work in mysql

[@deepeshmatkati3058]

Perfect explanation

[@ankitbansal6]

Thank you 😊

[@KrishnaVermaAnalytics]

I tried to make a calendar table in MySQL:
SET cte_max_recursion_depth=30000;
with recursive cte as
(select cast('2000-01-01' as date) as cal_date
,year('2000-01-01') as cal_year
,dayofyear('2000-01-01') as cal_year_day
,quarter('2000-01-01') as cal_quarter
,month('2000-01-01') as cal_month
,monthname('2000-01-01') as cal_month_name
,day('2000-01-01') as cal_month_day
,week('2000-01-01') as cal_week
,weekday('2000-01-01') as cal_week_day
,dayname('2000-01-01') as cal_day_name
union all
select date_add(cal_date, interval 1 day) as cal_date,
year(date_add(cal_date, interval 1 day)) as cal_year,
dayofyear(date_add(cal_date, interval 1 day)) as cal_year_day,
quarter(date_add(cal_date, interval 1 day)) as cal_quarter,
month(date_add(cal_date, interval 1 day)) as cal_month,
monthname(date_add(cal_date, interval 1 day)) as cal_month_name,
day(date_add(cal_date, interval 1 day)) as cal_month_day,
week(date_add(cal_date, interval 1 day)) as cal_week,
weekday(date_add(cal_date, interval 1 day)) as cal_week_day,
dayname(date_add(cal_date, interval 1 day)) as cal_day_name
from cte
where cal_date < cast('2050-12-31' as date)
)
select row_number() over(order by cal_date asc) as rn,
cal_date, cal_year, cal_year_day, cal_quarter, cal_month, cal_month_name, cal_month_day, cal_week, cal_week_day, cal_day_name
from cte;

[@shashanktiwari133]

how have you created dim table using this cte ?

[@KrishnaVermaAnalytics]

@@shashanktiwari133 it's a recursive cte not just an ordinary cte. it works the same as you apply recursion in any function while coding.