April 8, 2010 at 2:33 pm
My current subqueries will not allow my information to group by SOEID. Does someone know a quick way to make the code in my stored procedure group? I have tried everything. Is it only the Pivot command? If so, do you have code samples based on stored proc. My stored proc is below:
USE [Sourceinfo]
GO
/****** Object: StoredProcedure [dbo].[CTTS_TIME_sp] Script Date: 04/08/2010 15:24:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CTTS_TIME_sp]
-- Add the parameters for the stored procedure here
(
@From_Date datetime,
@To_Date datetime,
@TM_Manager nvarchar(50),
@avp nvarchar (50),
@site nvarchar (50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select Final_Results.SOEID, Sum(Final_Results.Prod_Hrs)as Prod_Hrs,Sum(Final_Results.Paid_Hrs)as Paid_hrs,
Final_Results.P1_start, Final_Results.P1_stop, Final_Results.B1_start, Final_Results.B1_stop, Final_Results.P2_start, Final_Results.P2_stop, Final_Results.L1_start,
Final_Results.L1_stop, Final_Results.P3_start, Final_Results.P3_stop, Final_Results.B2_start, Final_Results.B2_stop, Final_Results.P4_start, Final_Results.P4_stop,
Final_Results.P5_start, Final_Results.P5_stop
From(
Select
Results.SOEID, SUM(Results.Prod_hrs) as Prod_Hrs, SUM(Results.Paid_hrs) as Paid_Hrs,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else ''End)as P1_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P1_stop,
(Case When Results.ACTIVITY_CODE= 'B' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as B1_start,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as B1_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P2_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P2_stop,
(Case When Results.ACTIVITY_CODE = 'L' AND Results.RANKING= '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as L1_start,
(Case When Results.ACTIVITY_CODE = 'L' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as L1_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '3' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P3_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '3' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P3_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '4' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P4_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '4' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P4_stop,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as B2_start,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as B2_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '5' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P5_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '5' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P5_stop
FROM(
Select (B.[SOEID]), B. [STARTTIME], B.[STOPTIME], B.[ACTIVITY_CODE], CONVERT(nvarchar(10), STARTTIME, 101) AS WORKDATE, DENSE_RANK() OVER
(Partition By B.[ACTIVITY_CODE], B.[SOEID] ORDER BY B.[SOEID], B.[STARTTIME], B.[STOPTIME]) AS RANKING,
(Case When C.[PROD] = 'P' Then (B.[TASK_DUR_MINUTES])/60.00 Else 0 End) as Prod_hrs,
(Case When C.[PAID] = 'P' Then (B.[TASK_DUR_MINUTES])/60.00 Else 0 End) as Paid_hrs
From dbo.SCTTTimeEntryTbl B INNER JOIN dbo.SCTTActivityTbl C
ON B.[ACTIVITY_CODE] = C.[ACTIVITY_CODE]Inner Join dbo.SHIERARCHYtbl D
ON D.[SHRCSOEID] = B.[SOEID]
WHERE CONVERT(nvarchar(10), STARTTIME, 101) between @From_Date AND @To_Date AND D.[SHRCMGRNAME]= @TM_Manager
AND D.[SHRC2NDMGRNAME] = @avp AND D.[SHRCCITY] = @site AND B.[ACTIVITY_CODE] in ('P', 'B', 'L')
Group BY B.[SOEID], B.[ACTIVITY_CODE],B.[STARTTIME], B.[STOPTIME], B.[TASK_DUR_MINUTES], C.[PROD], C.[PAID]) AS Results
Group By Results.SOEID,Results.Prod_hrs, Results.Paid_hrs, Results.ACTIVITY_CODE, Results.STARTTIME, Results.STOPTIME,Results.RANKING)As Final_Results
Group By Final_Results.SOEID,Final_Results.P1_start, Final_Results.P1_stop, Final_Results.B1_start, Final_Results.B1_stop, Final_Results.P2_start, Final_Results.P2_stop, Final_Results.L1_start,
Final_Results.L1_stop, Final_Results.P3_start, Final_Results.P3_stop, Final_Results.B2_start, Final_Results.B2_stop, Final_Results.P4_start, Final_Results.P4_stop,
Final_Results.P5_start, Final_Results.P5_stop
END
April 8, 2010 at 3:42 pm
As a suggestion, it will be easier for people to help you if you clean up the code that you post. For example:
USE [Sourceinfo]
GO
/****** Object: StoredProcedure [dbo].[CTTS_TIME_sp] Script Date: 04/08/2010 15:24:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CTTS_TIME_sp]
-- Add the parameters for the stored procedure here
(
@From_Date datetime,
@To_Date datetime,
@TM_Manager nvarchar(50),
@avp nvarchar (50),
@site nvarchar (50)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select Final_Results.SOEID,
Sum(Final_Results.Prod_Hrs)as Prod_Hrs,
Sum(Final_Results.Paid_Hrs)as Paid_hrs,
Final_Results.P1_start,
Final_Results.P1_stop,
Final_Results.B1_start,
Final_Results.B1_stop,
Final_Results.P2_start,
Final_Results.P2_stop,
Final_Results.L1_start,
Final_Results.L1_stop,
Final_Results.P3_start,
Final_Results.P3_stop,
Final_Results.B2_start,
Final_Results.B2_stop,
Final_Results.P4_start,
Final_Results.P4_stop,
Final_Results.P5_start,
Final_Results.P5_stop
From
(
Select Results.SOEID,
SUM(Results.Prod_hrs) as Prod_Hrs,
SUM(Results.Paid_hrs) as Paid_Hrs,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else ''End)as P1_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P1_stop,
(Case When Results.ACTIVITY_CODE= 'B' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as B1_start,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as B1_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P2_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P2_stop,
(Case When Results.ACTIVITY_CODE = 'L' AND Results.RANKING= '1' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as L1_start,
(Case When Results.ACTIVITY_CODE = 'L' AND Results.RANKING = '1' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as L1_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '3' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P3_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '3' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P3_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '4' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P4_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '4' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P4_stop,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as B2_start,
(Case When Results.ACTIVITY_CODE = 'B' AND Results.RANKING = '2' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as B2_stop,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '5' Then CONVERT(nvarchar(5), STARTTIME, 108)Else '' End) as P5_start,
(Case When Results.ACTIVITY_CODE = 'P' AND Results.RANKING = '5' Then CONVERT(nvarchar(5), STOPTIME, 108)Else '' End) as P5_stop
FROM
(
Select B.SOEID,
B. [STARTTIME],
B.[STOPTIME],
B.[ACTIVITY_CODE],
CONVERT(nvarchar(10),
STARTTIME, 101) AS WORKDATE,
DENSE_RANK() OVER (Partition By B.[ACTIVITY_CODE], B.[SOEID] ORDER BY B.[SOEID], B.[STARTTIME], B.[STOPTIME]) AS RANKING,
(Case When C.[PROD] = 'P' Then (B.[TASK_DUR_MINUTES])/60.00 Else 0 End) as Prod_hrs,
(Case When C.[PAID] = 'P' Then (B.[TASK_DUR_MINUTES])/60.00 Else 0 End) as Paid_hrs
From dbo.SCTTTimeEntryTbl B
INNER JOIN dbo.SCTTActivityTbl C
ON B.[ACTIVITY_CODE] = C.[ACTIVITY_CODE]
Inner Join dbo.SHIERARCHYtbl D
ON D.[SHRCSOEID] = B.[SOEID]
WHERE CONVERT(nvarchar(10), STARTTIME, 101) between @From_Date AND
@To_Date AND D.[SHRCMGRNAME]= @TM_Manager AND
D.[SHRC2NDMGRNAME] = @avp AND D.[SHRCCITY] = @site AND
B.[ACTIVITY_CODE] in ('P', 'B', 'L')
Group BY B.SOEID, B.[ACTIVITY_CODE],B.[STARTTIME], B.[STOPTIME], B.[TASK_DUR_MINUTES], C.[PROD], C.[PAID]
) AS Results
Group By Results.SOEID,
Results.Prod_hrs,
Results.Paid_hrs,
Results.ACTIVITY_CODE,
Results.STARTTIME,
Results.STOPTIME,
Results.RANKING
)As Final_Results
Group By Final_Results.SOEID,
Final_Results.P1_start,
Final_Results.P1_stop,
Final_Results.B1_start,
Final_Results.B1_stop,
Final_Results.P2_start,
Final_Results.P2_stop,
Final_Results.L1_start,
Final_Results.L1_stop,
Final_Results.P3_start,
Final_Results.P3_stop,
Final_Results.B2_start,
Final_Results.B2_stop,
Final_Results.P4_start,
Final_Results.P4_stop,
Final_Results.P5_start,
Final_Results.P5_stop
END
Start with the inner-most derived table and work outwards ... at what point are you having issues with your grouping?
April 8, 2010 at 6:04 pm
Sorry for not cleaning up the code. However, I am having problems with the code returning multiple lines for the SOEID. It should return one line per SOEID. The SOEID is the customer service agent ID. If you have suggestions, it is appreciated.
Group By Final_Results.SOEID,
Final_Results.P1_start,
Final_Results.P1_stop,
Final_Results.B1_start,
Final_Results.B1_stop,
Final_Results.P2_start,
Final_Results.P2_stop,
Final_Results.L1_start,
Final_Results.L1_stop,
Final_Results.P3_start,
Final_Results.P3_stop,
Final_Results.B2_start,
Final_Results.B2_stop,
Final_Results.P4_start,
Final_Results.P4_stop,
Final_Results.P5_start,
Final_Results.P5_stop
April 8, 2010 at 6:23 pm
So you are grouping by a bunch of different fields, each of which can have multiple values ([P1_start] can be either CONVERT(nvarchar(5), STARTTIME, 108) or ''). If you have multiple records for the same SOEID, you will continue to get mutiple records for the same SOEID.
If you only want one value for each SOEID, you need to aggregate all other fields (P1_start, P1_stop, B1_start ... )
April 8, 2010 at 6:29 pm
The P1_start, P1_stop fields are fields capturing the time. Prod_Hrs and Paid_Hrs are aggregated. I had to had the others to the Group By Clause. I thinking it must be a workaround.
April 8, 2010 at 6:31 pm
I am not sure it will work. I was hoping someone had the same experience.
April 8, 2010 at 6:35 pm
I don't think you understand. GROUP BY is working exactly as intended in the example you've given. When you include multiple fields in the condition it only aggregates on records that are exactly the same across all of the fields in the GROUP BY.
Remove your date fields from the GROUP BY, then aggregate those in the SELECT (use MIN or MAX for example) and it will work perfectly. If you cannot aggregate your dates, you cannot guarantee single records for your ID field.
April 8, 2010 at 6:56 pm
I tried you suggestion, but it returned the following message.
StartTime is invalid in the select list because it must be contained in an aggregate function or group by clause.
April 8, 2010 at 10:45 pm
vktaylor (4/8/2010)
I tried you suggestion, but it returned the following message.StartTime is invalid in the select list because it must be contained in an aggregate function or group by clause.
Actually, you tried half the suggestion 😉
You removed the field from the group by, but you didn't add it to an aggregate. Wrap a Max() around each of the fields that you removed from the GROUP BY.
[Edit] Edited for gender neutrality.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply