I have a table with the follow columns: reportid, reportname, startdate, consolidated
Reports which are consolidated do not have a start date.
What I need to do is to find the earliest start date within the subreports and set it as the start date
For example
report reportname startdate consolidated
1 ABC 2019/1/1 1
2 DEF 3,4
3 GHI 2019/4/1 3
4 JKF 2019/5/1 4
The report may be consolidated from any number of reports (ie. report 10 may consist of 11,12,13 while report 20 may consist of only 21 and 22)
Output required
report reportname startdate consolidated
1 ABC 2019/1/1 1
2 DEF 2019/4/1 3,4
3 GHI 2019/4/1 3
4 JKF 2019/5/1 4
I can only think of pulling each number and looping through the entire list, comparing each date that is picked up as I go. However, this list is very very long which doesn't make it very feasible.
Thanks in advance!
Unfortunately, I do not have the authority to adjust the database where these tables are concerned.