This is my table:
idDate timeformat timeformatdate idYear YearName idSemester semestername idquarter quartername idmonth month idweek week idday day
20160101 2016-01-01 01-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 53 W53 1 Friday , 1
20160102 2016-01-02 02-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 53 W53 2 Saturday , 2
20160103 2016-01-03 03-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 53 W53 3 Sunday , 3
20160104 2016-01-04 04-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 4 Monday , 4
20160105 2016-01-05 05-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 5 Tuesday , 5
20160106 2016-01-06 06-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 6 Wednesday, 6
20160107 2016-01-07 07-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 7 Thursday , 7
20160108 2016-01-08 08-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 8 Friday , 8
20160109 2016-01-09 09-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 9 Saturday , 9
20160110 2016-01-10 10-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 1 W1 10 Sunday , 10
20160111 2016-01-11 11-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 11 Monday , 11
20160112 2016-01-12 12-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 12 Tuesday , 12
20160113 2016-01-13 13-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 13 Wednesday, 13
20160114 2016-01-14 14-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 14 Thursday , 14
20160115 2016-01-15 15-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 15 Friday , 15
20160116 2016-01-16 16-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 16 Saturday , 16
20160117 2016-01-17 17-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 2 W2 17 Sunday , 17
20160118 2016-01-18 18-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 18 Monday , 18
20160119 2016-01-19 19-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 19 Tuesday , 19
20160120 2016-01-20 20-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 20 Wednesday, 20
20160121 2016-01-21 21-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 21 Thursday , 21
20160122 2016-01-22 22-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 22 Friday , 22
20160123 2016-01-23 23-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 23 Saturday , 23
20160124 2016-01-24 24-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 3 W3 24 Sunday , 24
20160125 2016-01-25 25-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 25 Monday , 25
20160126 2016-01-26 26-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 26 Tuesday , 26
20160127 2016-01-27 27-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 27 Wednesday, 27
20160128 2016-01-28 28-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 28 Thursday , 28
20160129 2016-01-29 29-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 29 Friday , 29
20160130 2016-01-30 30-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 30 Saturday , 30
20160131 2016-01-31 31-Jan-16 2016 2016 1 S1 1 Q1 201601 Jan 4 W4 31 Sunday , 31
I'm trying to create a time_hierarchy via a SQL statement. What i want to achieve is for example:
Time
[2016] as Year
[Q1, 2016] as Quarter
[Jan, 2016] as Month
[Jan, 1, 2016] as Day
Etc...
I've really no idea how to achieve this or if my table do not support this kind of hierarchy.
Could you help me?
Thanks
Perhaps something like this?
SELECT
CASE WHEN quarterName IS NULL THEN 'Year'
WHEN month IS NULL THEN 'Quarter'
WHEN idDay IS NULL THEN 'Month'
ELSE 'Day'
END
AS aggregateLevel,
CASE WHEN quarterName IS NULL THEN YearName
WHEN month IS NULL THEN QuarterName || ', ' || YearName
WHEN idDay IS NULL THEN Month || ', ' || YearName
ELSE Month || ', ' || CAST(idDay AS VARCHAR(2)) || YearName
END
AS periodTitle,
MIN(idDate) AS periodFirstDate,
MAX(idDate) AS periodFinalDate
FROM
yourTable
GROUP BY
GROUPING SETS (
(YearName),
(YearName, quarterName),
(YearName, quarterName, month),
(YearName, quarterName, month, idDay)
)
ORDER BY
MIN(idDate),
MAX(idDate) DESC