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.
If you just need to add each hour separately, you can use a CROSS APPLY, like this:
SELECT b.ID, b.ShiftDate, b.ShiftName, b.PlantNum, b.MachineNumber,
Dateadd(Hour,hours.hours, b.StartTime) AS StartTime,
ISNULL(s.Hour01, 'Y') AS Scheduled
FROM Master_tbl b
LEFT OUTER JOIN Scheduled_tbl s ON b.MasterID = s.MasterID
CROSS JOIN (
VALUES(0),(1) /*,(2),(3),...(23)*/
) AS hours(hours)
Scott - you need to include the different hour columns from the schedule - it appears.
Looking at this a bit more - I am going to assume that each hour column from Scheduled_tbl can be either NULL, 'Y' or 'N'. It seems a bit weird to me to mark an item as being scheduled when there isn't a value in the table...I would assume that if an entry doesn't exist then it wasn't scheduled.
With that said - we can expand on what Scott provided and do something like this:
SELECT b.ID, b.ShiftDate, b.ShiftName, b.PlantNum, b.MachineNumber,
Dateadd(Hour,h.hours, b.StartTime) AS StartTime,
isnull(h.scheduled, 'Y') AS Scheduled
FROM Master_tbl b
LEFT OUTER JOIN Scheduled_tbl s ON b.MasterID = s.MasterID
CROSS APPLY (
VALUES(0,s.Hour01),(1,s.Hour02),(2,s.Hour03) /* repeat for all */
) AS h(hours,scheduled)
If you are getting additional rows - then there is something in the relationship between Master_tbl and Scheduled_tbl causing multiple rows to be returned.
If the example you have above is where you are showing a problem - I am not seeing the problem. You want one row for each hour and the above is returning a row for hour 06 and hour 07.
The CROSS APPLY is going to take values from the Scheduled_tbl and create one row per output row from the statement. So...
VALUES (0, sHour01), (1, sHour02)
Will return 2 rows FOR EACH row in Scheduled_tbl. If that table has multiple rows then you would get additional rows. When you say you are getting unknown rows - what does that actually mean?
And finally - if you provide sample data for each table we can actually test our queries to insure they are returning the expected data.
After further inspection, what you suggested is correct. The original sql was simply outputting it in a different order and I didn't see the additional rows at first. Thanks for the assistance!