x

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')

 

  
more ▼

asked Apr 13 at 03:46 PM in Default

avatar image

druidruid
21 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 15 at 09:49 AM

avatar image

Usman Butt
14.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x1208
x267

asked: Apr 13 at 03:46 PM

Seen: 47 times

Last Updated: Apr 15 at 09:49 AM

Copyright 2018 Redgate Software. Privacy Policy