CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DRUIDRUID
DRUIDRUID

Reputation: 369

subquery to return multiple rows

I have been stabbing at this for a while. Looking at a fe 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) we get the percent from the TimeSUM query which is 7:59 then divide by each OpSUM. 

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

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

Upvotes: 0

Views: 97

Answers (2)

DRUIDRUID
DRUIDRUID

Reputation: 369

@Alan I will give you full credit. I toyed with your approach and was able to produce exactly what I was looking for. The reason I want to use the date time format below is for clean reporting reasons. I appreciate your help with this. I was initially thinking a cross join was needed somewhere. Again, Thanks and cheers!

DECLARE 
    @Start     DATETIME    ,
    @End       DATETIME    ,
    @Offset    INT         
SET @Offset    = 3
SET @Start     = dateadd(day, datediff(day, 0, getdate()), -@Offset)
SET @End       = dateadd(day, datediff(day, 0, getdate()), -@Offset)

SELECT 
    ProdOp = OpCode,
    OpSUM  =  right(convert(varchar(9),(sum(datediff(second,StartTime,FinishTime)) / 3600 )),3) + ':' 
             + right('0' + convert(varchar(2),(sum(datediff(second,StartTime,FinishTime)) / 60) % 60 ),2),
    PCT    = cast(cast(SUM(datediff(second, StartTime, FinishTime)) * 100.0 / convert(decimal, t.total)as decimal(10,1)) as varchar(5)) + ' %'

FROM 
    MaintTimeLog
    CROSS JOIN (SELECT SUM(datediff(second,StartTime,FinishTime)) as total FROM MaintTimeLog where EventDate >= @Start and EventDate <= @End  and ID = 57524  and OpCode <> 'll') t
 where EventDate >= @Start and EventDate <= @End  and ID = 57524 and OpCode <> 'll'   
GROUP BY
    OpCode,
    t.total


ProdOp  OpSUM   PCT
BB      0:20    4.2 %
DF      1:15    15.7 %
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 %

Upvotes: 0

Alan
Alan

Reputation: 1428

My calcs are different, but I think they are correct - I checked them with an excel pivot table to get a percentage of grand totals. Try this:

SELECT 
    OpCode,
    convert(varchar, dateadd(s,sum(datediff(second,StartTime,FinishTime)), 0), 114),
    SUM(datediff(second, StartTime, FinishTime)) * 100.0 / convert(decimal, t.total)
FROM 
    MaintTimeLog
    CROSS JOIN (SELECT SUM(datediff(second,StartTime,FinishTime)) as total FROM MaintTimeLog) t
GROUP BY
    OpCode,
    t.total

The convert function, which itself uses a dateadd function, is only required for the formatting of the results. Your initial attempt does the mathematical calculation of the components of the formatted result, whereas this version uses the convert function to return the results in a formatted value without modifying the underlying values. Format 114 specifies the date format required.

Upvotes: 1

Related Questions