question

druidruid avatar image
druidruid asked

Subquery to return multiple rows

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')
sql-server-2008sqlsql server
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
Assuming that you would be calculating the duration for only a given day, the following gives the expected result. DECLARE @Now DATETIME , @Start DATETIME , @End DATETIME , @Final VARCHAR (50), @Offset INT SET @Offset = 0 SET @Now = convert(datetime,'20180411') - @Offset SET @Start = dateadd(day, datediff(day, 0, '20180411'), -@Offset) SET @End = dateadd(day, datediff(day, 0, '20180411'), -@Offset) declare @TotalTime decimal(18, 2) --Calculate total time duration select @TotalTime = SUM(datediff(MINUTE, StartTime, FinishTime)) from MaintTimeLog m join AssociateInfo ai on ai.ID = m.ID where EventDate >= @Start and EventDate <= @End and ai.FirstName = 'R' and OpCode <>'LL' select @TotalTime --Calculate time duration per OpCode select *, convert(time(0), dateadd(minute, DurationInMinutes, 0)) OpSum ,round(DurationInMinutes / @TotalTime * 100, 1) PCT from ( select m.OpCode, SUM(DATEDIFF(MINUTE, StartTime, FinishTime)) DurationInMinutes from MaintTimeLog m join AssociateInfo ai on ai.ID = m.ID where EventDate >= @Start and EventDate <= @End and ai.FirstName = 'R' and OpCode <> 'LL' group by m.OpCode )MaintTimeLog Please note that I have ignored your query as there are some filters applied in your code which are not matching with the expected results, so you may have to change the solution slightly according to the data.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.