table name: transcript
| transcriptid | userid | moduleid | linkid | type | startdate |
|---|---|---|---|---|---|
| t1 | user1 | p1 | p1 | program | 1970-01-01 01:00:00.000 |
| t2 | user1 | m1 | p1 | module | 2024-05-03 12:34:19.777 |
| t3 | user1 | m2 | p1 | module | 2024-05-03 13:05:52.630 |
| transcriptid | userid | moduleid | linkid | type | startdate |
|---|---|---|---|---|---|
| t4 | user2 | p2 | p2 | program | 1970-01-01 01:00:00.000 |
| t5 | user2 | m3 | p2 | module | 2024-04-03 08:34:19.777 |
| t6 | user2 | m4 | p2 | module | 2024-04-03 10:05:52.630 |
Context:
I have a program module (p1) that contains 2 submodules (m1,m2).
The issue is that program modules startdate aren’t updating correctly for several programs and they should match the startdate of the earliest submodule that was started (e.g. t2)
The linkid column uses the moduleid to link submodules to the program module (e.g. m1,m2 are submodules of p1)
What im wating to do is update the startdate for program modules to use the earliest startdate from the linked submodules.
transcriptid t1 should be using the startdate from t2.
transcriptid t4 should be using the startdate from t5.
Hope that all makes sense!
Husnaid Javaid is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.