In this multiple UNION ALL, there are many selects. Below are just the first two for simplicity.
As can be seen, the hour increases for each select. It works fine but there may be a need to increase it in the future. I've been looking at a possible CROSS APPLY after doing some research but having no luck with it.
SELECT b.ID, b.ShiftDate, b.ShiftName, b.PlantNum, b.MachineNumber, Dateadd(Hour,0, b.StartTime) AS StartTime, ISNULL(s.Hour01, 'Y') AS Scheduled FROM Master_tbl b LEFT OUTER JOIN Scheduled_tbl s ON b.ID = s.ID UNION ALL SELECT b.ID, b.ShiftDate, b.ShiftName, b.PlantNum, b.MachineNumber, Dateadd(Hour,1, b.StartTime) AS StartTime, ISNULL(s.Hour02, 'Y') AS Scheduled FROM Master_tbl b LEFT OUTER JOIN Scheduled_tbl s ON b.ID = s.ID
And here is a sample of the output:
8 2017-02-20 1st Shift 5000 J31 2017-02-20 06:00:00.000 Y 9 2017-02-20 2nd Shift 5000 J31 2017-02-20 14:00:00.000 Y 7 2017-02-20 3rd Shift 5000 J31 2017-02-19 22:00:00.000 Y 11 2017-02-21 1st Shift 5000 J31 2017-02-21 06:00:00.000 Y 12 2017-02-21 2nd Shift 5000 J31 2017-02-21 14:00:00.000 Y 10 2017-02-21 3rd Shift 5000 J31 2017-02-20 22:00:00.000 Y
There are currently 24 selects and I know there is a more efficient way.
Especially since I removed a few columns for a better read.
Thanks for any input.