How can i generate a Date table with fields like "Dayofweek", "weekofyear" etc ;and rows equals to date from 2010-01-01 till current_date
like below:
Dayofweek Dayofmonth Dayofyear Weekofmonth Weekofyear Holiday
2010-01-01 6 1 1 1 1 Y
2010-01-02 7 2 2 1 1 N
2010-01-03 1 3 3 1 1 N
.....
2019-03-31 1 31 90 6 14 N
P.S. DayofWeek = day num of the week, Sun = 1, Sat = 7
Dayofmonth = day num of the month
Holiday is a flag to distinguish whether the records is a public holiday
So first step i need to do may be create records from 2010-01-01 till current_date, i wonder while loop in hive and mssql will do?
Then I have the column ready
Finally combine them.
I have tried
"Declare @startdate date
Declare @enddate date
set @startdate = '2010-01-01'
set @end_date = current_date
while @ start_date <=end_date
BEGIN
DATEADD(DAY,1,@startdate)
END
"Declare @startdate date
Declare @enddate date
set @startdate = '2010-01-01'
set @end_date = current_date
while @ start_date <=end_date
BEGIN
DATEADD(DAY,1,@startdate)
END
Dayofweek Dayofmonth Dayofyear Weekofmonth Weekofyear Holiday
2010-01-01 6 1 1 1 1 Y
2010-01-02 7 2 2 1 1 N
2010-01-03 1 3 3 1 1 N
.....
2019-03-31 1 31 90 6 14 N
Solution for Hive:
set hivevar:start_date=2010-01-01; --replace with your start_date
set hivevar:end_date=current_date; --replace with your end_date
with date_range as
(--this query generates date range
select date_add ('${hivevar:start_date}',s.i) as dt
from ( select posexplode(split(space(datediff(${hivevar:end_date},'${hivevar:start_date}')),' ')) as (i,x) ) s
),
holiday as (
select stack(5, --add more
'01-01', 'New Year',
'01-21', 'Martin Luther King Day',
'02-18', 'Presidents Day',
'05-27', 'Memorial Day',
'07-04', 'Independence Day'
) as ( mtdt,holiday_name)
)
select d.dt as date,
date_format(current_date,'u') as dayofweek,
day(dt) as dayofmonth,
date_format(current_date,'D') as dayofyear,
date_format(current_date,'W') as weekofmonth,
weekofyear(dt) as weekofyear,
case when h.mtdt is not null then 'Y' else 'N' end as Holiday,
h.holiday_name
from date_range d
left join holiday h on substr(d.dt,6)= h.mtdt
;
Add more holidays.
You can get a calendar table with the following solution:
SELECT calendar.*,
DATEPART(dw, calendar.date_value) AS DayOfWeek,
DATEPART(dd, calendar.date_value) AS DayOfMonth,
DATEPART(dy, calendar.date_value) AS DayOfYear,
DATEPART(wk, calendar.date_value) AS WeekOfYear,
CASE WHEN holidays.date_value IS NULL THEN 'N' ELSE 'J' END AS Holiday
FROM (
SELECT DATEADD(DAY, t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0, '1970-01-01') AS date_value FROM
(SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
(SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
(SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
(SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
(SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) calendar INNER JOIN (
SELECT '2010-01-01' AS date_value UNION SELECT '2010-01-06'
) holidays ON calendar.date_value = holidays.date_value
WHERE calendar.date_value BETWEEN CAST('2010-01-01' AS DATE) AND GETDATE()
ORDER BY calendar.date_value
The basic calendar table is based on this solution on StackOverflow. With the INNER JOIN you can add some holidays.
With DATEPART you can get some information about the date.
