SQLTeam.com | Weblogs | Forums

Multiple UNION ALL - need more efficient way

tsql
sql2012

#1

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.


#2

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)

#3

What is in the column s.Hour01, s.Hour02, etc...

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)

#4

sHour01 has the Y or N in it. And Jeff, the script you suggested is close but I'm getting unknown rows.

ID	ShiftDate	ShiftName	PlantNum	MachineNumber	StartTime	Scheduled
8	2017-02-20	1st Shift 	5000	J31	2017-02-20 06:00:00.000	Y
8	2017-02-20	1st Shift 	5000	J31	2017-02-20 07:00:00.000	Y

In yours, the original is return then another one with the hour added.
And I updated my question - just renamed a param


#5

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.


#6

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!