Create Calendar for 3 Dates in 3 Tables

[microwin88x]

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:

- 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 connect the Data Model so that I could handle dates from all tables?

Thank you!

[rahulpawarb]

Hello Micro,

Please refer below:

Canonical Date

Hope this will help.

Regards!

Rahul

[tunoi]

You need to concatenate those 3 tables into one and rename the date fields to have the same name. After that you will be able to build objects that respond to your questions