Group BY

• The_V

  Mr or Mrs. 500

  Points: 561

  More actions

  March 29, 2010 at 3:19 pm

  #231267

  End Results - I want to display results by SOEID only. Currently, the query works only if I add all fields to the group by clause from the select statements. This create multiple listings of SOEIDs which are not duplicates. Therefore, I need to show one listing per SOEID. Any workarounds for the group by clause? Please advise. Code below.

  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.SCTTTimeEntryMTDTbl_Verlin_2 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.STARTTIME, Results.STOPTIME,Results.ACTIVITY_CODE, Results.RANKING)As Final_Results

  Group By Final_Results.SOEID