Using Teradata SQL: Create a reference table for human generations

I recently had a request to analysis some people data by human generations, things like who is a Millennial, who's in Generation X. The SQL below creates a suitable reference table.

-- Creates a table with the definitions of human generations for analysis purposes.

-- Drop the table if it already exusts, otherwise skip
SELECT 1 FROM dbc.TablesV WHERE databasename = 'USER_DB' AND TABLENAME = 'ref_generation_defs';
.IF ACTIVITYCOUNT = 0 THEN GOTO ok
DROP TABLE USER_DB.ref_generation_definitions;
.LABEL ok

-- Create the table
CREATE TABLE USER_DB.ref_generation_defs (
	Generation_Name VARCHAR(60),	-- The name that will be displayed most of the time
	Alternative_Names VARCHAR(120),	-- Other names the generation may be known by seperated by /
	Start_Dt DATE,	-- Should always be the 1st of January in the appropiate year
	End_Dt DATE,	-- Should always be the 31st of December in the appropiate year or a high waterrmark date for the current generation
	Notes VARCHAR(200)	-- Any relevant notes
);

-- Load the data - it's only 7 records so it's not worth getting fancy for this
INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Lost Generation', '', DATE'1883-01-01', DATE'1900-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('G.I. Generation', '', DATE'1901-01-01', DATE'1927-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Traditionalists', 'Silent Generation/Lucky Few', DATE'1928-01-01', DATE'1944-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Baby Boomers', 'Me Generation', DATE'1945-01-01', DATE'1964-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Gen X', 'MTV Generation/Generation X', DATE'1965-01-01', DATE'1979-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Millennials', 'Gen Y/Generation Y', DATE'1980-01-01', DATE'1994-12-31', '');

INSERT INTO USER_DB.ref_generation_defs
	(Generation_Name, Alternative_Names, Start_Dt, End_Dt, Notes)
	VALUES ('Gen Z', 'iGen/Internet Generation/Generation Z/Post-Millennials', DATE'1995-01-01', DATE'9999-12-31', 'The current generation');

The final bit of SQL creates a table that is useful if you need to put a definition of the generations in a report. It formats in a way that's people friendly :-)

-- Produce a nice table that people can cut and paste into reports, spreadsheets etc
SELECT
	Generation_Name,
	CASE
		WHEN end_dt < CURRENT_DATE THEN 
			TRIM( EXTRACT(YEAR FROM Start_Dt) ) || ' - ' || TRIM( EXTRACT(YEAR FROM End_Dt) ) 
		ELSE
			TRIM( EXTRACT(YEAR FROM Start_Dt) ) || ' - Now' 
		END AS Date_Range
FROM USER_DB.ref_generation_defs
ORDER BY Start_dt;

Enjoy!

"I accept choas. I'm not sure whether it accepts me." - Bob Dylan