Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following tables:
CUSTOMERS | |||||
DOC_NUM | LAST_NAME | FIRST_NAME | BIN_NUM | BIRTH_DATE | CREATE_DATE |
1 | SMITH | JOHN | 557 | 01/01/1985 | 01/02/2017 |
2 | BLACK | JACK | 855 | 02/03/1986 | 02/02/2017 |
3 | ELLIS | SOPHIE | 358 | 04/05/1987 | 03/03/2017 |
4 | JOHNSON | MAX | 125 | 15/10/1989 | 04/03/2017 |
5 | DONALD | WILL | 456 | 05/07/1980 | 01/04/2017 |
TRANSACTIONS | |||||
BIN_NUM | TRANS_DATE | TRANS_TIME | TRANS_NUM | SHOP | TOTAL |
557 | 05/02/2017 | 15:15 | 2258 | LOC1 | 558 |
855 | 07/02/2017 | 17:18 | 3325 | LOC1 | 125 |
358 | 05/03/2017 | 20:05 | 1225 | LOC3 | 85 |
AIRLINE_MILES | |||||
DOC_NUM | MILES_DATE | MILES_AMT | AIRLINE_NUM | SHOP | BILL_NUM |
1 | 05/02/2017 | 100 | A180 | LOC1 | 850 |
2 | 07/02/2017 | 200 | A185 | LOC1 | 855 |
3 | 05/03/2017 | 300 | A190 | LOC3 | 900 |
The thing is I need to create a Master Calendar to handle the following dates from the 3 tables.
- CUSTOMERS: CREATE_DATE
- TRANSACTIONS: TRANS_DATE
- AIRLINE_MILES: MILES_DATE
So that if I filter one Date (Year/Month/Day) I'd like to know:
- How many Customers were created on that period of time.
- How many Transactions were made and which amount.
- How many Miles where exchanged on that date.
Do you know how could I create the Data Model so that I could handle dates from all tables at a time?
Thanks!
How about this??
In your calendar,
Load Date,
Date as CREATE_DATE,
Date as TRANS_DATE,
Date as MILES_DATE,
........
.......
..
....
From your master calender.
You have two options:
These may also help
How to use - Master-Calendar and Date-Values