I have a requirement to perform an INSERT of new records and an UPSERT on a fact table. The fact table is f_course and the corresponding dimension table is d_course. The tables have the following columns:
f_course:
- id
- d_course_id
- person_number
- start_date
- end_date
d_course:
- id
- course_title
- start_date
- end_date
- hours
If a person signs up for more than one course on the same day, the requirement only allows us to insert one row in f_course. To clarify, for a start_date and end_date, there can only be 1 row for a person_number inserted into f_course - this row is a snapshot of courses between these dates.
Is there a way to meet this requirement and do the insert where we are only entering 1 row in f_course even if the person signs up for multiple courses on the same day?