I have a table containing bookings. Each booking contains a field with delimited dates in the format yyyymmdd. Each booking also contains a fields with the attendance details in for format of the date and T/F if attended or not - this may not contain all the dates or be null if no attendance data available (ie if there is no corresponding date in the attended field we don't know if they attended that date). There may also be dates in the attendance field where the corresponding date doesn’t appear in the booked field (ie they attended a date they didn’t book).
e.g.:
| ID | BookedDates | AttendedDates | |
|---|---|---|---|
| 1 | ;20241020;20241021; | ;20241020T;20241021F; | //attended one date didn’t attend the other |
| 2 | ;20241020; | ;20241020T; | //attended all dates |
| 3 | ;20241020;20241021; | //No attendance data | |
| 4 | ;20241020; | ;20241020T;20241021T; | //Attended one date booked and another date they didn’t book. |
I want to create a view which shows this data as a separate row for each date in a booking and a nullable T/F columns to show if they booked and ALSO a nullable T/F column to show if they booked
e.g.
| ID | BookedDate | Booked | Attended |
|---|---|---|---|
| 1 | 20/10/2024 | True | True |
| 1 | 21/10/2024 | True | False |
| 2 | 20/10/2024 | True | True |
| 3 | 20/10/2024 | True | |
| 3 | 21/10/2024 | True | |
| 4 | 20/10/2024 | True | True |
| 4 | 21/10/2024 | False | True |
I think I can work out to split and loop through the booked/attended dates for each row and set if they attended but how do I use a script to output multiple rows as required? Ive read i can do something with asynchronous scripts but cant see how?
After a bit more digging I have come to the conclusion that SSIS is the best way to do this, so I have added a Transformation script component and done the work in there :)