I have a stored proceedure I am working with.The problem I am running into with it;I get duplicate items with different values when I wanted them grouped by the key value.The code I wrote:SELECT LEFT(MY_DATABASE.PROJ.L1_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L2_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L3_PROJ_SEG_ID,9) AS PROJ_ID,MY_DATABASE.PROJ.EMPL_ID AS PM,MY_DATABASE.PROJ.PROJ_NAME AS [Description],MY_DATABASE.PROJ.CUST_ID AS Customer,Labor = SUM(CASE WHEN SUB_TOT_TYPE_NO IN (2, 4, 5) THEN (PY_INCUR_AMT + YTD_INCUR_AMT) ELSE 0.0 END)Expense = SUM(Case WHEN SUB_TOT_TYPE_NO = 3 THEN (PY_INCUR_AMT + YTD_INCUR_AMT) ELSE 0.0 end) FROM MY_DATABASE.PROJ, MY_DATABASE.PSR_FINAL_DATA WHERE MY_DATABASE.PROJ.PROJ_ID = MY_DATABASE.PSR_FINAL_DATA.PROJ_ID AND FY_CD IN ('2006','2007') AND PD_NO = '5' AND RATE_TYPE IN ('T', 'N')GROUP BY LEFT(MY_DATABASE.PROJ.L1_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L2_PROJ_SEG_ID + '.' + MY_DATABASE.PROJ.L3_PROJ_SEG_ID,9), MY_DATABASE.PROJ.EMPL_ID, MY_DATABASE.PROJ.PROJ_NAME, MY_DATABASE.PROJ.CUST_ID ORDER BY 1 ASCAs soon as I combine Labor with Expense in the query, The results for PROJ_ID are coming up with 3 separate records of the same PROJ_ID but with the suffix variant used to discern them(x,y,z). If I keep the functions for Labor or Expense alone in the select statement I get one PROJ_ID combining the 3 with the GROUP BY Function the way I want.Is there a function I need to use in the SELECT or GROUP BY portions to get these records to group with both or more functions in the SELECT?Thank you for your time.
Best Regards,~Chris
Stored Procedure: Grouping Select Functions?
Posted - 2007-06-05 : 17:55:25
MadhivananFailing to plan is Planning to fail