Data Model - Single Transaction Table

[Khadeer123]

Dear Team,

Below is the brief about the requirement.

• Requirement for Automobile Industry
• The transaction table list is Enquiry, Test Drive, and Booking attached sample data for your reference.
• Enquiry Table Enquiry ID is the primary key. For each Enquiry, the system generates a unique number.
• Test Drive table Test Drive ID is the primary key and Enquiry Key is the common key between Enquiry and Test drive.
• Test drive is not mandatory for all Enquiries it's optional.
• One Enquiry may give more than one Test drive and for a different model or same model (Ex: Enquiry ID 11 and 18 in Testdrive table)
• Booking table Booking ID is the primary key. Enquiry Key is the common key between Enquiry and Booking.
• Model on the time of Enquiry may differ while booking means Enquiry model and booking model may be same or different (Enquiry ID 3,7, 15 in booking table)

Input Tables

Enquiry Data

Enquiry ID	Enquiry Date	Model	Enquiry Type	Exchange	Customer Type
1	21-01-2021	Maruti	Dealer	N	Ind
2	27-01-2021	Tata	Online	N	Ind
3	31-01-2021	Tata	Telephone	Y	Ind
4	03-02-2021	Kia	Dealer	N	Ind
5	07-02-2021	Tata	Online	Y	Ind
6	12-03-2021	Maruti	Telephone	N	Ind
7	12-03-2021	Tata	Online	N	Corp
8	14-03-2021	Kia	Telephone	Y	Ind
9	21-03-2021	Maruti	Dealer	Y	Ind
10	27-03-2021	Kia	Online	N	Ind
11	12-04-2021	Kia	Telephone	N	Ind
12	15-04-2021	Maruti	Online	N	Ind
13	01-05-2021	Kia	Dealer	N	Ind
14	07-05-2021	Kia	Online	Y	Ind
15	18-05-2021	Tata	Telephone	N	Corp
16	22-05-2021	Maruti	Dealer	N	Ind
17	27-05-2021	Tata	Online	N	Ind
18	30-05-2021	Maruti	Telephone	Y	Ind
19	31-05-2021	Kia	Online	N	Corp
20	04-06-2021	Tata	Dealer	N	Ind

Testdrive 

Testdrive ID	Enquiry ID	Testdrive Date	Model
101	2	25-01-2021	Tata
102	3	01-02-2021	Tata
103	5	07-02-2021	Kia
104	8	15-03-2021	Kia
105	9	21-03-2021	Maruti
106	11	12-04-2021	Tata
107	11	13-04-2021	Kia
108	13	02-05-2021	Kia
109	15	18-05-2021	Tata
110	18	01-06-2021	Tata
111	18	01-06-2021	Maruti
112	19	01-06-2021	Kia

Booking 

Booking ID	Enquiry ID	Booking Date	Model	Status
201	1	30-01-2021	Maruti	Invoiced
202	2	31-01-2021	Tata	Invoiced
203	3	31-01-2021	Kia	Invoiced
204	7	12-03-2021	Kia	Invoiced
205	9	22-03-2021	Maruti	Booked
206	11	15-04-2021	Kia	Allotted
207	15	18-05-2021	Kia	Allotted
208	17	27-05-2021	Tata	Booked
209	18	01-06-2021	Maruti	Invoiced
210	20	04-06-2021	Tata	Booked

The resultant output should be as below. 

Thanks in advance. 

Sorry for the long post.

Regards,

Khadeer

[Icey · Accepted solution]

Hi @Khadeer123 ,

Please check the attachment:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Khadeer123]

Yes. How to achieve the output which I have mentioned in the image by using the above data. 

Regards,

Khadeer

[Shahfaisal]

Do you have a specific question you need help with?