0

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
leftjoin
  • 36,950
  • 8
  • 57
  • 116
Pak Lim Li
  • 21
  • 3

2 Answers2

1

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

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.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87