question

I have been stabbing at this for a while. Looking at a few options to show the below resultset. Want to show the ProdOp, OpSUM, then the PCT (which we will grab from the total amount of time minus t.opcode LL. Its been a long day and im thinking it may be because subs cannot return multiple rows? If not any other way to approach? cross join? **Expected Result (PCT with actual Percentage)**PCT is OpSUM DIVIDED BY TimeSUM (which is in the subquery) ProdOp OpSUM PCT BB 0:20 4.2% DF 1:15 15.6% HF 0:10 2.1% HR 0:25 5.2% JT 0:14 2.9% MM 0:27 5.6% NW 0:39 8.1% PE 0:21 4.4% PX 0:45 9.4% SP 2:52 35.9% SS 0:31 6.5% **Current Approach...** DECLARE @Now DATETIME , @Start DATETIME , @End DATETIME , @Final VARCHAR (50), @Offset INT SET @Offset = 1 SET @Now = GETDATE()-@Offset SET @Start = dateadd(day, datediff(day, 0, getdate()), -@Offset) SET @End = dateadd(day, datediff(day, 0, getdate()), -@Offset) select [ProdOp], [OpSUM], [TimeSUM], Cast(Cast((OpSUM/TimeSUM)*100 as decimal(10,1)) as varchar(5)) + ' %' as [PCT] from select( (select t.OpCode FROM MaintTimeLog t join AssociateInfo ai on t.ID = ai.ID where t.EventDate >= @Start and t.EventDate <= @End and t.OpCode NOT IN ('SS', 'LL', 'BB', 'MM') group by ai.FirstName, t.OpCode ) as ProdOp, (select right(space(5)+rtrim(right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2)),5) FROM MaintTimeLog t join AssociateInfo ai on t.ID = ai.ID where t.EventDate >= @Start and t.EventDate <= @End and ai.FirstName = 'R' and t.OpCode NOT IN('SS', 'LL', 'BB', 'MM') group by ai.FirstName, t.OpCode ) as OpSUM, (select right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2) FROM MaintTimeLog t join AssociateInfo ai on t.ID = ai.ID where t.EventDate >= @Start and t.EventDate <= @End and ai.FirstName = 'R' and t.opcode <>'LL' group by ai.FirstName ) as TimeSUM from MaintTimeLog t join AssociateInfo ai on t.ID = ai.ID where EventDate >= @Start and EventDate <= @End and ai.FirstName = 'R' group by ai.FirstName )q **`MaintTimeLog`sample data** CREATE TABLE [dbo].[MaintTimeLog]( [EventDate] [varchar](15) NOT NULL, [ID] [varchar](7) NOT NULL, [DeptCode] [varchar](2) NOT NULL, [OpCode] [varchar](2) NOT NULL, [StartTime] [time](0) NOT NULL, [FinishTime] [time](0) NOT NULL, ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MaintTimeLog] WITH CHECK ADD CONSTRAINT [CK_Start_LessThan_Finsih_Maint] CHECK (([StartTime]<[FinishTime])) GO ALTER TABLE [dbo].[MaintTimeLog] CHECK CONSTRAINT [CK_Start_LessThan_Finsih_Maint] GO INSERT INTO [dbo].[MaintTimeLog]([EventDate],[ID] ,[DeptCode] ,[OpCode] ,[StartTime] ,[FinishTime]) VALUES ('4/11/2018', '57524', 'SS', 'SS', '6:00:00', '6:31:00'), ('4/11/2018', '57524', 'FP', 'SP', '6:31:00', '7:01:00'), ('4/11/2018', '57524', 'MM', 'MM', '7:01:00', '7:24:00'), ('4/11/2018', '57524', 'FS', 'SP', '7:24:00', '7:33:00'), ('4/11/2018', '57524', 'RC', 'JT', '7:33:00', '7:47:00'), ('4/11/2018', '57524', 'FS', 'SP', '7:47:00', '7:50:00'), ('4/11/2018', '57524', 'HP', 'SP', '7:50:00', '9:40:00'), ('4/11/2018', '57524', 'BB', 'BB', '9:40:00', '10:00:00'), ('4/11/2018', '57524', 'RQ', 'SP', '10:00:00', '10:20:00'), ('4/11/2018', '57524', 'DS', 'NW', '10:20:00', '10:59:00'), ('4/11/2018', '57524', 'FC', 'PE', '10:59:00', '11:20:00'), ('4/11/2018', '57524', 'MT', 'HF', '11:20:00', '11:30:00'), ('4/11/2018', '57524', 'LL', 'LL', '11:30:00', '12:01:00'), ('4/11/2018', '57524', 'TW', 'PX', '12:01:00', '12:46:00'), ('4/11/2018', '57524', 'MM', 'MM', '12:46:00', '12:50:00'), ('4/11/2018', '57524', 'FS', 'HR', '12:50:00', '13:15:00'), ('4/11/2018', '57524', 'HD', 'DF', '13:15:00', '14:30:00') **`AssociateInfo` Sample data** INSERT INTO [dbo].[AssociateInfo]([ID],[FirstName]) VALUES ('57524', 'R')