0
I am structuring a new Datawarehouse that will in the future be consumed on a BI platform.
Already created some dimensions for my database one of them was the Dates, in which I break the date into several formats for future manipulation. I am currently creating the dimension of Hours which for the time being is in the following format:
HORA_COMPLETA | HORA | MINUTO | SEGUNDO | PERIODO |TURNO
10:01:25 | 10 | 01 | 25 | Manhã | 1
17:43:32 | 17 | 43 | 32 | Tarde | 1
23:12:13 | 23 | 12 | 13 | Noite | 2
03:07:46 | 03 | 07 | 46 | Madrugada | 2
.
.
.
- There are 2 possible shifts (6:00 to 18:00' turn 1' and 18:00 to 06:00' turn 2' )
The problem I’m having is this::
In the future I will want to know the results of the Shift 2 per day. The issue is that this shift (2) crosses two days (the night of the day 7/05 and the dawn of the day 8/05 for example), in this case the result of shift 2 would be the junction of these two periods that are on different days .
The structure of the date table is briefly like this:
DATA_COMPLETA | DIA | MES | ANO
07/05/2018 | 07 | 05 | 2018
08/05/2018 | 08 | 05 | 2018
09/05/2018 | 09 | 05 | 2018
.
.
.
Is there any way to put this on the table in order to address this situation?
*Just to complement, these two tables are not directly accessed or other tables use this data.
This dimension of dates you refer to is related to what? Time marking? Anyway, there are two dimensions that you refer to: one is by date and one is by turn. If a turn crosses two dates, you have to see by turn. If you want to see by date, you will see the same turn appearing twice.
– Rogério Dec
In the case refer to the period for the registration of some chips in the Rogério system, the issue is that to see by turn the base is consider the dawn and the night of day 7 for example. Not the night of the 7th and the dawn of the 8th as it should be
– Clayton Tosatti