SentEmail:
LOAD * ;
SQL
SELECT
sent_email_datetime,
DATE_PART('month',sent_email_datetime) AS month,
DATE_PART('year',sent_email_datetime) AS year,
TRUNC(DATE_TRUNC('month',sent_email_datetime)) AS month_start_date,
LAST_DAY(sent_email_datetime) AS month_end_date,
CASE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 1 AND 3 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/01/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 4 AND 6 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/04/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 7 AND 9 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/07/01')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 10 AND 12 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/10/01')::DATE
END AS quarter_start_date,
CASE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 1 AND 3 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/03/31')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 4 AND 6 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/06/30')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 7 AND 9 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/09/30')::DATE
WHEN DATE_PART('month',sent_email_datetime) BETWEEN 10 AND 12 THEN CONCAT(DATE_PART('year',sent_email_datetime)::VARCHAR,'/12/31')::DATE
END AS quarter_end_date,
CASE
WHEN DATE_PART('month',sent_email_datetime)=1 THEN CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/01/01')::Date
ELSE CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/01/01')::Date
END AS year_start_date,
CASE
WHEN DATE_PART('month',sent_email_datetime)=1 THEN CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/12/31')::Date
ELSE CONCAT(DATE_PART('year',sent_email_datetime)::varchar,'/12/31')::Date
END AS year_end_date
FROM "$(vConnectionPath)"."gcdw_rpt"."content_effectiveness_sent_email_vw"
LIMIT 10000
;
